[친절한 SQL 튜닝] 소트 튜닝
소트 수행 과정
기본적으로 PGA에 할당한 Sort Area에서 이루어진다. 다만 Sort Area가 다 차면 디스크 Temp 테이블스페이스를 활용한다
- 메모리 소트 : 전체 데이터의 정렬 작업을 메모리 내에서 완료(Internal Sort)
- 디스크 소트 : 할당받은 Sort Area 내에서 정렬을 완료하지 못해 디스크 공간까지 사용하는 경우(External Sort)
소트 대상 집합을 SGA 버퍼캐시를 통해 읽어들이고, 일차적으로 Sort Area에사 정렬 시도
양이 많으면 템프페이블 스페이스에 임시 세그먼트 만들어 저장
Sort Area가 찰 때 마다 Temp영역에 저장해 두는 단계 집할을 Sort Run이라고 부른다
소트 연산은 메모리 집약적일 뿐 아니라 CPU 집약적
소트 오퍼레이션
1. Sort Aggregate : 실제 데이터 정렬을 하지는 않지만 Sort Area 사용
2. Sort Order By
3. Sort Group By
Hash Group By
해싱 알고리즘을 사용해 버킷을 찾아 그룹별로 집계 항목 갱신
4. Sort Unique
5. Sort Join 소트 머지 조인 시
6. Window Sort
Union, Union All
Union은 중복 제거를 하므로 Union All보다 수행시간이 길다 다만 Union 하는 두 집합이 서로 상호 배타적이라고하면 Union 대신 Union All를 사용하는게 더 낫다
Exists
중복 레코드를 제거할 목적으로 Distinct를 사용하는데 Exists를 사용하게 되면 서브쿼리의 데이터 존재여부만 확인하게 되므로 부분 범위 처리가 가능해진다
인덱스를 이용한 소트 연산 생략
인덱스는 항상 키 컬럼 순으로 청렬된 상태를 유지한다
이를 활용하면 Order By, Group By 절이 있어도 소트 연산을 생략 할 수 있다
여기에 Top N 쿼리 특성을 결합하면 온라인 트랜잭션 처리 시스템에서 매우 빠른 응답 속도를 낼 수 있다
인덱스 선두 컬럼이 “종목코드 + 거래일시” 순으로 구성되지 않은 경우 소트 연산 생략 불가
인덱스 선두 컬럼이 “종목코드 + 거래일시” 순으로 구성 된 경우
실행 계획에 Sort Order By 항목이 없어졌다
최소/최대 값 구하기
인덱스는 정렬돼 있으므로 이를 이용하면 전체 데이터를 읽지 않고도 최소 또는 최대값을 쉽게 찾을 수 있다
인덱스 맨 왼쪽으로 내려가서 첫 번째 읽는 값이 최소값, 맨 오른쪽으로 내려가 읽는 첫 번째 값이 최대값이다
1. 조건절 컬럼과 MAX 컬럼이 모두 인덱스에 포함돼 있고, 인덱스 선두 컬럼이 모두 = 조건인 경우
인덱스가 DEPTNO + MGR + SAL 구성된 경우 이다
인덱스가 DEPTNO + SAL + MGR 인경우 DEPTNO 가 액세스 조건 MGR이 필터 조건으로 조건을 만족하는 레코드 한개를 찾았을 때 멈추게 된다
인덱스가 SAL + DEPTNO + MGR 인경우 DEPTNO, MGR이 필터 조건으로 인덱스 가장 우측에서 스캔 시작하여 조건을 만족하는 레코드를 하나 찾으면 멈춘다
인덱스가 DEPTNO + SAL인 경우 인덱스를 통해 MGR이 필터링 되지 않으므로 테이블에서 필터링이 되어야하므로 DEPTNO = 30 인 모든 레코드를 읽어 필터링 후 MAX값을 구한다. First Row StopKey 알고리즘이 동작하지 않는다
Top N 쿼리 이용 최소/최대 값 구하기
Top N 쿼리에 동작하는 Top N Stopkey 알고리즘은 모든 컬럼이 인덱스에 포함돼 있지 않아도 잘 동작한다
DEPTNO = 30을 만족하는 가장 오른쪽 레코드 부터 역순으로 스캔하면서 테이블 엑세스 하다가 MGR 조건이 일치하는 레코드를 찾았을 때 멈춘다
이력조회
복합한 이력 조회
해당 방법은 인덱스 컬럼을 가공했으므로 First Row Stopkey 알고리즘이 동작하지 않는다
해당 방법은 상태변경이력을 여러번 조회하는 비효율은 맀으나 First Row Stopkey 알고리즘이 잘 작동한다. 다만 컬럼이 많이진다면 SQL 문은 위 처럼 복잡해진다
Index_des 힌트 활용
인덱스를 역순으로 읽도록 index_desc 힌트를 사용하고, 첫번째 레코드에서 바로 멈추도록 rownum≤1조건절 사용
해당 쿼리의 경우 predicate pushing 쿼리 변환이 동작하여 인라인 뷰 안쪽으로 장비번호 조건절이 들어가면서 동작한다. Top N Stopkey 알고리즘이 작동하지 않아 성능이 느려질 수 는 있지만 쿼리 결과 집합은 보장된다
인덱스 활용 패턴은 랜덤 I/O 발생량 만큼 성능도 비례해서 느려지므로 대량 데이터 조회 시에는 윈도우 함수를 활용하는 것이 full scan과 해시조인을 이용하기 때문에 과거 이력까지 읽지만 인덱스를 이용하는 방식보다 빠르다
이처럼 KEEP 절을 활용할 수 도 있다
Sort Group By 생략
region이 선두 컬럼인 인덱스를 이용하면 Sort Group By 연산을 생략할 수 있다
인덱스가 정렬되어있으므로 순서대로 읽어 처리하다 다른 값을 만나게 되면 운반단위에 집계값을 저장하는 식으로 처리한다
인덱스를 이용해 Nosort 방식으로 Group By를 처리하면 부분 범위 처리가 가능해진다
소트 데이터 줄이기
1번의 경우 lpad에 의해 가동된 결과 집합을 sort area에 저장하므로 2번의 가공하지 않는 상태로 정렬을 하는 sql이 sort area를 더 적게 사용한다
1번의 경우 예수금원장의 모든 컬럼을 sort area에 저장하므로 계좌번호, 총예수금만 저장하는 2번 sql이 더 적응 sort area를 사용한다
Top N 쿼리를 사용하게 되면 N개의 sort area 공간을 사용하게 되므로 소트 부하가 적다
sort order by 오퍼레이션이 나타났지만 stopkey로 표시되면 top n 소트 알고리즘이 동작하였다
이 알고리즘이 동작하면서 sort area 사용량을 최소화 해준다
그 결과 sorts (disk) 항목을 사용하지 않았다
Top N 쿼리 아닐 시 소트 부하
해당 쿼리를 사용하면서 쿼리는 조금 간결해졌으나 실행계획에 stopkey가 사라지고 pr, pw가 증가한 것을 확인할 수 있다
이를 통해 Top N 소트 알고리즘이 동작하지 않았고, 정렬을 수행하는데 disk를 사용하였다
정렬을 위해 temp 테이블스페이스를 이용했다
분석함수에서 Top N 소트
윈도우 함수 중 rank나 row_number함수는 max 함수보다 소트 부하가 적다
Top N 소트 알고리즘이 동작하기 때문이다
max를 이용하는 경우 pr 13456, pw 8960 이 발생하였다
rank함수를 사용하는 경우 pr 40, pw 40으로 physical read, write가 발생하긴했으나 max 함수 사용시 보다 줄었고, 수행시간도 8초 가량 차이가 발생했다