[1] 소트와 성능

(1) 메모리 소트와 디스크 소트

• 메모리 소트(In-Memory)
  • Oracle은 소트 영역을 PGA 영역에 할당한다.
  • SQL Server는 버퍼 캐시에 할당한다.
  • 전체 데이터의 정렬 작업을 할당받은 소트 영역 내에서 완료한다.
  • 'Internal Sort' 또는 'Optimal Sort'라고도 함.
• 디스크(To-Disk) 소트
  • 소트 시 메모리 공간이 부족할 경우 디스크 공간을 사용한다.
  • 할당받은 소트 영역 내에서 정렬을 완료하지 못해 디스크 공간까지 사용하는 것을 말한다.
  • 'External Sort'라고도 한다.
  • Onepass Sort : 정렬 대상 집합을 디스크에 한 번만 기록하는 것이다.
  • Multipass Sort : 정렬 대상 집합을 디스크에 여러 번 기록하는 것이다.

(2) 소트를 발생시키는 오퍼레이션

• Sort Aggregate
  • 전체 로우를 대상으로 집계를 수행할 때 나타난다.
  • 실제 소트가 발생하진 않는다.
  • SQL Server 실행계획엔 'Stream Aggregate' 라고 표시된다.
• Sort Order By : 정렬된 결과집합을 얻고자 할 때 나타난다.
• Sort Group By : Sorting 알고리즘을 사용해 그룹별 집계를 수행할 때 나타나며 오라클은 Hashing 알고리즘으로 그룹별 집계를 수행하기도 한다.
• Window Sort : 윈도우 함수를 수행할 때 나타난다.

(3) 소트 튜닝 요약

• 될 수 있으면 소트가 발생하지 않도록 쿼리 작성해야 한다.
• 소트가 불가피하다면 메모리 내에서 수행을 완료할 수 있도록 해야 한다.
• 소트 오퍼레이션은 메모리 집약적, CPU 집약적, 데이터량이 많을 때는 디스크 I/O까지 발생시킨다.
• ⭐️ 특히, 부분범위처리를 할 수 없게 만든다.

[2] 데이터 모델 측면에서의 검토

• 자주 사용되는 데이터 액세스 패턴을 고려하지 않은 채 물리 설계를 진행하는 경우
• M:N 관계의 테이블을 해소하지 않아 핵심 프로그램이 항상 소트 오퍼레이션을 수반하는 경우 등

[예시] 정상적인 데이터 모델

• 가입상품과 고객별상품라인 분리
  

[예시] 비정상적인 데이터 모델

• 자식 테이블에 통합되어있음
• 이 회사는 고객별 가입상품 레벨의 데이터 조회가 매우 빈번하게 일어나며, 그때마다 고객별상품라인 테이블을 group by 해야한다.
• group by, union, distinct 같은 연산자가 심하게 많이 사용되는 패턴을 보인다면
  대개 데이터 모델이 잘 정규화되지 않았음을 암시한다.
  

[예시] 잘 정규화된 데이터 모델을 사용했을 때

select 과금.고객id , 과금.상품id , 과금.과금액 , 가입상품.가입일시
from   과금 , 가입상품
where  과금.고객id(+) = 가입상품.고객id
and    과금.상품id(+) = 가입상품.상품id
and    과금.과금연월(+) = :yyyymm;

[3] 소트가 발생하지 않도록 SQL 작성

(1) Union을 Union All로 대체

• union을 사용하면 옵티마이저는 상단과 하단의 두 집합 간 중복을 제거하려고 sort unique 연산을 수행한다.
• union all은 중복을 허용하며 두 집합을 단순히 결합하므로 소트연산이 불필요하다.

(2)Distinct를 Exists 서브쿼리로 대체

[예시] 입력한 과금연월(yyyymm) 이전에 발생한 과금 데이터를 모두 스캔하고 중복값 제거

• 각 월별로 과금이 발생한 적이 있는지 여부만 확인하면 된다.
• 연월테이블을 먼저 드라이빙해 과금 테이블을 exists 서브쿼리로 필터링하는 방식
  • [과금연월 + 연월] 인덱스를 구성해주면 최적으로 수행될 수 있다.

