SQL 튜닝이론 - SQL 처리 중 정렬연산(SORT OPERATION) 이 발생하는 경우 총정리

 

SQL 튜닝이론 - SQL 처리 중 정렬연산(SORT OPERATION) 이 발생하는 경우 총정리

 

SQL 수행 도중 정렬된 데이터 집합이 필요할 때, DBMS은 PGA와 DISK TEMP TABLESPACE를 활용합니다. 소트 머지 조인, 해시 조인과 ORDER BY, GROUP BY 연산이 대표적으로 정렬된 데이터 집합을 활용하는 케이스입니다.

 

소트 수행 과정

 

소트는 기본적으로 PGA에 할당한 SORT AREA에서 처리됩니다. SORT AREA가 차면 DISK TEMP TABLESPACE를 활용합니다. SORT AREA에서 작업을 완료할 수 있는지에 따라 소트는 두 가지 유형으로 구분됩니다.

 

- 메모리 소트 : 전체 데이터의 정렬 작업을 메모리 내에서 완료하는 것을 말하며 INTERNAL SORT 라고도 합니다.

 

- 디스크 소트 : 할당받은 SORT AREA 내에서 정렬을 완료하지 못해 디스크 공간까지 사용하는 경우를 말하며, EXTERNAL SORT 라고도 합니다.

 

소트할 대상 집합을 SGA 버퍼캐시를 통해 읽어들이고, 일차적으로 SORT AREA에서 정렬을 시도합니다. SORT AREA 내에서 데이터 정렬을 마무리하는 것이 최적이지만, 데이터가 많을 때는 정렬된 중간 집합을 TEMP TABLESPACE에 임시 세그먼트를 만들어 저장합니다. SORT AREA가 찰 때마다 TEMP 영역에 저장해둔 중간 단계의 집합을 'SORT RUN' 이라 부릅니다.

 

정렬된 최종 결과집합을 얻으려면 이를 다시 데이터를 SGA에 MERGE 해야합니다. 각 SORT RUN 내에서는 이미 정렬된 상태이므로 MERGE 과정은 어렵지 않습니다. 오름차순 정렬이라면 각각에서 가장 작은 값부터 PGA로 읽어 들이다가 PGA가 찰 때마다 쿼리 수행 다음 단계로 전달하거나 클라이언트에게 전송하면 됩니다.

 

소트 연산은 메모리 집약적이고 CPU 집약적입니다. 처리할 데이터량이 많을 때는 디스크 소트를 통한 디스크 I/O 까지 발생하므로 디스크 소트는 쿼리 성능을 좌우하는 매우 중요한 요소입니다. 디스크 소트가 발생하는 순간 SQL 수행 성능은 나빠질 수밖에 없습니다. 많은 서버 리소스를 사용하고 디스크 I/O가 발생하는 것도 문제지만, 부분범위 처리를 불가능하게 함으로써 OLTP 환경에서 애플리케이션 성능을 저하시키는 주 요인이 됩니다. 될 수 있으면 소트가 발생하지 않도록 SQL을 작성해야 하고, 소트가 불가피 하다면 메모리 내에서 수행을 완료할 수 있도록 해야합니다.

 

소트 오퍼레이션

 

소트를 발생시키는 오퍼레이션은 다음과 같습니다.

 

SORT AGGREGATE

 

전체 로우를 대상으로 집계를 수행할 때 나타나는 오퍼레이션입니다. SORT 라는 표현을 사용하지만, 실제로 데이터를 정렬하지 않고 SORT AREA를 사용합니다.

 

SELECT SUM(SAL), MAX(SAL), MIN(SAL), AVG(SAL) FROM EMP;

-- EXECUTION PLAN
-- SELECT STATEMENT
--  SORT AGGREAGTE
--    TABLE ACCESS FULL EMP

 

데이터를 정렬하지 않고 SUM, MAX, MIN, AVG 값을 구하는 절차는 아래와 같습니다.

 

1. SORT AREA에 SUM, MAX, MIN, AVG, COUNT 값을 위한 변수를 각각 하나씩 할당한다.

2. EMP 테이블 첫 번째 레코드에서 읽은 SAL 값을 SUM, MAX, MIN 변수에 저장하고, COUNT 변수에는 1을 저장한다.

3. EMP 테이블에서 레코드를 하나씩 읽어서 내려가면서 SUM 변수에는 값을 누적하고, MAX, MIN 에 각각 자신보다 큰 값, 작은
값이 나타날 때마다 값을 대체한다.

4. COUNT 변수에는 null이 아닌 레코드를 만날때마다 1씩 증가시킨다.

