소트 튜닝 - SQL전문가 가이드 p.689~p.693

1. 소트 튜닝 방안

 1) 데이터 모델 측면에서의 검토

 2) 소트가 발생하지 않도록 SQL 작성

 3) 인덱스를 이용한 소트 연산 대체

 4) 소트 영역을 적게 사용하도록 SQL 작성

 5) 소트 영역 크기 조정

 

2. 메모리 소트와 디스크 소트

- SQL 수행 도중 Sort 오퍼레이션으 필요할 때마다 DBMS는 정해진 메모리 공간에 Sort Area를 할당하고 정렬을 수행한다. Oracle은 Sort Area를 PGA(Private Global Area) 영역에 할당하고, SQL Server는 버퍼 캐시에 할당한다.

- 소트 오퍼레이션 수행 중에 메모리 공간이 부족할 때는 디스크 공간을 사용한다. Oracle에서는 Temp Tablespace를 이용하고, SQL Server에서는 tempdb를 이용한다.

- 대량의 데이터를 정렬할 땐 디스크 소트가 불가피하다.

- 전체 대상 집합을 디스크에 기록했다가 다시 읽는 작업을 여러 번 반복하는 경우 SQL 수행 성능은 극도로 나빠진다.

 

 

구분	설명
메모리(In-Memory) 소트	전체 데이터의 정렬 작업을 할당 받은 소트 영역 내에서 완료하는 것을 말하며, ‘Internal Sort’ 또는 ‘Optimal Sort’라고도 한다.
디스크(To-Disk) 소트	할당 받은 소트 영역 내에서 정렬을 완료하지 못해 디스크 공간까지 사용하는 경우를 말하며, ‘External Sort’ 라고도 한다. 디스크에 임시 저장했다가 다시 읽는 작업을 반복한 횟수에 따라 디스크 소트를 다음 두 가지로 구분하기도 한다. *One-pass Sort : 정렬 대상 집합을 디스크에 한 번만 기록 *Multi-pass Sort  : 정렬 대상 집합을 디스크에 여러 번 기록

 

3. 소트를 발생시키는 오퍼레이션

 1) Sort Aggregate - 전체 로우를 대상으로 집계를 수행할 때

  SQL> SELECT SUM(SAL), MAX(SAL), MIN(SAL) FROM EMP;

 2) Sort Order by - 정렬된 결과집합을 얻고자 할 때

  SQL> SELECT * FROM EMP ORDER BY SAL DESC;

 

 3) Sort Group by - Sorting 알고리즘을 사용해 그룹별 집계를 수행할 때

  SQL> SELECT DEPTNO, JOB, SUM(SAL), MAX(SAL), MIN(SAL)

FROM EMP

GROUP BY DEPTNO, JOB

;

 

 4) Sort Unique - 선택된 결과집합에서 중복 레코드를 제거하고자 할 때(Union 연산자, Distinct 연산자)

  SQL> SELECT DISTINCT DEPTNO

FROM EMP

ORDER BY DEPTNO

;

 

 5) Sort Join - Sort Merge Join을 수행할 때

  SQL> SELECT /*+ ordered use_merge(e) */

FROM EMP E, DEPT D

WHERE D.DEPTNO = E.DEPTNO

;

 

 6) Window Sort - 윈도우 함수를 수행할 때

  SQL> SELECT EMPNO, ENAME, JOB, MGR, SAL, ROW_NUMBER() OVER (ORDER BY HIREDATE)

FROM EMP

;

 

4. 소트 오퍼레이션은 메모리 집약적(Memory-intensive) 일뿐만 아니라 CPU 집약적(CPU-intensive)이기도 하며, 데이터량이 많을 때는 디스크 I/O까지 발생시키므로 쿼리 성능을 크게 떨어뜨린다. 특히, 부분범위처리를 할 수 없게 만들어 OLTP 환경에서 성능을 떨어뜨리는 주요인이 되곤 한다. 될 수 있으면 소트가 발생하지 않도록 SQL을 작성해야 하고, 소트가 불가피하다면 메모리 내에서 수행을 완료할 수 있도록 해야 한다.