[1] 소트와 성능
(1) 메모리 소트와 디스크 소트
1. 메모리 소트(In-Memory)
- Oracle은 소트 영역을 PGA 영역에 할당
- SQL Server는 버퍼 캐시에 할당
- 전체 데이터의 정렬 작업을 할당받은 소트 영역 내에서 완료하는 것.
- 'Internal Sort' 또는 'Optimal Sort'라고도 함.
2. 디스크(To-Disk) 소트
- 소트 시 메모리 공간이 부족할 경우 디스크 공간을 사용
- 할당받은 소트 영역 내에서 정렬을 완료하지 못해 디스크 공간까지 사용하는 것.
- 'External Sort'라고도 함.
- 디스크 기록 횟수에 따른 분류
- Onepass Sort : 정렬 대상 집합을 디스크에 한 번만 기록
- Multipass Sort : 정렬 대상 집합을 디스크에 여러 번 기록
(2) 소트를 발생시키는 오퍼레이션
1. Sort Aggregate
select sum(sal), max(sal), min(sal) from emp
---------------------------------------------------------------------------
| 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 |
---------------------------------------------------------------------------- 전체 로우를 대상으로 집계를 수행할 때 나타남
- 실제 소트가 발생하진 않는다.
- SQL Server 실행계획엔 'Stream Aggregate' 라고 표시됨.
2. Sort Order By
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 518 | 4 (25)| 00:00:01 |
| 1 | SORT ORDER BY | | 14 | 518 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------- 정렬된 결과집합을 얻고자 할 때 나타남.
3. Sort Group By
select deptno, job, sum(sal), max(sal), min(sal) from emp group by deptno, job
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 165 | 4 (25)| 00:00:01 |
| 1 | HASH GROUP BY | | 11 | 165 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 210 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------- Sorting 알고리즘을 사용해 그룹별 집계를 수행할 때 나타남
- 오라클은 Hashing 알고리즘으로 그룹별 집계를 수행하기도 함.
4. Sort Unique
select distinct deptno from emp order by deptno
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 9 | 5 (40)| 00:00:01 |
| 1 | SORT UNIQUE | | 3 | 9 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 42 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------- 선택된 결과집합에서 중복 레코드를 제거하고자 할 때 나타남.
- Union 연산자나 Distinct 연산자를 사용할 때가 대표적임.
5. Sort Join
select /*+ ordered use_merge(e) */ * from emp e, dept d where d. deptno = e. deptno
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 798 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 798 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 518 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | EMP_IDX | 14 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 4 | 80 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | DEPT | 4 | 80 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------- Sort Merge Join을 수행할 때 나타남.
6. Window Sort
- 윈도우 함수를 수행할 때 나타남.
(3) 소트 튜닝 특징
- 될 수 있으면 소트가 발생하지 않도록 쿼리 작성해야 하며 , 소트가 불가피하다면 메모리 내에서 수행을 완료할 수 있도록 해야 함.
- 소트 오퍼레이션은 메모리 집약적, CPU 집약적, 데이터량이 많을 때는 디스크 I/O까지 발생시킨다.
- 특히, 부분범위처리를 할 수 없게 만든다.
[2] 데이터 모델 측면에서의 검토
데이터 모델 측면에서의 검토가 필요한 경우
- 자주 사용되는 데이터 액세스 패턴을 고려하지 않은 채 물리 설계를 진행할 때
- M:M 관계의 테이블을 해소하지 않아 핵심 프로그램이 항상 소트 오퍼레이션을 수반하는 경우 등.
(1) 정상적인 데이터 모델
- 가입상품과 고객별상품라인 분리된 경우
(2) 비정상적인 데이터 모델
select 과금.고객id , 과금.상품id , 과금.과금액 , 가입상품.가입일시
from 과금
,(select 고객id , 상품id , min(가입일시) 가입일시
from 고객별상품라인
group by 고객id, 상품id) 가입상품
where 과금.고객id(+) = 가입상품.고객id
and 과금.상품id(+) = 가입상품.상품id
and 과금.과금연월(+) = :yyyymm- 자식 테이블에 통합
- 이 회사는 고객별 가입상품 레벨의 데이터 조회가 매우 빈번하게 일어나며, 그때마다 "고객별상품라인" 테이블을 group by 해야된다.
- group by, union, distinct 같은 연산자가 심하게 많이 사용되는 패턴을 보인다면 대개 데이터 모델이 잘 정규화되지 않았음을 암시
잘 정규화된 데이터 모델을 사용했을 때
select 과금.고객id , 과금.상품id , 과금.과금액 , 가입상품.가입일시
from 과금 , 가입상품
where 과금.고객id(+) = 가입상품.고객id
and 과금.상품id(+) = 가입상품.상품id
and 과금.과금연월(+) = :yyyymm[3] 소트가 발생하지 않도록 SQL 작성
(1) Union을 Union All로 대체
- union을 사용하면 옵티마이저는 상단과 하단의 두 집합 간 중복을 제거하려고 sort unique 연산을 수행
- union all은 중복을 허용하며 두 집합을 단순히 결합하므로 소트연산 불필요
1. Union
select empno, job, mgr from emp where deptno = 10
union
select empno, job, mgr from emp where deptno = 20;
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 152 | 6 (67)| 00:00:01 |
| 1 | SORT UNIQUE | | 8 | 152 | 6 (67)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| EMP | 3 | 57 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_IDX | 3 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 95 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | EMP_IDX | 5 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------2. Union All
select empno, job, mgr from emp where deptno = 10
union all
select empno, job, mgr from emp where deptno = 20;
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 152 | 4 (50)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 3 | 57 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_IDX | 3 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 95 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_IDX | 5 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
=> (2)[1] Distinct를 Exists 서브쿼리로 대체 : 입력한 과금연월(yyyymm) 이전에 발(2) Distinct를 Exists 서브쿼리로 대체
튜닝 전
select distinct 과금연월
from 과금
where 과금연월 <= :yyyymm
and 지역 like :reg || '%';튜닝 후
select 연월
from 연월테이블 a
where 연월 <= :yyyymm
and exists
(select 'x'
from 과금
where 과금연월 = a.연월
and 지역 like :reg || '%' )- 입력한 과금연월(yyyymm) 이전에 발생한 과금 데이터를 모두 스캔하고 중복값 제거
- 각 월별로 과금이 발생한 적이 있는지 여부만 확인하면 된다.
- 연월테이블을 먼저 드라이빙해 과금 테이블을 exists 서브쿼리로 필터링하는 방식
- [과금연월 + 연월??] 인덱스 구성해주면 최적으로 수행될 수 있음.
(3) 불필요한 Count 연산 제거
데이터 존재 여부만 확인하면 되는데 불필요하게 전체 건수를 Count하는 경우
튜닝 전
declare l_cnt number;
begin
select count(*) into l_cnt
from member
where memb_cls = '1'
and birth_yyyy <= '1950';
if l_cnt > 0 then
dbms_output.put_line('exists');
else
dbms_output.put_line('not exists');
end if;
end;튜닝 후
declare l_cnt number;
begin
select 1 into l_cnt
from member
where memb_cls = '1'
and birth_yyyy <= '1950'
and rownum <= 1;
dbms_output.put_line('exists');
exception
when no_data_found then
dbms_output.put_line('not exists');
end;
도광양회(韜光養晦) ‘빛을 감추고 어둠속에서 힘을 기른다’