1. 소트 튜닝 원리

SQL 튜닝에서 빠질 수 없는 요소가 소트 튜닝이다.
소트 오퍼레이션은 수행과정에서 CPU와 메모리를 많이 사용하고, 데이터량이 많을 때는 디스크 I/O 까지 일으킨다.

많은 서버 리소스를 사용하는 것도 문제지만 부분범위처리를 불가능하게 해 OLTP 환경에서 애플리케이션 성능을 저하시키는 주요인이다.

1. 소트 수행 과정

SQL 수행 도중 데이터 정렬이 필요할 때면 오라클은 PGA 메모리에 Sort Area를 할당하는데, 그 안에서 처리를 완료할 수 있는지 여부에 따라 소트를 두 가지 유형으로 나눈다.

• 메모리 소트

  전체 데이터의 정렬 작업을 메모리 내에서 완료하는 것을 말하며, 'Internal Sort' 라고도 한다.

• 디스크 소트

  할당받은 Sort Area 내에서 정렬을 완료하지 못해 디스크 공간까지 사용하는 경우를 말한다.
  'External Sort' 라고도 한다.

소트를 하는 세 가지 방법이 있다.

1. Optimal 소트

소트 오퍼레이션이 메모리 내인 Sort Area 내에서만 이루어진다. => 최적

데이터의 양이 많을 때 정렬된 중간 결과집합을 Temp 테이블스페이스의 Temp 세그먼트에 임시 저장한다.
=> 이것이 디스크 소트

Sort Area가 찰 때마다 Temp 영역에 저장해 둔 중간 단계의 집합을 'Sort Run' 이라고 한다.
정렬 완료 후 결과 집합을 머지해야 최종 결과 집합이 나온다.

이처럼, 정렬된 결과를 Temp 영역에 임시 저장했다가 다시 읽어들이는 디스크 소트가 발생하는 순간 SQL 수행 성능은 급격하게 나빠진다.

2. Onepass 소트

Sort Area에서의 정렬이 꽉차 정렬 대상 집합이 디스크에 한 번만 쓰인다. => 나쁘진 않음

3. Multipass 소트

크기가 너무 커서 정렬 대상 집합이 디스크에 여러 번 쓰인다. => 최악

2. 소트 오퍼레이션 측정

autotrace의 sorts (memory) 가 메모리 소트이고, sorts (disk) 가 디스크 소트이다.

소트과정에서 발생하는 디스크 I/O는 Direct Path I/O 방식을 사용하므로 버퍼 캐시를 경유하는 일반적인 디스크 I/O에 비해 무척 가볍다.

3. Sort Area

1. PGA (Process/Program/Private Global Area)

각 오라클 서버 프로세스는 자신만의 PGA 메모리 영역을 할당받고, 이를 프로세스에 종속적인 고유 데이터를 저장하는 용도로 사용한다.

PGA는 다른 프로세스와 공유되지 않는 독립적인 메모리 공간으로서, 래치 매커니즘이 필요 없어 똑같은 개수의 블록을 읽더라도 SGA 버퍼 캐시에서 읽는 것보다 훨씬 빠르다.

2. UGA (User Global Area)

전용 서버 방식으로 연결할 때는 프로세스와 세션이 1:1 관계를 갖지만, 공유 서버 방식으로 연결할 때는 1:M 관계를 갖는다.
즉, 세션이 프로세스 개수보다 많아질 수 있는 구조로서, 하나의 프로세스가 여러 개 세션을 위해 일한다.
이때, 각 세션을 위한 독립적인 메모리 공간이 필요한데, 이를 UGA라고 한다.

UGA는 서버 프로세스와의 연결 방식에 따라 그 위치가 달라지는데, 전용 서버 방식으로 연결할 때는 PGA, 공유 서버 방식으로 연결할 때는 SGA에 할당한다.
SGA에 할당했을 때는 Large Pool이 설정됐으면 Large Pool에, 그 외에는 Shared Pool에 할당된다.

• 하나의 프로세스는 하나의 PGA만을 갖는다.

• 하나의 세션은 하나의 UGA만을 갖는다.

• PGA에는 세션과 독립적인 프로세스만을 관리한다.

• UGA에는 프로세스와 독립적인 세션만을 관리한다.

• 거의 대부분 전용 서버 방식을 사용하므로 세션과 프로세스는 1:1 관계이고, 따라서 UGA도 PGA 내에 할당된다고 이해하면 된다.

3. CGA (Call Global Area)

PGA에 할당되는 메모리 공간으로는 CGA도 있다.
오라클은 하나의 데이터베이스 Call을 넘어서 다음 Call까지 계속 참조되어야하는 정보는 UGA에 담고,
Call이 진행되는 동안에만 필요한 데이터는 CGA에 담는다.

Sort Area는 어떤 메모리 영역에 할당할까??

