[SQLP] 소트 튜닝 - 2

데이터 모델 측면에서의 검토

자주 사용하는 데이터 엑세스 패턴을 고려하지 않고 물리 설계를 진행하거나, M:M 관계를 해소하지 않아 핵심 프로그램이 항상 소트 오퍼레이션을 수행하여 성능이 저하되는 경우 흔히 접할 수 있다.

• 아래의 상황을 보면 PK 없거나, 적거나 아래의 가입상품 테이블 처럼 소수일 때, 테이블 간소화를 위해 고객별상품라인 테이블로 통합하는 상황이 있다.

• 정합성에는 이슈가 없겠지만, 자주 조회가 일어난다면 성능이 좋을리가 없다.

select 과금.고객id, 과금.상품id, 과금.과금액, 가입상품.가입일시  
from 과금,  
(
    select 고객id, 상품id, min(가입일시) 가입일시  
    from 고객별상품라인  
    group by 고객id, 상품id
) 가입상품  
where 과금.고= 가입상품.상품id  
and 과금.과금연월(+) = :yyyymm

• 만약 이전의 정규화한 데이터 모델을 사용한다면, 쿼리 가독성과 성능도 잡을 수 있다.
• 정규화가 제대로 이뤄지지 않아 생기는 성능 문제도 많다고 한다.
  • SQL에서 group by, union, distinct 등을 많이 사용하는 쿼리가 있으면 정규화가 제대로 되지 않을 가능성이 높다.

select 과금.고객id, 과금.상품id, 과금.과금액, 가입상품.가입일시  
from 과금, 가입상품  
where 과금.고객id(+) = 가입상품.고객id  
and 과금.상품id(+) = 가입상품.상품id  
and 과금.과금연월(+) = :yyyymm

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

가. Union을 Union All로 대체

• 옵티마이저는 union을 사용하면 각 집합 간 중복을 제거하려고 sort unique 연산을 수행한다.

 select empno, job, mgr 
 from emp 
 where deptno = 10  
 union  
 select empno, job, mgr 
 from emp 
 where deptno = 20;

union 실행계획

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=8 Bytes=120)
1 0 SORT (UNIQUE) (Cost=8 Card=8 Bytes=120)
2 1 UNION-ALL
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=4 Bytes=60)
4 3 INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (NON-UNIQUE) (Cost=1 Card=4)
5 2 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=4 Bytes=60)
6 5 INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (NON-UNIQUE) (Cost=1 Card=4)

 

union all 실행계획

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=8 Bytes=120)  
1 0 UNION-ALL  
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=4 Bytes=60)  
3 2 INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (NON-UNIQUE) (Cost=1 Card=4)  
4 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=4 Bytes=60)  
5 4 INDEX (RANGE SCAN) OF 'EMP_DEPTNO_IDX' (NON-UNIQUE) (Cost=1 Card=4)

나. Distinct를 Exists 서브쿼리로 대체

• 중복 쿼리를 exists 서브쿼리로 대체하려 처리할 수 있다.

select distinct 과금연월  
from 과금  
where 과금연월 <= :yyyymm  
and 지역 like :reg || '%'

Distinct 리소스 사용 계획

• 과금연월 (yyyymm) 이전에 발생한 과금 데이터를 모두 스캔하는 동안 1,586,208 개 블록을 읽었다.
• 최종적으로는 중복값을 제거하고 35건을 출력하는 매우 비효율적인 쿼리이다.

call count | cpu   | elapsed | disk  | query    | current | rows
-----------|-------|---------|-------|----------|---------|-----
Parse      | 1     | 0.00    | 0.00  | 0        | 0       | 0
Execute    | 1     | 0.00    | 0.00  | 0        | 0       | 0
Fetch      | 4     | 27.65   | 98.38 | 32,648   | 1,586,208 | 0     | 35
-----------|-------|---------|-------|----------|---------|-----
Total      | 6     | 27.65   | 98.38 | 32,648   | 1,586,208 | 0     | 35

Rows | Row Source Operation
-----|---------------------------------------------------
35   | HASH UNIQUE (cr=1,586,208 pr=32,648 pw=0 time=98,704,640 us)
9,845,517 | PARTITION RANGE ITERATOR PARTITION: 1 KEY (cr=1,586,208 pr=32,648)
9,845,517 | TABLE ACCESS FULL 과금 (cr=1,586,208 pr=32,648 pw=0 time=70,155,864 us)

 

