01 소트 수행 원리
(1) 소트 수행 과정
데이터 정렬이 필요할 때 오라클은 PGA 메모리에 Sort Area 를 할당하는데, 그 안에서 처리를 완료할 수 있는지 여부에 따라 소트를 두가지 유형으로 나눈다.
메모리 소트: 전체 데이터의 정렬 작업을 메모리 내에서 완료하는 것을 말하며, Internal Sort 라고도 한다.
디스크 소트: 할당받은 Sort Area 내에서 정렬을 완료하지 못해 디스크 공간까지 사용하는 경우를 말하며, External Sort 라고도 한다.
- Optimal 소트: 소트 오퍼레이션이 메모리 내에서만 이루어짐
- Onepass 소트: 정렬 대상 집합이 디스크에 한 번만 쓰임
- Multipass 소트: 정렬 대상 집합이 디스크에 여러 번 쓰임
(2) 소트 오퍼레이션 측정
(3) Sort Area
데이터 정렬을 위해 사용되는 Sort Area 는 소트 오퍼레이션이 진행되는 동안 공간이 부족해질 때마다 청크 단위로 조금씩 할당된다.
sort_area_retained_size 는 데이터 정렬을 끝내고 나서 결과집합을 모두 Fetch 할 때까지 유지할 Sort Area 크기를 지정한다.
PGA (Process/Program/Private Global Area): 다른 프로세스 와 공유되지 않는 독립적인 메모리 공간
UGA (User Global Area): 각 세션을 위한 독립적인 메모리 공간
* 하나의 프로세스는 하나의 PGA를 갖는다.
* 하나의 세션은 하나의 UGA를 갖는다.
* PGA에는 세션과 독립적인 프로세스만의 정보를 관리한다.
* UGA에는 프로세스와 독립적인 세션만의 정보를 관리한다.
* 거의 대부분 전용 서버 방식을 사용하므로 세션과 프로세스는 1:1 관계고,
따라서 UGA도 PGA 내에 할당된다고 이해하면 쉽다.
CGA (Call Global Area): PGA 에 할당되는 메모리 공간으로 오라클은 하나의 데이터베이스 Call을 넘어서
다음 Call 까지 계속 참조되어야 하는 정보는 UGA에 담고, Call이 진행되는 동안에만 필요한 데이터는 CGA에 담는다.
CGA는 Parse Call, Execute Call, Fetch Call마다 매번 할당 받는다.
Call이 진행되는 동안 Recursive Call이 발생하면 그 안에서도 Parse, Execute, Fetch 단계별로 CGA가 추가로 할당된다.
CGA에 할당된 공간은 하나의 Call이 끝나자마자 해제돼 PGA로 반환된다.
* CGA : Call이 진행되는 동안만 필요한 정보 저장
* UGA : Call을 넘어서 다음 Call까지 계속 참조되는 정보 저장
* Sort Area 할당 위치
SQL 문 종류와 소트 수행 단계에 따라 다르다.
(4) 소트 튜닝 요약
* 데이터 모델 측면에서의 검토
* 소트가 발생하지 않도록 SQL 작성
* 인덱스를 이용한 소트 연산 대체
* Sort Area를 적게 사용하도록 SQL 작성
* Sort Area 크기 조정
02 소트를 발생시키는 오퍼레이션
(1) Sort Aggregate
실제 소트가 발생하지는 않는다.
(2) Sort Order By
데이터 정렬을 위해 order by 오퍼레이션을 수행할 때 나타난다.
(3) Sort Group By
소팅 알고리즘을 사용해 그룹별 집계를 수행할 때 나타난다.
* Hash Group By 와 비교
10gR2에서 hash group by 방식이 도입되면서, order by 절을 함께 명시하지 않으면 대부분 hash group by 방식으로 처리된다.
* Group By 결과의 정렬 순서
정렬된 group by 결과를 얻고자 한다면, 실행계획에 설령 'sort group by' 라고 표시되더라도 반드시 order by 를 명시해야 한다.
(4) Sort Unique
Unnesting 된 서브쿼리가 M쪽 집합이거나 Unique 인덱스가 없다면, 그리고 세미조인으로 수행되지도 않는다면
메인 쿼리가 조인되기 전에 sort unique 오퍼레이션이 먼저 수행된다.
(5) Sort Join
소트 머지 조인을 수행할 때 나타난다.
(6) Window Sort
분석함수를 수행할 때 나타난다.
03 데이터 모델 측면에서의 검토
불합리한 데이터 모델이 소트 오퍼레이션을 유발하는 경우를 흔히 접할 수 있다.
튜닝 과정에서 조사된 SQL에 group by, union, distinct 같은 연산자가 불필요하게 많이 사용되는 패턴을 보인다면
대개 데이터 모델이 잘 정규화되지 않았음을 암시한다.
데이터 모델 이상으로 발생한 데이터 중복을 제거하려다 보니 소트 오퍼레이션을 수행하게 되는 것이다.
04 소트가 발생하지 않도록 SQL 작성
union -> union all
distinct -> exists 서브 쿼리
05 인덱스를 이용한 소트 연산 대체
인덱스는 항상 키 컬럼 순으로 정렬된 상태를 유지하므로 이를 이용해 소트 오퍼레이션을 생략할 수 있다.
(1) Sort Order By 대체
(2) Sort Group By 대체
(3) 인덱스가 소트 연산을 대체하지 못하는 경우
06 Sort Area 를 적게 사용하도록 SQL 작성
(1) 소트를 완료하고 나서 데이터 가공하기
(2) Top-N 쿼리
* Top-N 쿼리의 소트 부하 경감 원리
* 효과 측정 : Top-N 쿼리가 작동할 때
* 효과 측정 : Top-N 쿼리가 작동하지 않을 때
(3) 분석함수에서의 Top-N 쿼리
07 Sort Area 크기 조정
(1) PGA 메모리 관리 방식의 선택
(2) 자동 PGA 메모리 관리 방식 하에서 크기 결정 공식
(3) 수동 PGA 메모리 관리 방식으로 변경 시 주의사항
manual 모드로 설정한 많은 세션에서 Sort Area 와 Hash Area를 아주 큰 값으로 설정하고
실제 매우 큰 작업을 동시에 수행한다면 가용한 물리적 메모리가 고갈돼
페이징이 발생하면서 시스템 전체 성능을 크게 떨어뜨릴 수 있다.
(4) PGA_AGGREGATE_TARGET 의 적정 크기
*OLTP : (총 물리메모리 x 80%) x 20%
*DSS : (총 물리메모리 x 80%) x 50%
(5) Sort Area 할당 및 해제