(3)불필요한 Count 연산 제거

• 데이터 존재 여부만 확인하면 되는데 불필요하게 전체 건수를 Count하는 경우

declare l_cnt number;
begin
    select count(*) into l_cnt
    from   member
    where  memb_cls = '1' and    birth_yyyy <= '1950';
    if l_cnt > 0 then
        dbms_output.put_line('exists');
    else
        dbms_output.put_line('not exists');
    end if;
end;

[4] 인덱스를 이용한 소트 연산 대체

(1) Sort Order By 대체

• 인덱스를 사용하면 sort order by 연산을 대체할 수 있다.즉, 소트 오퍼레이션이 나타나지 않는다.
• 소트 대상 레코드가 무수히 많고 그 중 일부만 읽고 멈출 수 있을 때만 유용하다.

(2) Sort Group By 대체

• 선두 컬럼인 결합 인덱스나 단일 컬럼 인덱스를 사용하면 Sort Group By 연산을 대체 할 수 있다.
• SORT GROUP BY NOSORT라고 표시된다.

(3) 인덱스를 활용한 Min, Max 구하기

• 인덱스가 항상 정렬 상태를 유지한다는 특징을 이용하여 Min, Max 값 추출한다.
• ⭐️ 주의) max 함수 내에서 인덱스 컬럼을 가공하면 인덱스를 사용하지 못하게 될 수 있다.

[5] 소트 영역을 적게 사용하도록 SQL 작성

• 소트 연산이 불가피하다면 메모리 내에서 처리되게 하려고 노력해야 한다.

(1) 소트 완료 후 데이터 가공

• 가공된 결과치를 소트영역에 담는 것보다 가공되지 않은 상태로 정렬을 완료하고 나서 최종 출력할 때 가공 하는 것이 소트영역을 적게 사용한다.

(2)Top-N 쿼리

• 소트 연산(=값 비교) 횟수와 소트 영역 사용량을 최소화할 수 있다.
• [종목코드 + 거래일시] 인덱스가 존재하면 order by 연산을 대체할 수 있다.
• ⭐️rownum 조건으로 N건에서 멈춘다.
  • 실행계획의 'COUNT (STOPKEY)'가 그것을 의미한다.

  select * 
  from (select 거래일시, 체결건수, 체결수량, 거래대금
        from   시간별종목거래
        where  종목코드 = 'KR123456' and    거래일시 >= '20080304'
        order by 거래일시
        )
  where rownum <= 10

• Top-N 쿼리의 소트 부하 경감 원리
  • [종목코드 + 거래일시] 인덱스가 없으면 종목코드를 선두로 갖는 다른 인덱스를 사용하거나, Full Table Scan 방식으로 처리된다.
  • 이때 정렬 작업이 불가피하나 Top-N 쿼리로 소트 영역을 최소한으로 사용하게 할 수 있다.
  • Top 10 (rownum <= 10)이면, 10개의 레코드를 담을 배열만 할당(소트 영역 사용량 줄임)해서 처음 10개 레코드를 정렬된 상태로 담는다.
  • 이후 읽는 레코드에 대해서 맨 우측의 가장 큰값과 비교해서,그보다 작은 값이 나타날 때만 배열 내에서 다시 정렬을 시도하여 소트 연산 횟수를 줄인다.

[6] 소트 영역 크기 조정

• Oracle 8i에서는 sort_area_size 파라미터로 조정한다.
• Oracle 9i 부터 '자동 PGA 메모리 관리(Automatic PGA Memory Management)' 기능이 도입된다.
  • pga_aggregate_target 파라미터에 인스턴스 전체 이용 가능한 PGA 메모리 총량을 지정한다.
  • Oracle이 자동으로 각 세션에 메모리를 할당해 준다.
  • workarea_size_policy 를 auto로 설정하면 된다.(기본설정)
  • sort_area_size 파라미터는 무시된다.
    • 이는 수동으로 전환하여 배치 등 작업에 필요한 만큼의 소트 영역 크기를 늘림으로써성능을 향상시킬 수 있다.