5. 레코드를 다 읽고 나면 각 변수에 저장된 값을 출력하고 AVG는 SUM 값을 COUNT 값으로 나눈 2800을 출력하면 된다.

 

SORT ORDER BY

 

ORDER BY로 데이터를 정렬할 때 나타나는 오퍼레이션입니다.

SELECT * FROM EMP order by sal desc;

-- EXECUTION PLAN
-- SELECT STATEMENT
--   SORT ORDER BY
--    TABLE ACCESS FULL EMP

 

SORT GROUP BY

 

소팅 알고리즘을 사용해 그룹별 집계를 수행할 때 나타나는 오퍼레이션입니다.

 

SELECT DEPTNO, SUM(SAL), MAX(SAL), MIN(SAL), AVG(SAL)
FROM EMP
group by DEPTNO
order by DEPTNO;

-- EXECUTION PLAN
-- SELECT STATEMENT
--  SORT GROUP BY
--    TABLE ACCESS FULL EMP

 

SORT GROUP BY 을 이해하기 위해 수천 명의 사원이 근무하는 회사를 가정합니다. 부서는 네 개 뿐이며, 부서코드로는 각각 10,20,30,40 을 사용합니다. 집계 항목은 합계, 최대값, 최소값, 평균값입니다.

 

10 부터 40 까지의 부서코드로 이루어진 4개의 공간을 준비합니다. 각 공간에 SUM,MAX,MIN,COUNT 입력란을 두고 공간을 부서번호 순으로 정렬합니다. 각 사원의 급여 정보를 읽으며 부서번호에 해당하는 공간을 찾습니다. 공간은 사원번호 순에 따라 정렬돼 있으므로 쉽게 찾을 수 있습니다. 공간을 찾으면 SUM,MAX,MIN,COUNT 값을 갱신합니다.

 

SORT AGGREGATE 에서 사용했던 방식을 부서코드 별 공간에 똑같이 적용합니다. 부서 개수를 미리 알 수 없다면, 직원 대장을 읽다가 새로운 부서가 나타날 때마다 새로운 공간을 정렬 순서에 맞춰 중간에 끼워 넣는 방식을 사용해야합니다. 사원의 데이터가 수억 건이더라도 부서의 수가 4개라면 단 네 곳의 저장공간만 있으면 되므로 SORT AREA가 클 필요가 없습니다. 집계할 대상 레코드가 아무리 많아도 TEMP 테이블스페이스를 쓰지 않습니다.

 

오라클 10gR2 버전에서 도입된 HASH GROUP BY 방식도 알고있어야합니다. GROUP BY 절 뒤에 ORDER BY 절을 명시하지 않으면 이제 대부분 HASH GROUP BY 방식으로 처리하기 때문입니다.

 

SELECT deptno, SUM(SAL), MAX(SAL), MIN(SAL), AVG(SAL)
FROM EMP;
group by deptno

-- EXECUTION PLAN
-- SELECT STATEMENT
--  HASH GROUP BY
--    TABLE ACCESS FULL EMP

 

SORT GROUP BY에서 저장공간을 찾기 위해 소트 알고리즘을 사용했다면, HASH GROUP BY는 해싱 알고리즘을 사용합니다. 읽는 레코드마다 GROUP BY 컬럼의 해시 값으로 해시버킷을 찾아 그룹별로 집계항목을 갱신하는 방식입니다. 부서가 많지 않다면 집계할 대상 레코드가 아무리 많아도 TEMP 테이블스페이스를 사용하지 않습니다.

 

그룹핑 결과의 정렬 순서

 

10g R2에서 HASH GROUP BY가 도입되면서 정렬순서를 보장하지 않게 되었다고 알려져있지만, 사실 오라클은 9i부터 이미 그룹핑 결과가 정렬 순서를 보장하지 않습니다.

 

select deptno, job, sum(sal), max(sal), min(sal)
from emp
group by deptno, job;

-- execution plan
-- SELECT STATEMENT
--    SORT(GROUP BY)
--      TABLE ACCESS FULL OF EMP

 

실행계획에 표시된 SORT GROUP BY의 의미는 소팅 알고리즘을 사용해 값을 집계한다는 뜻일 뿐 정렬을 의미하지 않습니다. 물론 쿼리에 ORDER BY절을 명시하면 정렬 순서가 보장됩니다. 이때도 실행계획은 똑같이 SORT GROUP BY로 표시되므로 실행계획만 보고 정렬 여부를 판단해서는 안됩니다.

 

