친절한 SQL 튜닝 5장 정리(소트 튜닝)

1. 소트 연산에 대한 이해

SQL 수행 중 가공된 데이터 집합이 필요할 경우 오라클은 PGA와 TEMP테이블스페이스를 활용한다.

소트는 기본적으로 PGA에 할당한 SORT AREA에서 이루어진다.

SORT AREA아 다 차면 디스크 TEMP 테이블스페이스를 활용한다.

메모리 소트 => 메모리 내에서 완료하는 것 Internal Sort

디스크 소트 => 디스크 공간까지 사용하는 것 External Sort

디스크 소트를 사용하는 순간 I/O가 발생하면서 성능이 나빠지고, 부분범위 처리를 불가능하게 함으로써 OLTP환경에서 애플리케이션 성능을 저하시키는 주요 원인으로 꼽힌다.

Sort Group By => 소형 알고리즘을 사용해 그룹별 집계를 수행할 때 사용 ( 메모지 4장을 준비하여, 메모지별로 그룹을 나누고 그에 해당하는 데이터를 끼워 넣는 방식이라고 생각하기 )

그룹을 많이 하지 않는다면( 메모지가 많지 않을 경우 ) Sort Area가 클 필요는 없다. ( temp테이블스페이스를 쓰지 않는다 라는 뜻 )

Group By 절 뒤에 Order By 절을 명시하지 않으면 대부분 hash group by방식으로 처리한다.

Sort Group By => 소트 알고리즘 사용
Hash Group BY => 해싱 알고리즘 사용( 그룹바이 컬럼의 해시 값으로 해시 버킷을 찾아 그룹별로 집계항목을 갱신하는 방식이다. )

실행계획에 sort group by가 표시되어 있다고 결과가 정렬되어 있다고 보장할 순 없다. 단지 소팅 알고리즘을 사용해 값을 집계한다라는 뜻일 뿐 데이터가 정렬되어 있다고 믿으면 안된다.

order by절이 없으면 논리적 순서를 무시하고 물리적으로 저장된 순서에 따라 값을 읽어서 정렬을 보장하지 않는다.

정렬된 그룹핑 결과를 얻고자 한다면, 실행계획에 sort group by라고 표시되어있더라도 반드시 order by를 명시해야 한다.

unnesting된 서브쿼리가 M쪽 집합이면 중복 레코드 제거를 위해 => Sort Unique 오퍼레이션이 나타난다.

distinct, union, minus, intersect같은 집합 연산자를 사용할 떄도 sort unique가 나타난다.

sort join => 소트 머지 조인을 수행할 때 나타난다.

window sort => 윈도우 함수를 수행할 때 나타난다.

---

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

union, minus, distinct연산자는 중복 레코드를 제거하기 위한 소트 연산을 발생시켜서 꼭 필요한 경우에만 사용하도록 한다.

union보다는 union all을 사용한다.

distinct, minus 연산자는 중복을 제거해야 해서 전체 데이터를 읽어야한다. 따라서 부분범위 처리가 불가능하고 많은 I/O가 발생한다. => 따라서 Exists 사용 권장 ( 데이터 존재 여부만 확인하기 때문에 전체를 읽지 않아서 성능적으로 좋다. 부분범위 처리가 가능하다는 뜻 )

---

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

인덱스는 항상 키 컬럼 순으로 정렬된 상태를 유지한다.

따라서 order by, group by절이 있어도 소트 연산을 생략할 수 있다.

부분범위 처리 활용한 튜닝 기법이 3-tier 환경에서도 여전히 유효한 이유 => Top N 쿼리를 쓰기 때문

Top N 쿼리는 전체 결과집합 중 상위 n개 레코드만 선택하는 쿼리다.

SELECT TOP 10 ...
OR
FETCH FIRST 10 ROWS ONLY... -> IBM DB2
OR
WHERE ROWNUM <= 10 -> 오라클

COUNT(STOPKEY) => ROWNUM으로 지정한 건수만큼 결과 레코드를 얻으면 거기서 바로 멈춘다. 이 기능을 TOP N STOPKEY 알고리즘 이라고 한다.

ROWNUM이 TON N STOPKEY알고리즘을 작동하게 하는 열쇠이므로 ROWNUM을 빼면 전체범위 스캔처리로 바뀐다.

MIN/MAX를 인덱스를 활용하여 값을 구하고자 한다면 해당 인자 컬럼이 인덱스에 포함되어 있어야 한다.

첫번째 조건을 만족하는 레코드 하나를 찾았을 때 바로 멈춘다는 뜻 => FIRST ROW STOPKEY 알고리즘

원하는 인덱스가 없을경우 조건절과 ORDER BY절에 인덱스 컬럼을 넣고 ROWNUM을 활용하면 MIN/MAX 결과값을 얻을 수 있다.

이력 조회를 구현할 때 FIRST ROW STOPKEY OR TOP N STOPKEY알고리즘이 동작할 수 있게 설계 및 구현하는 것이 튜너의 숙제다.

인덱스 컬럼을 가공하면 FIRST ROW STOPKEY 알고리즘이 작동하지 않는다.

부모테이블 조건절이 인라인 뷰 안쪽으로 파고 들어가는 것 => Predicate Pushing이라고 한다. ( 12c부터 사용가능 )

만약 이력 조회 패턴 중 대용량 데이터 조회라면 TOP N STOPKEY OR FIRST ROW STOPKEY보다는 윈도우 함수를 이용하는 것이 효과적이다.

그룹핑 연산에도 인덱스를 활용할 수 있다. ( SORT GROUP BY 생략 가능하다는 소리 )

Sort Group By생략 => SORT GROUP BY NOSORT 라고 실행계획에 나옴

---

4. SORA AREA를 적게 사용하도록 SQL 작성

1. 가공하지 않은 데이터를 정렬하고 가공한다.
2. * 사용보다 컬럼명을 지정해서 정렬하고자 하는 컬럼만 사용하도록 한다. 안그럼 모든 컬럼을 Sort Area에 저장한다.

Top N 소트 알고리즘
1. 키순서대로 학생 전체를 모은다. ( 1000명 가정 )
2. 맨 앞에 10명을 앞으로 부른다.
3. 나머지 990명을 맨 앞 10명과 비교한다.
4. 맨 앞 10명보다 키큰 학생이 나오면 교체한다.

Top N 소트 알고리즘은 소트 연산 횟수와 sort area사용량을 줄여준다.

RANK나 ROW_NUMBER 함수는 MAX 함수보다 소트 부하가 적다. TOP N 소트 알고리즘이 작동하기 때문이다.