쿼리를 아래와 같이 바꿀 수 있다.

select 연월  
from 연월테이블 a  
where 연월 <= :yyyymm  
and exists (  
    select 'x'  
    from 과금  
    where 과금연월 = a.연월  
    and 지역 like :reg || '%'  
)

Exists 리소스 사용 계획

• exists 서브쿼리는 메인 쿼리로부터 건건이 입력받은 값에 대한 조건을 만족하는 첫 번째 레코드를 만나는 순간 true를 반환하고 서브쿼리 수행을 마친다.
• 그렇기 때문에 과금 테이블에 [과금연월 + 지역] 순으로 인덱스를 구성하면 최적으로 수행될 수 있다.

call count | cpu   | elapsed | disk | query | current | rows
-----------|-------|---------|------|-------|---------|-----
Parse      | 1     | 0.00    | 0.00 | 0     | 0       | 0
Execute    | 1     | 0.00    | 0.00 | 0     | 0       | 0
Fetch      | 4     | 0.00    | 0.01 | 0     | 82      | 0     | 35
-----------|-------|---------|------|-------|---------|-----
Total      | 6     | 0.00    | 0.01 | 0     | 82      | 0     | 35

Rows | Row Source Operation
-----|---------------------------------------------------
35   | NESTED LOOPS SEMI (cr=82 pr=0 pw=0 time=19,568 us)
36   | TABLE ACCESS FULL 연월테이블 (cr=6 pr=0 pw=0 time=557 us)
35   | PARTITION RANGE ITERATOR PARTITION: KEY KEY (cr=76 pr=0 pw=0 time=853 us)
35   | INDEX RANGE SCAN 과금_N1 (cr=76 pr=0 pw=0 time=683 us)

다. 불필요한 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

Count 실행계획

• 26112 개의 Block I/O 가 발생하면서 ,17.56초나 소요됐다.
• 총 26112개 중 4742개나 되는 것이 성능을 저하시킨 주요 원인이다.

Call Count | CPU Time | Elapsed Time | Disk | Query | Current | Rows
-----------|----------|--------------|------|-------|---------|-----
Parse      | 1        | 0.000        | 0.000| 0     | 0       | 0    | 0
Execute    | 1        | 0.000        | 0.000| 0     | 0       | 0    | 0
Fetch      | 2        | 0.172        | 17.561| 4,742 | 26,112  | 0    | 1
-----------|----------|--------------|------|-------|---------|-----
Total      | 4        | 0.172        | 17.561| 4,742 | 26,112  | 0    | 1

Rows | Row Source Operation
-----|---------------------------------------------------
0    | STATEMENT
1    | SORT AGGREGATE (cr=26,112 pr=4,742 pw=0 time=17,561,372 us)
29,184| TABLE ACCESS BY INDEX ROWID MEMBER (cr=26,112 pr=4,742 pw=0 time=30,885,229 us)
33,952| INDEX RANGE SCAN MEMBER_IDX01 (cr=105 pr=105 pw=0 time=2,042,777 us)

• 이를 rownum 으로 확 줄일 수 있다.

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;

 

rownum 실행계획

Call Count | CPU Time | Elapsed Time | Disk | Query | Current | Rows
-----------|----------|--------------|------|-------|---------|-----
Parse      | 1        | 0.000        | 0.000| 0     | 0       | 0    | 0
Execute    | 1        | 0.000        | 0.000| 0     | 0       | 0    | 0
Fetch      | 2        | 0.000        | 0.000| 0     | 3       | 0    | 1
-----------|----------|--------------|------|-------|---------|-----
Total      | 4        | 0.000        | 0.000| 0     | 3       | 0    | 1

Rows | Row Source Operation
-----|---------------------------------------------------
0    | STATEMENT
1    | COUNT STOPKEY (cr=3 pr=0 pw=0 time=54 us)
1    | TABLE ACCESS BY INDEX ROWID MEMBER (cr=3 pr=0 pw=0 time=46 us)
1    | INDEX RANGE SCAN MEMBER_IDX01 (cr=2 pr=0 pw=0 time=26 us)