소트 튜닝

1. 소트 수행 원리

• SQL 수행 도중 데이터 정렬이 필요할 때면 오라클은 PGA 메모리에 Sort Area를 할당하는데, 그 안에서 처리를 완료할 수 있는지 여부에 따라 전체 데이터의 정렬 작업을 메모리 내에서 완료하는 것을 메모리 소트라고 하며 할당받은 Sort Area 내에서 정렬을 완료하지 못해 디스크 공간까지 사용하는 경우를 디스크 소트라고 한다. 디스크 소트는 Direct Path I/O 방식으로 사용하긴 하지만 발생하는 순간 SQL 수행 성능이 크게 나빠진다.
• 각 오라클 서버 프로세스는 다른 프로세스와 공유되지 않는 독립적인 메모리 공간인 PGA 메모리 영역을 할당받고 고유 데이터를 저장하는 용도로 사용한다. 세션을 위한 독립적인 메모리 공간도 필요한데 이를 UGA(User Global Area)라고 한다. UGA는 전용 서버 방식으로 연결할 때는 PGA에 위치하고, 공유 서버 방식으로 연결할 때는 SGA에 할당된다. 또한 하나의 데이터베이스 Call을 넘어서 다음 Call까지 계속 참조되어야 하는 정보는 UGA에 담고 Call이 진행되는 동안에 필요한 데이터를 PGA에서 할당되는 메모리 공간인 CGA(Call Global Area)에 담는다.
• DML 문장은 Sort Area를 CGA에 할당한다.
• SELECT문은 데이터 정렬 상황에 따라 다르지만 sort_area_retained_size 제약이 없다면 마지막 소트는 UGA에서 그 전 단계는 CGA에서 처리한다. 
• 소트 튜닝에서 중요한 점은 데이터량이 많을 대는 디스크 I/O까지 발생시키며 부분범위처리를 할 수 없기에 OLTP 환경에서 성능을 떨어뜨리는 주 요인이 되기 때문에 가능하다면 소트가 발생하지 않도록 SQL을 작성하고 불가피하다면 메모리 내에서 수행하도록 해야 한다.

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

• 전체 로우를 대상으로 집계를 수행할 때 SORT AGGREGATE를 수행하지만 실제 소트는 발생하지 않는다.
• order by 오퍼레이션을 수행하면 SORT ORDER BY가 수행되며 소트가 발생한다.
• group by 오퍼레이션을 수행하면 SORT GROUP BY가 수행되면서 소트가 발생한다. order by절을 함께 명시하지 않으면 대부분 hash group by 방식으로 처리되며 소트는 발생하지 않는다. 또한 실행계획에 sort group by라고 표시되더라도 정렬된 결과가 나오지 않으니 정렬된 그룹 데이터가 필요하면 반드시 order by를 명시해야 한다.
• SORT UNIQUE는 Unnesting 된 서브쿼리가 M 쪽 집합이거나 Unique 인덱스가 없으며 세미 조인으로 수행되지 않으면 메인 쿼리와 조인되기 전에 sort unique 오퍼레이션이 수행된다. 집합 연산자와 distinct 연산에 order by가 있으면 sort unique 오퍼레이션이 수행된다. distinct 연산에 order by가 없으면 hash unique 방식으로 수행되며 소트는 없다.
• 소트 머지 조인을 수행하면 SORT JOIN 오퍼레이션을 수행하며 분석함수를 사용하면 WINDOW SORT 오퍼레이션을 수행한다.

3. 데이터 모델 측면에서의 검토

• SQL에 group by, union, distinck 같은 연산자가 불필요하게 많이 사용되는 패턴을 보인다면 대게 데이터 모델이 잘 정규화되지 않았음을 암시하며 데이터 모델 이상으로 발생한 데이터 중복을 제거하려다 보니 소트 오퍼레이션을 수행하게 되는 것이다.

4. 소트가 발생하지 않도록 SQL 작성

• 쓸데없이 union을 남발하지 말고 distinct 연산보다 exists 서브쿼리로 대체하는 것이 성능상 더 뛰어나다. 

5. 인덱스를 이용한 소트 연산 대체

• 소트 머지 조인에서 Outer 테이블 조인 칼럼에 인덱스가 있을 때 sort join 오퍼레이션을 생략한다.
• 서브쿼리에 사용된 테이블이 Unique 인덱스를 갖는다면 Unnesting 되었을 때 sort unique 오퍼레이션을 생략한다.
• Sort Order By를 대체하려면 인덱스가 조건절에 사용되고 order by 절에 인덱스 칼럼만 포함되어야 한다.
• Sort Group By를 대체하려면 선두칼럼인 인덱스를 group by 절에 사용하면 된다.
• 때때로 옵티마이저가 인덱스를 이용하지 않는 편이 낫다고 판단하고 Full Scan과 sort 연산을 수행할 수도 있기에 힌트를 사용하자.

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

• 소트를 완료하고 나서 데이터를 가공하는 것이 Sort Area를 훨씬 적게 사용한다. 인라인뷰를 적극 사용하자.
• Top-N 쿼리(rownum)로 작성하면 소트 연산 횟수를 최소화시켜 Sort Area를 훨씬 적게 사용한다. 또한 분석함수 사용 시 rank()나 row_number()를 사용하면 Top-N 쿼리 알고리즘이 작동해 max() 등 함수를 쓸 때보다 소트 부하를 경감시켜 준다.

7. Sort Area 크기 조정

• 디스크 소트가 발생하지 않도록 하고 불가피할 때는 Onepass 소트로 처리되도록 Sort Area 크기를 조정할 수 있다. PGA 메모리는 자동 PGA 메모리 관리 기능에 의해 사용자가 일일이 그 크기를 조정할 필요 없고 DBA가 PGA 메모리 총량만 지정하면 된다. 하지만 트랜잭션이 거의 없는 야간에 대량의 배치 Job을 수행할 때는 수동 방식으로 변경하여 직접 크기를 조정하는 것이 효과적일 수 있다.

 

- 참고 서적 : 오라클 성능 고도화 원리와 해법 2 -