[DataBase] 고급 SQL 튜닝 - Sort 튜닝

** 데이터 진흥원 - SQL 전문가 가이드의 [과목3-6장 : 고급 SQL 튜닝] 내용

 

(1) Sort 튜닝 (2) DML 튜닝 (3) DB CALL 튜닝 (4) 파티셔닝 (5) 대용량 배치 프로세스 튜닝 (6) 복잡 구문 간결화 - 6개 중 (1번)

---

1) Sort 튜닝

> 데이터를 다루는데 있어 sort와 search는 언제나 숙제이고, 데이터가 클수록 메모리/CPU에 무리가 간다.

> 가능한 소트가 발생하지 않도록 작성하거나, 메모리 상에서 완료될 수 있도록 작성해야 한다.

> 실행 계획에 Sort의 유무부터 확인하여, 수정이 필요해 보이면 튜닝한다.

 

◼︎ Sort의 종류와 리소스

memory sort (1차적으로 메모리에서 시도) = Internal sort = optimal sort		disk sort (메모리 공간이 부족할 경우 디스크 사용) = external sort = onepass sort(1번만 기록)/ multipass sort(여러 번 기록)
oracle 리소스	SQL Server 리소스	oracle 리소스	SQL Server 리소스
PGA (private Global Area)	버퍼 캐시	Temp Tablespace	tempdb

 

◼︎ Sort 발생 Operation - 튜닝 중점 구간

Aggregation	select sum(sal), max(sal), min(sal) from emp Execution Plan --------------------------------------------------------------------- 0      SELECT STATEMENT Optimizer=ALL_ROWS 1   0    SORT(AGGREGATE) 2   1       TABLE ACCESS (FULL) OF 'EMP' (TABLE)
Order By	select * from emp order by sal desc Execution Plan --------------------------------------------------------------------- 0      SELECT STATEMENT Optimizer=ALL_ROWS 1   0    SORT(ORDER BY) 2   1       TABLE ACCESS (FULL) OF 'EMP' (TABLE)
Group By	select deptno, job, sum(sal), max(sal), min(sal) from emp group by deptno, job Execution Plan --------------------------------------------------------------------- 0      SELECT STATEMENT Optimizer=ALL_ROWS 1   0    SORT(GROUP BY) 2   1       TABLE ACCESS (FULL)
UNIQUE	select distinct deptno from emp order by deptno Execution Plan --------------------------------------------------------------------- 0      SELECT STATEMENT Optimizer=ALL_ROWS 1   0    SORT(UNIQUE) 2   1       TABLE ACCESS (FULL)
JOIN	select /*+ ordered use_merge(e) */ * from emp e, dept d where d.deptno = e.deptno Execution Plan --------------------------------------------------------------------- 0      SELECT STATEMENT Optimizer=ALL_ROWS 1   0    MERGE JOIN 2   1      SORT(JOIN) 3   2        TABLE ACCESS (FULL) OF 'EMP' 4   1      SORT(JOIN) 5   2        TABLE ACCESS (FULL) OF 'DEPT'
window sort	select empno, ename, job, sal, row_number() over (order by hiredate) from emp Execution Plan --------------------------------------------------------------------- 0      SELECT STATEMENT Optimizer=choose 1   0    WINDOW (Sort) 2   1       TABLE ACCESS (FULL) OF 'EMP'

 

◼︎ Sort 튜닝 방안

방법	내용
데이터 모델 검토	데이터 정규화가 수행되지 않아 M:M 형태의 구조를 가지면 sort연산이 다수 발생할 수밖에 없다. 정규화를 고려하여, group by, union, distinct와 같은 연산을 제거한다.
소트 제거	EX01_union의 union all 대체 union은 중복을 제거하기 위해 sort가 수행된다.반면, union all은 중복을 허용하여 sort하지 않는다. 따라서 중복 데이터가 없는 데이터임이 분명하다면 union all로 대체한다. EX02_Distinct의 Exists 대체 - 조금 귀찮긴 하지만 확실히 효과가 있는 방법
	[변경 전] select distinct 과금연월   from 과금  where 과금연월 <= :yyyymm       and 지역 like :reg || '%' -> 과금 테이블이 큰 경우 전체 sort가 발생한다. -> 예시 쿼리과 같이 단발성 쿼리라면 굳이 진행 X	[변경 후] select 연월    from 연월테이블 a where 연월 <= :yyyymm      and exists (select 'x' from 과금                             where 과금연월 = a.연월                                   and 지역 like :reg || '%') -> 연월 테이블 & 과금에 [과금연월&지역] 인덱스 필요 -> 귀찮지만 확실히 효율적이다.
인덱스를 통한 소트 대체	(1) order by, (2) group by, (3) min/max에 필요한 열들로 인덱스가 존재하면 sort가 생략된다. 이 때, 인덱스를 추가로 생성할 생각이라면, sort 연산의 부하를 측정하여 유불리를 판단해야 한다.
소트 영역 최소화	(1) sort와 데이터 변형 구문의 구분 select lpad(상품번호,30) || lpad(상품명, 30) || lpad(고객ID,10) || lpad(고객명,20) || lpad(주문일시)   from 주문상품 where 주문일시 between :start and :end  order by 상품번호 -> lpad를 진행한 총 100바이트 이상의 열을 sort 열에 담는다. select lpad(상품번호,30) || lpad(상품명, 30) || lpad(고객ID,10) || lpad(고객명,20) || lpad(주문일시)   from (select 상품번호,상품명,고객ID,고객명,주문일시               from 주문상품             where 주문일시 between :start and :end              order by 상품번호) -> 소트가 끝난 후 가공함으로 sort영역을 훨씬 적게 사용한다. ------------------------ (2) Top N Stopkey top N이 동작하는 원리는 N 크기의 배열을 생성하여 정렬한 후, 각 행을 가장 오른쪽 값(배열의 max)과 비교하여 크면 버리고, 작으면 배열 안에 집어넣는다. (이러면 복잡도는 O에 수렴하고, 전체 sorting하지 않을 수 있다.) -> rownum을 1~10까지 구하라면 잘 하지만, 90~100을 구하라고 하면 못하는 이유
	[SQL Server] select top 10 거래일시, 체결건수, 체결수량    from 시간별종목거래 where 종목코드='A'      and 거래일시 >= '20240501' -> 실행 계획에 [종목코드 + 거래일시]가 있으면 best -> 없더라도 위에서 설명한 Top n sort로 처리됨	[Oracle] select * from(    select 거래일시, 체결건수, 체결수량       from 시간별종목거래    where 종목코드='A'         and 거래일시 >= '20240501'      order by 거래일시) where rownum <= 10
	** 윈도우 함수에서도 max 보다 rank() row_number()를 사용하면 top n sort가 사용되어 더 빠를 수 있다.
소트 영역 크기 조정	최근의 버전들에서는 자동으로 조정되는 측면이 있으나, 과거 버전에서는 alter session set sort_area_size = 10485760; 과 같은 구문으로 직접 수정했다.

---