같은 SORT GROUP BY인데 ORDER BY 유무에 따라 정렬 순서가 달라지는 이유 소팅 알고리즘을 사용해 그룹핑한 결과집합은 논리적인 정렬 순서를 갖는 연결리스트 구조이기 때문입니다. 사용자가 ORDER BY를 명시하면 오라클은 논리적 정렬 순서를 따라 값을 읽기 때문에 정렬 순서가 보장됩니다. 물리적으로 저장된 순서는 논리적 순서와 다를 수 있고, ORDER BY 절이 없으면 오라클은 반드시 정렬된 순서로 출력할 의무가 없습니다. 이때는 논리적 순서를 무시하고 물리적으로 저장된 순서에 따라 값을 읽으므로 정렬을 보장하지 않습니다 결론적으로 정렬된 그룹핑 결과를 얻고자 한다면, 실행계획에 설령 SORT GROUP BY 라고 표시되더라도 반드시 ORDER BY 를 명시해야합니다.

 

추가적으로 그룹핑을 위해 내부적으로 사용하는 알고리즘이 바뀔 뿐 ORDER BY 절을 추가한다고 해서 그룹핑과 정렬 작업을 각각 수행하지 않습니다.

 

SORT UNIQUE

 

옵티마이저가 서브쿼리를 풀어 일반 조인문으로 변환하는 것을 서브쿼리 UNNESTING 이라고 합니다. UNNESTING 된 서브쿼리가 M쪽 집합일때 서브쿼리를 드라이빙 테이블로서 메인 쿼리와 조인 하려면 중복 레코드부터 제거해야 합니다. 이때 아래와 같이 SORT UNIQUE 오퍼레이션이 나타납니다.

 

select /*+ordered use_nl(dept)*/
*
from dept
where deptno in ( select /*+unnest*/ deptno from emp where job = 'CLERK' )

-- execution plan
-- SELECT STATEMENT
--  NESTED LOOPS
--    SORT UNIQUE
--      TABLE ACCESS BY INDEX ROWID EMP TABLE
--        INDEX RANGE SCAN OF EMP_JOB_IDX
--      TABLE ACCESS BY INDEX ROWID DEPT TABLE
--        INDEX UNIQUE SCAN DEPT PK

 

만약 PK/UNIQUE 제약 또는 UNIQUE 인덱스를 통해 UNNESTING된 서브쿼리의 유일성이 보장되면 SORT UNIQUE 오퍼레이션은 생략됩니다. UNION, MINUS, INTERSECT 같은 집합 연산자를 사용할 때도 아래와 같이 SORT UNIQUE 오퍼레이션이 나타납니다.

 

select job, mgr from emp where deptno = 10
union
select job, mgr from emp where deptno = 20

-- execution plan
-- SELECT STATEMENT
--    SORT UNIQUE
--      UNION-ALL
--        TABLE ACCESS FULL EMP
--        TABLE ACCESS FULL EMP

select job, mgr from emp where deptno = 10
minus
select job, mgr from emp where deptno = 20

-- execution plan
-- SELECT STATEMENT
--   MINUS
--    SORT UNIQUE
--       TABLE ACCESS FULL EMP
--    SORT UNIQUE
--       TABLE ACCESS FULL EMP

SELECT DISTINCT DEPTNO FROM EMP ORDER BY DEPTNO;

-- execution plan
-- SELECT STATEMENT
--    SORT UNIQUE
--       TABLE ACCESS FULL EMP

-- 오라클 10gR2부터는 DISCTINCT 연산에도 아래와 같이 HASH UNIQUE 방식을 사용한다.
-- GROUP BY 와 마찬가지로 ORDER BY 생략할 때 그렇다.

SELECT DISTINCT DEPTNO FROM EMP;

-- execution plan
-- SELECT STATEMENT
--    HASH UNIQUE
--       TABLE ACCESS FULL EMP

 

SORT JOIN

 

SORT JOIN 오퍼레이션은 소트 머지 조인을 수행할 때 나타납니다.

 

SELECT /*+ORDERED USE_MERGE(e)*/
*
FROM DEPT D, EMP E
WHERE D.DEPTNO = D.DEPTNO;

-- execution plan
-- SELECT STATEMENT
--  SORT JOIN
--    TABLE ACCESS FULL DEPT
--    TABLE ACCESS FULL EMP

 

WINDOW SORT

 

윈도우 함수를 수행할 때 나타납니다.

 

SELECT EMPNO, ENAME, JOB, MGR, SAL, AVG(SAL) OVER (PARTITION BY DEPTNO)
FROM EMP;

-- execution plan
-- SELECT STATEMENT
--  WINDOW SORT
--    TABLE ACCESS FULL EMP