[SQLP] 소트 튜닝 - 1

1. 소트와 성능

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

SQL 수행 도중 Sort 연산은 메모리 공간에 Sort Area에 할당하고 정렬을 수행한다. Oracle에서는 PGA(Program Global Area) 영역에 할당한다.
그러나 많은 데이터를 사용할 때는 오버 플로우로 인해 디스크 공간을 사용해야 한다.

메모리 (In-Memory) 소트

• 전체 데이터의 정렬 작업을 할당받은 소트 영역 내에서 완료하는 것을 말하며, Internal Sort 또는 Optimal Sort 라고도 한다.

디스크(To-Disk) 소트

• 할당 받는 소트 영역 내에서 정렬을 완료하지 못해 디스크 공간까지 사용하는 경우를 말하여 External Sort라 한다.

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

Sort Aggregate

• 전체 로우 대상 집계를 수행할 때 나타남

select sum(sal), max(sal), min(sal) from emp

• Sort Aggregate 실행 계획

Execution Plan  
-------------------------------------------------------------  
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=4)  
1 0 SORT (AGGREGATE) (Card=1 Bytes=4)  
2 1 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=56)

Sort Order By

• 정렬된 결과 집합을 얻고자 할 때 나타남

select * from emp order by sal desc

• Sort Order By 실행계획

Execution Plan  
-------------------------------------------------------------  
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=14 Bytes=518)  
1 0 SORT (ORDER BY) (Cost=4 Card=14 Bytes=518)  
2 1 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes=518)

Sort Group By

• Sorting 알고리즘을 사용해 그룹별 집계를 수행할 때 나타남

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

Sort Group By 실행계획

Execution Plan ------------------------------------------------------------- 
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=11 Bytes=165) 
1 0 SORT (GROUP BY) (Cost=4 Card=11 Bytes=165) 
2 1 TABLE ACCESS (FULL) OF 'SCOTT.EMP' (TABLE) (Cost=3 Card=14 Bytes=210)

Oracle은 Hashing 알고리즘으로 집계를 수행하기도 하는데 실행계획은 다음과 같다

Execution Plan  
-------------------------------------------------------------  
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=11 Bytes=165)  
1 0 HASH (GROUP BY) (Cost=4 Card=11 Bytes=165)  
2 1 TABLE ACCESS (FULL) OF 'SCOTT.EMP' (TABLE) (Cost=3 Card=14 Bytes=210)

Sort Unique

• 결과 집합에서 중복 레코드를 제거할 때 나타난다.
• Union 연산자나 Distinct 연산자를 사용할 때가 대표적이다.

select distinct deptno from emp order by deptno

Sort Unique 실행계획

Execution Plan  
-------------------------------------------------------------  
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=3 Bytes=6)  
1 0 SORT (UNIQUE) (Cost=4 Card=3 Bytes=6)  
2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=13 Bytes=26)

Sort Join

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

select /*+ ordered use_merge(e) */ *  
from emp e, dept d  
where d.deptno = e.deptno

Sort Join 실행계획

Execution Plan  
-------------------------------------------------------------  
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=3K Bytes=177K)  
1 0 MERGE JOIN (Cost=11 Card=3K Bytes=177K)  
2 1 SORT (JOIN) (Cost=4 Card=13 Bytes=442)  
3 2 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=13 Bytes=442)  
4 1 SORT (JOIN) (Cost=7 Card=654 Bytes=19K)  
5 4 TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=654 Bytes=19K)

Window Sort

• 윈도우 함수를 수행할 때 나타난다.

select empno, ename, job, mgr, sal, row_number() over (order by hiredate)  
from emp

Windwo Sort 실행계획

Execution Plan
-------------------------------------------------------------  
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=13 Bytes=364)  
1 0 WINDOW (SORT) (Cost=4 Card=13 Bytes=364)  
2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=13 Bytes=364)StmtText  

다. 소트 튜닝 요약

• 소트 오퍼레이션은 Memory-Intensive일 뿐만 아니라 CPU-intensive 이기도 하다.
• 소트할 데이터 양이 많을 때는 DIsk I/O도 발생하므로 쿼리 성능이 나빠진다.
• 최대한 소트를 피하거나 최소화하도록 노력해보고 아래와 같은 방식을 취해보자
  • 데이터 모델 측면에서의 검토
  • 소트가 발생하지 않도록 SQL 작성
  • 인덱스를 이용한 소트 연산 대체
  • 소트 영역을 적게 사용하도록 SQL 작성
  • 소트 영역 크기 조정