소트 튜닝

소트 튜닝

SQL 수행 도중 가공된 데이터 집합이 필요할 때, 오라클은 PGA와 Temp 테이블 스페이스를 활용한다. 소트 머지 조인, 해시 조인, 데이터 소트와 그룹핑 등이 대표적이다. 

소트 수행 과정
1)메모리 소트(In-Memory Sort) : 전체 데이터의 정렬 작업을 메모리 내에서 완료하는 것을 말하며, 'Internal Sort'라고도 한다.
2)디스크 소트(To-Disk Sort) : 할당받은 Sort Area 내에서 정렬을 완료하지 못해 디스크 공간까지 사용하는 경우를 말하며, 'External Sort'라고도 한다.

소트 연산은 메모리 집악적일 뿐만 아니라 CPU 집약적이기도 하다. 부분범위 처리를 불가능하게 함으로써 OLTP 환경에서 애플리케이션 성능을 저하시키는 주요인이 되기도 한다. 될 수 있으면 소트가 발생하지 않도록 SQL을 작성해야 하고, 소트가 불가피하다면 메모리 내에서 수행을 완료할 수 있도록 해야 한다.

소트 오퍼레이션

1.Sort Aggregate
Sort Aggregate는 아래처럼 전체 로우를 대상으로 집계를 수행할 때 나타난다. Sort라는 표현을 사용하지만, 실제로 데이터를 정렬하진 않는다.  Sort Area를 사용한다는 의미로 이해하면 된다.

select sum(price), min(price), avg(price) from product;

1) Sort Area에 SUM, MAX, MIN, COUNT 값을 위한 변수를 각각 하나씩 할당한다.
2) 레코드를 하나씩 읽어 SUM, MAX, MIN, COUNT에 값을 할당 한다.
3) 레코드를 모두 다 읽고 나면  SUM, MAX, MIN, COUNT에 값이 저장되어 있다.

2.Sort Order By
Sort Order By는 데이터를 정렬할 때 나타난다.

select * from product order by price desc;

3.Sort Group By
소팅 알고리즘을 사용해 그룹별 집계를 수행할 때 나타난다. 정렬된 그룹핑 결과를 얻고자 한다면, 실행계획에 설명 Sort Group By라고 표시되더라도 반드시 Order By를 명시해야 한다.

select deptno, sum(price), max(price), min(price), avg(price)
from product
group by deptno
order by deptno;

4.Sort Unique
옵티마이저가 서브쿼리를 풀어 일반 조인문으로 변환하는 것을 '서브쿼리 Unnesting'이라고 한다. 서브쿼리가 메인쿼리와 조인하기 전에 중복 레코드부터 제거해야 한다. 이때 아래와 같이 Sort Unique 오퍼레이션이 나타난다.
UNION, MINUS, INTERSECT, DISTINCT 연산자를 사용해도 Sort Unique 오퍼레이션이 나타난다. 10R2 부터는 Distinct 연산도 Hash Unique 방식을 사용한다.

select col1, col2 from table where col3=1
union all
select col1, col2 from table where col3=2

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

select /*+ ordered use_merge(t2) */
from table1 t1, table2 t2
where t1.col1 = t2.col1

6.Window Sort
Window Sort는 윈도우 함수를 수행할 때 나타난다.

select col1, col2
  , avg(col3) over (partition by col3)
from table t

 

소트가 발생하지 않는 SQL작성

Union, Minus, Distinct 연산자는 중복 레코드를 제거하기 위한 소트 연산을 발생시키므로 꼭 필요한 경우에만 사용하고, 성능이 느리다면 소트 연산을 피할 방법이 있는지 찾아봐야 한다.

1. Union vs Union All
Union은 소트 작업을 수행하지만 Union All은 중복을 확인하지 않고 두 집합을 단순히 결합하므로 소트작업을
수행하지 않는다. 따라서 될 수 있으면 Union All을 사용해야 한다.
그런데 Union을 Union All로 변경하려다 자칫 결과 집합이 달라질 수 있으므로 주의해야 한다.

2.Exists 활용
3.조인방식변경
 인덱스가 존재하는 NL조인

 

인덱스를 이용한 소트 연산 생략

인덱스는 항상 키 컬럼 순으로 정렬된 상태를 유지한다. 이를 활용하면 SQL에 Order By 또는 Group By 절이 있어도 소트 연산을 생략할 수 있다. 여기에 Top N 쿼리 특성을 결합하면, 온라인 트랜잭션 처리 시스템에서 대량 데이터를 조회할 때 매우 빠른 응답 속도를 낼 수 있다.
Top N 쿼리는 전체 결과집합 중 상위 N개 레코드만 선택하는 쿼리다.