[SQLP] 소트 튜닝
소트 작업 순서
1) SGA의 버퍼캐시에서 소트 대사 데이터를 읽어 들인다
2) PGA의 Sort area 에서 정렬을 한다.
3) Sort 대상의 양이 많을 때에는 정렬 중간의 집합을 Temp 에서 임시 세그먼트(disk)로 저장한다.
이를 Sort run 이라고 한다.
Sort run 이 발생하면 disk에 내려쓰는 작업이기 때문에 속도가 느려진다.
>> 이를 위해 소트 튜닝이라는 개념이 나타난다.
대부분은 인덱스로 처리를 한다.
소트 튜닝의 종류
1) Optimal sort: 정렬 대상이 적어서, sort area에서 작업을 마무리 한다.
2) Onpass sort: 하나의 소트 작업에 대해서 정렬 대상의 집합을 디스크에 한 번만 기록한다. >> sort run 발생
3) Multipass sort: 하나의 소트 작업에 대해서 디스크에 여러 번 기록하는 경우 >> sort run 발생
소트 연산 종류
1) sort aggregate operator
2) sort group by operator
3) sort unipue operator
4) sort order by operator
5) sort hash group by operator
소트 튜닝 : 인덱스 사용
sort aggregate : min,max 함수로 소트 연산 대체가 가능하다
- 인덱스 생성하여 튜닝
예) 인덱스 내에서 sort 하는 것과 인덱스를 사용 안하고 sort 하는 것을 비교
##인덱스 사용 전
explain plan for
select max(sal) from emp;
select * from table(dbms_xplan.display)
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
| 2 | TABLE ACCESS FULL| EMP | 14 | 56 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
##인덱스 사용 후
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
| 2 | INDEX FULL SCAN (MIN/MAX)| IDX_TEST | 1 | 4 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
sort order by 대체(중요★ ★ ★ ★ ★ ★ ★ )
- 결합 인덱스의 생성 공식
조건절 선택도가 낮은 컬럼 + 정렬 대상 컬럼
정렬 대상 컬림이 맨 뒤에 와야 한다!
예)
select * from emp where deptno=10 order by sal desc;
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 190 | 3 (34)| 00:00:01 |
| 1 | SORT ORDER BY | | 5 | 190 | 3 (34)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 190 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IDX_EMP_DEPTNO | 5 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPTNO"=10)
##deptno=10 인걸 선행 컬럼으로 생성해야한다
create index idx_test2 on emp(deptno, sal);
##인덱스 생성할 때, 선행 컬럼인 deptno을 먼저 기입함
explain plan for
select * from emp where deptno=10 order by sal desc;
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 190 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | EMP | 5 | 190 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN DESCENDING| IDX_TEST2 | 5 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
## 값이 많이 없어서 그다지 효과적인 건 안보이지만 cost가 줄어든 것은 알 수 있다.
sort grouby by 대체
group by 컬럼이 포함된 결합 인덱스나 단일 컬럼 인덱스를 넣으면 된다.
실기 예제
Distinct 대신 Exists 사용하기
Distinct 를 할 경우 모든 테이블을 다 읽어야 하지만 exist 를 사용하면 semi join 하나만 찾으면 끝난다. 따라서 exist 를 사용하면 sort 연산을 대체할 수 있다.
예시)
select distinct 과금연월
from 과금
where 과금연월<=:yyyymmdd
and 지역 like:reg||'%'
## 과금연월이 1,0 값이 나올 텐데 1= 과금을 하였다 0 = 과금안했다.
## 결론적으로 과금을 했는지 안했는지를 알아보는 것이다.
select 연월
from 연월테이블
where dusdnjf<=:yyyymm
and exist(
select 'x'
from 과금
whwere 과금연원=a.연월
and 지역 like :reg||'%');
#exist 로 sort 연상 생략을 통해 튜닝을 진행할 수 있다.