[친절한 SQL 튜닝] 5. 소트 튜닝 [1]

 

5.1 소트 연산에 대한 이해

소트는 PGA에 할당한 Sort Area에서 이루어진다. 메모리 공간인 Sort Area가 다 차면, 디스크 Temp 테이블스페이스를 활용한다.

 

소트 유형

• 메모리 소트(In-Memory Sort) : 전체 데이터의 정렬 작업을 메모리 내에서 완료하는 것. "Internal Sort"라고도 불림
• 디스크 소트(To-Disk Sort) : 할당받은 Sort Area 내에서 정렬을 완료하지 못해 디스크 공간까지 사용하는 것.                     "External Sort"라고도 불림

 

Disk Sort 과정

 

• 소트할 대상 집합을 SGA 버퍼캐시를 통해 읽어 들이고, 일차적으로 Sort Area에서 정렬 시도(양이 많을 때는 정렬된 중간집합을 Temp 테이블스페이스에 임시 세그먼트를 만들어 저장함)
• Sort Area가 찰 때마다 Temp 영역에 저장해 둔 중간 단계의 집합을 "Sort Run"이라고 불림.
• 정렬된 최종 결과집합을 얻기 위해 Merge를 해야 함. 각 Sort Run 내에서 이미 정렬된 상태이므로 오름차순 정렬이라면 각각 가장 작은 값부터 PGA로 읽어 들이다가 PGA가 찰 때마다 쿼리 수행 다음 단계로 전달하거나 클라이언트에 전송

 

* 소트 연산은 메모리 집약적일 뿐만 아니라 CPU 집약적이기도 함. 처리할 데이터량이 많을 때는 디스크 I/O까지 발생하므로 쿼리 성능을 좌우하는 매우 중요한 요소. 디스크 소트가 발생하는 순간 SQL 수행 성능이 나빠질 수밖에 없음.

 

* 부분범위 처리를 불가능하게 함으로써  OLTP 환경에서 애플리케이션 성능을 저하시키는 주 요인(소트가 불가피하다면 메모리 내에서 수행을 완료할 수 있도록 해야 함).

 

 

5.1.2 소트 오퍼레이션

 

Sort Aggregate

전체 로우를 대상으로 집계를 수행할 때 나타난다. "Sort"라는 표현을 사용하지만, 실제로 데이터를 정렬하진 않고, Sort Area를 사용한다는 의미이다.

 

SELECT SUM(SAL), MAX(SAL), MIN(SAL), AVG(SAL)
  FROM EMP;
  
  ---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     4 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |    56 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

 

1. Sort Area에 SUM, MAX, MIN, COUNT 값을 위한 변수를 각각 하나씩 할당한다.
2. EMP 테이블 첫 번째 레코드에서 읽은 SAL 값을 SUM, MAX, MIN 변수에 저장하고, COUNT 변수에는 1을 저장한다.
3. EMP 테이블에서 레코드를 하나씩 읽어 내려가면서 SUM 변수에는 값을 누적하고, MAX 변수에는 기존보다 큰 값이 나타날 때마다 값을 대체하고, MIN 변수에는 기존보다 작은 값이 나타날 때마다 값을 대체. COUNT 변수에는 SAL 값이 NULL이 아닐 때마다 1씩 증가시킨다.
4. EMP 레코드를 다 읽고 나면 각 변수를 출력함. AVG 변수는 SUM 값을 COUNT 값으로 나누어 출력

 

Sort Order By

데이터를 정렬할 때 나타난다. 정렬 과정은 디스크 소트 과정을 거침.

 

SELECT *
  FROM EMP
 ORDER BY SAL DESC;
 
 ---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   518 |     4  (25)| 00:00:01 |
|   1 |  SORT ORDER BY     |      |    14 |   518 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

 

 

Sort Group By

소팅 알고리즘을 사용해 그룹별 집계를 수행할 때 나타난다.

 

SELECT DEPTNO, SUM(SAL), MAX(SAL), MIN(SAL), AVG(SAL)
  FROM EMP
 GROUP BY DEPTNO
 ORDER BY DEPTNO;
 
 ---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    11 |   165 |     4  (25)| 00:00:01 |
|   1 |  SORT GROUP BY     |      |    11 |   165 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   210 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

 

 

예를 들어, 수천명의 사원(EMP)이 근무하는 회사를 가정해 보자.

부서는 네 개뿐이며, 부서코드로는 각각 10, 20, 30, 40을 사용한다.

 

각 네 개의 부서에 SUM, MAX, MIN, COUNT를 기록할 수 있는 환경을 만들어주고, Sort Aggregate에서 사용했던 방식을 똑같이 사용해 값을 집계한다.

 

DBMS는 Sort Group By를 처리할 때 Sort Aggregate 방식을 사용한다. 부서의 수가 많지 않다면 Sort Area가 클 필요가 없고, 집계할 대상 레코드가 아무리 많아도 Temp 테이블스페이스를 사용하지 않는다.

 

* 오라클 10gR2 버전에서는 Hash Group By를 사용한다(Order By 절을 명시하지 않으면 대부분 Hash Group 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');
                   
 ---------------------------------------------------------------------------------
| Id | Operation                     | Name        | Rows | Bytes | Cost (%CPU) |
---------------------------------------------------------------------------------
|  0 | SELECT STATEMENT              |             |    3 |    87 |     4  (25) |
|  1 |  NESTED LOOPS                 |             |    3 |    87 |     4  (25) |
|  2 |   **SORT UNIQUE**             |             |    3 |    33 |     2   (0) |
|  3 |   TABLE ACCESS BY INDEX ROWID | EMP         |    3 |    33 |     2   (0) |
|  4 |    TABLE RANGE SCAN           | EMP_JOB_IDX |    3 |       |     1   (0) |
|  5 |   TABLE ACCESS BY INDEX ROWID | DEPT        |    1 |    18 |     1   (0) |
|  6 |    INDEX UNIQUE SCAN          | DEPT_PK     |    1 |       |     0   (0) |
---------------------------------------------------------------------------------

 

• 만약 PK/Unique 제약 또는 Unique 인덱스를 통해 Unnesting된 서브쿼리의 유일성이 보장된다면, Sort Unique 오퍼레이션은 생략됨.
• UNION, MINUS, INTERSECT 같은 집합 연산자를 사용할 때도 Sort Unique 오퍼레이션이 나타남.
• DISTINCT 연산자를 사용해도 Sort Unique 오퍼레이션이 나타남.

 

Sort Join

소트 머지 조인을 수행할 때 나타난다.

SELECT /*+ ORDERED USE_MERGE(E) */
	   *
  FROM DEPT D, EMP E
 WHERE D.DEPTNO = E.DEPTNO
 
 ----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    14 |   770 |     8  (25)| 00:00:01 |
|   1 |  MERGE JOIN         |      |    14 |   770 |     8  (25)| 00:00:01 |
|   2 |   SORT JOIN         |      |     4 |    72 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| DEPT |     4 |    72 |     3   (0)| 00:00:01 |
|   4 |   SORT JOIN         |      |    14 |   518 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

 

 

Window Sort

윈도우 함수(분석 함수)를 수행할 때 나타난다.

 

SELECT EMPNO, ENAME, JOB, MGR, SAL
     , AVG(SAL) OVER(PARTITION BY DEPTNO)
  FROM EMP;
  
  ---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   406 |     4  (25)| 00:00:01 |
|   1 |  WINDOW SORT       |      |    14 |   406 |     4  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   406 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------