친절한 SQL 튜닝_5장.소트 튜닝_1

 

소트 튜닝

 

5.1. 소트 연사에 대한 이해

5.1.1 소트 수행 과정

소트는 기본적으로 PGA에 할당한 Sort Area에서 이루어진다. 메모리 공간인 Sort Area가 다 차면, 디스크 Temp 테이블스페이스를 활용한다. Sort Area에서 작업을 완료할 수 있는지에 따라 소트를 두 가지 유형으로 나눈다.

메모리 소트(In-Memory Sort) : 전체 데이터의 정렬 작업을 메모리 내에서 완료하는 것을 말하며, ‘Internal Sort’라고도 한다.

디스크 소트(To-Disk Sort) : 할당받은 Sort Area 내에서 정렬을 완료하지 못해 디스크 공간까지 사용하는 경우를 말하며, ‘External Sort’라고도 한다.

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

많은 서버 리소스를 사용하고 디스크 I/O가 발생하는 것도 문제지만, 부분범위 처리를 불가능하게 함으로써 OLTP 환경에서 애플리케이션 성능을 저하시키는 주요인이 되기도 한다.

 

5.1.2 소트 오퍼레이션

(1) Sort Aggregate

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

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

 

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 레코드를 다 읽고 나면 SUM, MAX, MIN, COUNT 변수에 각각 14000, 5000. 1000, 5가 저장돼 있다. SUM, MAX, MIN 값은 변수에 담긴 값을 그대로 출력하고, AVG는 SUM값을 COUNT 값으로 나눈 2800을 출력하면 된다.

 

(2) Sort Order By

Sort Order By는 데이터를 정렬할 때 나타난다.

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

 

(3) Sort Group By

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

Sort Group by 처리 과정을 쉽게 설명하기 위해 수천 명의 사원(EMP)이 근무하는 회사를 가정해 보자. 부서는 네 개뿐이며, 부서코드로는 각각 10, 20, 30, 40을 사용한다. 컴퓨터의 도움을 받지 않고 부서별 급여(SAL)를 집계하려고 할 때, 어떤 방법을 사용하면 좋을까? 집계하고자 하는 항목은 급여에 대한 합계, 최대값, 최소값, 평균값이다.

급여 대장에서 전체 사원의 급여 정보를 읽어 부서번호 순으로 정렬하는 작업부터 해야 할까? 그렇지 않다. 10부터 40까지 적은 메모지 네 개만 준비하면 된다. 각 메모지에 SUM, MAX, MIN, COUNT를 적을 수 있도록 입력란에 두고, 메모지를 부서번호 순으로 정렬해 놓으면 준비 끝이다.

이제 각 사원의 급여 정보를 읽기 시작한다. 읽은 각 사원의 부서번호에 해당하는 메모지를 찾는다. 정렬돼 있으므로 메모지 찾기는 어렵지 않다. 메모지를 찾았으면 SUM, MAX, MIN, COUNT 값을 갱신한다. Sort Aggregate에서 사용했던 방식을 여기서도 똑같이 사용한다. 급여 대장을 다 읽고 나서 메모지에 기록돼 있는 정보가 우리가 원하던 부서별 급여 집계다.

오라클 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;

Id           Operation                           Name     Rows      Bytes      Cost(%CPU)          Time

0            SELECT STATEMENT                           11          165                     4(25)      00:00:01

1            HASH GROUP BY                           11          165                     4(25)      00:00:01

2              TABLE ACCESS FULL         EMP       14          210                     3(0)        00:00:01

Sort Group By에서 메모지를 찾기 위해 소트 알고리즘을 사용했다면, Hash Group By는 해싱 알고리즘을 사용한다.

읽는 레코드마다 Group By 컬럼의 해시 값으로 해시 버킷을 찾아 그룹별로 집계항목을 갱신하는 방식이다. 부서(그룹 개수)가 많지 않다면 집계할 대상 레코드가 아무리 많아도 Temp 테이블스페이스 쓸 일이 전혀 없다.

 

(4)Sort Unique

옵티마이저가 서브쿼리를 풀어 일반 조인문으로 변환하는 것을 ‘서브쿼리 Unnesting’이라고 한다. Unnesting된 서브쿼리가 M쪽 집합이면(1쪽 집합이더라도 조인 컬럼에 Unique 인덱스가 없으면), 메인 쿼리와 조인하기 전에 중복 레코드부터 제거해야 한다. 이때 아래와 같이 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                INDEX 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 된 서브쿼리의 유일성(Uniqueness)이 보장된다면, Sort Unique 오퍼레이션은 생략된다.

Union, Minus, Intersect 같은 집합(Set) 연산자를 사용할 때도 아래와 같이 Sort Unique 오퍼레이션이 나타난다.

Distinct 연산자를 사용해도 Sort Unique 오퍼레이션이 나타난다.

오라클 10gR2부터는 Distinct 연산에도 Hash Unique 방식을 사용한다. Group By와 마찬가지로 Order by를 생략할 때 그렇다.

 

(5) Sort Join

Sort Join 오퍼레이션은 소트 머지 조인을 수행할 때 나타난다.

(6) Window Sort

 

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

 

출처 : 친절한 SQL 튜닝 - 조시형 지음