6. Sort Area를 적게 사용하도록 SQL 작성

만약 소트 오퍼레이션 처리가 불가피하다면 메모리 내에서 처리를 완료할 수 있게 해야하고, Sort Area의 크기를 늘리는 방법도 있지만 그 전에 Sort Area를 적게 사용하도록 하는 방법을 찾아야 한다.

1. 소트를 완료 후 데이터 가공하기

가공하지 않은 상태로 정렬을 완료하고 나서 최종 출력할 때 가공하는 것이 Sort Area 사용을 최소화하는 방법이다.

2. Top-N 쿼리

✅ Top-N 쿼리 작동 시

select *
from (
	select wdate, wcount, wno, wprice
    from w
    where wcode = 'JY123'
    and wdate >= '20020625'
    order by wdate
)
where rownum <= 10;

오라클에서 위 쿼리를 [wcode + wdate] 인덱스를 사용한다면 옵티마이저는 인덱스를 이용해 order by 연산을 대체할 수 있다.

또한 rownum 조건을 사용해 n건에서 멈추도록 했으므로 조건절에 부합하는 레코드가 아무리 많아도 매우 빠른 속도를 낼 수 있다. (실행계획에 sort order by stopkey 표시)

✅ Top-N 쿼리 작동 안할 시

select *
from  (
	select a.*, rownum no
    from (
        select wdate, wcount, wno, wprice
        from w
        where wcode = 'JY123'
        and wdate >= '20020625'
        order by wdate
  	) a
 where no <= 10;

이 경우는 같은 양의 데이터를 읽고 정렬을 수행했지만 Top-N 쿼리 알고리즘이 작동하지 않아 디스크 소트를 이용해야 될 수도 있다.
실행계획상에는 stopkey가 표기되지 않는다.

3. 분석함수에서의 Top-N 쿼리

window sort 시에도 rank()나 row_number()을 쓰면 Top-N 쿼리 알고리즘이 작동해 max() 등의 함수를 쓸 때보다 소트 부하를 경감시켜 준다.

---

7. Sort 크기 조정

Sort Area 크기 조정을 통한 튜닝의 핵심은, 디스크 소트가 발생하지 않도록 하는 것을 1차 목표로 삼고 불가피할 때는 Onepass 소트로 처리되도록 하는 것이다.

오라클 9i부터 PGA 메모리 관리 방식을 지원한다.

Work Area

데이터 정렬, 해시 조인, 비트맵 머지, 비트맵 생성 등을 위해 사용하는 메모리 공간이다.
8i까지는 이들 Work Area의 기본 값을 관리자가 지정하고 조정했지만, 9i부터는 '자동 PGA 메모리 관리' 기능이 도입돼어 사용자가 일일이 그 크기를 조정하지 않아도 된다.

SGA는 sga_max_size 파라미터로 설정된 크기만큼 공간을 미리 할당한다.
PGA는 자동 PGA 메모리 관리 기능을 사용한다고 해서 pga_aggregate_target 크기만큼의 메모리를 미리 할당해 두지는 않는다.
이 파라미터는 workarea_size_policy를 auto로 설정한 모든 프로세스들이 할당받을 수 있는 Work Area의 총량을 제한하는 용도로 사용된다.

오라클 8i 이전에는 프로세스를 위해 할당된 PGA 공간을 프로세스가 해제될 때까지 OS에 반환하지 않았다.
9i에서 자동 PGA 메모리 관리 방식이 도입되면서 프로세스가 더 이상 사용하지 않는 공간을 즉각 반환함으로써 다른 프로세스가 사용할 수 있도록 한다.