소트 수행 과정

1. 소트 대상 집합을 SGA 버퍼캐시를 통해 읽어들임.
2. 일차적으로 PGA-Sort Area에서 정렬을 수행하고, 양이 많을 때는 정렬된 중간집합을 Temp 테이블 스페이스(디스크 영역)에 임시 세그먼트를 만들어 저장함.
   • Temp 영역에 저장해 둔 **중간 단계 집합을 ‘Sort Runs’**라고 부름
   • Temp 영역을 사용하는 SQL 수행 순간 성능이 크게 나빠짐
   • 소트 과정에서 발생하는 디스크 I/O는 Direct Path I/O 방식을 사용하므로 일반적인 디스크 I/O에 비해 가벼움 소트 과정에 디스크 I/O가 발생할 때, 나타나는 대기 이벤트는 direct path read temp, direct path write temp
   • Optimal 소트: 소트 연산이 메모리 내에서 이루어짐 → Sort Area만 사용하고 Temp 영역은 사용x
   • Onepass 소트: 정렬 대상 집합이 디스크에 한번만 쓰임
   • Multipass 소트: 정렬 대상 집합이 디스크에 여러 번 쓰임 → Sort Runs으로부터 읽은 데이터를 다시 디스크에 썼다가 읽어 들이는 과정을 반복 (3단계에서 Sort Runs에서 Chunk 단위로 읽어 들이는데, 중간 집합이 많아 Chunk 단위가 넘으면 발생)
3. 최종 결과집합을 위해 Merge 수행 오름차순일 경우, Sort Runs 내에 저장된 각 중간 집합의 가장 작은 값을 PGA로 읽어 들이다가 PGA가 찰 때마다 쿼리 수행 다음단계로 전달하거나 클라이언트에게 전달함.

<SQL Trace 예시>

<Autotrace 예시>

소트 오퍼레이션(실행계획 기준)

• Sort Aggregate
  • MIN/MAX 등 (집계)를 수행할 때
  • 실제 정렬 수행하지 않음(정렬 보장x)
• Sort Order By
  • Order by를 수행할 때
  • 정렬 수행함(정렬 보장o)
• Sort Group By
  • Group by+Order by를 수행할 때 → 소팅 알고리즘 사용
  • Order by를 명시하지 않으면 정렬 보장x → Order by를 명시하지 않아도 해당 실행계획이 나타날 수 있음
  • Group By 컬럼 값이 많지 않으면 Temp 테이블 스페이스 사용하지 않음
• Hash Group by
  • Oracle 10gR2 이후부터 Sort Group By에 Order by를 명시하지 않으면 채택하는 방식
  • 정렬 보장x
  • Group By 컬럼 값이 많지 않으면 Temp 테이블 스페이스 사용하지 않음
• Sort Unique (Hash Unique)
  • 중복 레코드 제거가 필요한 상황에 사용되는 소트 연산 → 기본적으로 소트 연산을 생략할 수 없음!
  • 발생하는 연산: Unnesting 된 서브쿼리, Distinct, Union, Minus, Intersect
    • Unnesting 서브쿼리가 M쪽 집합일 경우 메인쿼리와 조인 전 중복 제거 만약 Unnesting된 서브쿼리의 유일성(PK/Unique 제약 등)이 보장된다면 생략됨
    • Distinct 연산을 사용할 때 → Oracle 10gR2 이후부터는 Order By절이 없으면 Hash Unique 방식 채택 (Sort Unique 대신)

소트 연산이 발생하지 않도록 SQL 작성

1. UNION ALL

• UNION 연산에서 중복 레코드 제거를 위해 발생하는 소트 연산을 생략하는 방법
• UNION 상단/하단 집합을 조건절을 통해 상호 배타적으로 만들면 소트 연산 생각 가능(부분 범위 처리 가능해짐)