=> Sort Area가 할당되는 위치는 SQL문 종류와 소트 수행 단계에 따라 다르다.

DML 문장은 하나의 Execute Call 내에서 모든 데이터 처리를 완료하며, Execute Call이 끝나는 순간 자동으로 커서가 닫힌다.
따라서 DML 도중 정렬한 데이터를 Call을 넘어서까지 참조할 필요가 없으므로 Sort Area를 CGA에 할당한다.

SELECT 문의 데이터 정렬은 상황에 따라 다르다.
SELECT 문장이 수행되는 가장 마지막 단계에서 정렬된 데이터는 계속 이어지는 Fetch Call에서 사용되어야 한다.

✅ 요약

1. DML 문장 수행 시 발생하는 소트 -> CGA에서 수행

2. SELCT 문장 수행 시

   => 쿼리 중간 단계의 소트
   CGA에서 수행. sort_area_retained_size의 제약이 있다면, CGA에서 소트 수행
   이 제약만큼의 UGA를 할당해 정렬된 결과를 담았다가 이후 Fetch Call에서 Array 단위로 전송

   => 결과집합을 출력하기 직전 단계에서 수행하는 소트
   sort_area_retaines_size 제약이 있다면, CGA에서 소트 수행
   이 제약만큼의 UGA를 할당해 정렬된 결과를 담았다가 이후 Fetch Call에서 Array 단위로 전송
   sort_area_retains-size 제약이 없다면, 곧바로 UGA에서 소트 수행

CGA에 할당된 Sort Area는 하나의 Call이 끝나자마자 PGA에 반환된다.
UGA에 할당된 Sort Area는 마지막 로우가 Fetch 될 때 비로소 UGA Heap에 반환되고, 거의 대부분 그 부모 Heap에도 즉각 반환된다.

4. 소트 튜닝 요약

소트 오퍼레이션은 메모리 집약적일뿐만 아니라 CPU 집약적이기도 하며, 데이터량이 많을 때는 디스크 I/O까지 발생시키므로 쿼리 성능을 좌우하는 가장 중요한 요소다.
특히, 부분범위처리를 할 수 없게 만들어 OLTP 환경에서 성능을 떨어뜨리는 주 요소이다.
따라서 소트가 발생하지 않도록 SQL을 작성해야 되고, 소트가 불가피하다면 메모리 내에서 수행을 완료할 수 있도록 해야 한다.

---

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

1. Sort Aggregate

로우를 대상으로 집계를 수행할 때 나타나는데, 'sort'라는 표현을 사용하지만 실제 소트가 발생하진 않는다.

2. Sort Order By

데이터 정렬을 위해 order by 오퍼레이션을 수행할 때 나타난다.

3. Sort Group By

sort group by는 소팅 알고리즘을 사용해 그룹별 집계를 수행할 때 나타난다.
=> group by + order by

✅ Hash Group By 와 비교

10gR2에서 hash group by 방식이 도입되면서, order by 절과 함께 명시하지 않으면 대부분
hash group by 방식으로 처리된다.
=> group by

hash group by는 정렬을 수행하지 않고 해싱 알고리즘을 사용해 데이터를 그룹핑한다.
읽는 로우마다 group by 컬럼의 해시 버킷을 찾아 그룹별로 집계항목을 갱신하는 방식이다.
sort group by와 hash group by의 차이는 그룹을 찾아가는 방식이 해시 알고리즘이냐 소팅 알고리즘이냐의 차이이다.

사실 오라클은 9i부터 이미 group by 결과가 보장되지 않는다고 여러 문서를 통해 공표했다.

결론적으로 정렬된 group by 결과를 얻고자 한다면, 실행계획에 'sort group by'라고 표시돼도 반드시 order by를 명시해줘야 한다.
=> group by만 써도 sort group by라고 뜨는 경우가 있다. ex) group by + distinct count 연산

4. Sort Unique

Unnesting된 서브쿼리가 M쪽 집합이거나 Unique 인덱스가 없다면, 그리고 세미 조인으로 수행되지도 않는다면 메인 쿼리와 조인되기 전에 sort unique 오퍼레이션이 먼저 수행된다.

만약 PK/Unique 제약 또는 Unique 인덱스를 통해, Unnesting된 서브쿼리의 Uniqueness이 보장된다면 sort unique 오퍼레이션은 생략된다.

union, minus, intersect 같은 집합 연산자도 sort unique 오퍼레이션이 나타난다.

distinct 연산도 sort unique 오퍼레이션이 나타난다.
=> 오라클 10gR2부터는 distinct 연산에서 order by 생략하면 hash unique 오퍼레이션이 나타난다.

5. Sort Join

sort join 오퍼레이션은 소트 머지 조인을 수행할 때 나타난다.

6. Window Sort

window sort는 분석함수를 수행할 때 나타난다.