1. 튜닝 배경
- 대상 쿼리 내용 : 특정 기간(보통 1달) 내에 이루어진 오더에 대한 세금계산서를 조회
- 문제 상황 : 특정 거래처에 대해서만 세금계산서를 조회하는 상황에서 조회 속도가 현저히 떨어짐
- 원인 파악
- Table A(드라이빙 테이블)와 Table B(드리븐 테이블)를 조인할 때, 다음의 2가지 상황에서 옵티마이저는 Table B의 인덱스를 다르게 채택한다.
- 거래처 조건이 없을 때 : Primary Key
- 거래처 조건이 있을 때 : 결합인덱스 idx05 (거래처코드, 작업부서, 오더구분)
- 즉, where절 조건에 거래처코드가 포함되면서 옵티마이저가 드리븐 테이블의 인덱스를 pk -> idx05로 변경하였다.
- Table A(드라이빙 테이블)와 Table B(드리븐 테이블)를 조인할 때, 다음의 2가지 상황에서 옵티마이저는 Table B의 인덱스를 다르게 채택한다.
2. 쿼리 분석
조인방식
- nested loops : 드라이빙 테이블을 읽고, 드라이빙 테이블과 드리븐 테이블의 조인 키를 가지고 드리븐 테이블을 읽으며 행을 연결.
- 두 테이블의 연결 횟수는 드라이빙 테이블의 데이터 개수와 비례하므로 행 수가 적은 테이블을 드라이빙 테이블로 결정해야 함.
- 두 테이블의 조인 키에 드리븐 테이블의 인덱스가 포함되어 있어야 함.
- hash join : 드라이빙 테이블의 행에 대한 해시 값을 만들고, 드리븐 테이블을 읽으며 해시 값에 일치하는 행을 연결.
수행된 쿼리 Autotrace 분석
| 구분 | 조인방식 | 조인키 | Table A(Driving) index | Table B(Driven) index |
|---|---|---|---|---|
| 협력사 조건 없는 쿼리 | nested loops | A.배차번호=B.운임번호 | A_Idx05(운송일자) | B_PK(운임번호) |
| 협력사 조건 있는 쿼리 | hash join | A.배차번호=B.운임번호 | A_Idx05(운송일자) | B_Idx05(거래처코드,작업부서,오더구분) |
협력사 조건이 없는 쿼리 : nl방식으로 조인
- Table A가 드라이빙 테이블로서 먼저 읽히며, 이 때 A_idx05로 인덱스 block에 접근하여 조건에 맞는 행만 가지고 온다.
- 추출된 Table A의 행을 기준으로 드리븐 테이블인 Table B의 행 매칭을 시도한다. 추출된 A의 행 만큼 Table B로의 연결을 시도하기 때문에 A에서 추출된 행이 적은 것이 유리하다.
- 연결 횟수 : A 추출 행
- Table B로 매칭을 시도할 때, B_pk로 인덱스 block에 접근하여 조건에 맞는 B의 행만을 A와 매칭시킨다.
- 작업을 빠르게 수행하기 위해 드리븐 테이블(Table B)의 인덱스가 Table A,B의 조인키에 포함하여야 한다.
- 드리븐 테이블의 인덱스가 조인키에 포함되지 않는 경우 nl 조인을 사용할 수 없다.
협력사 조건이 있는 쿼리 : hash join방식으로 조인
- Table A가 드라이빙 테이블로서 먼저 읽히며, 이 때 A_idx05로 인덱스 block에 접근하여 조건에 맞는 행만 가져온다. 위의 nl 방식과 동일
- 추출된 Table A 행의 조인키 값으로 hash value를 생성한다.
- Table B로 매칭을 시도할 때, B_idx05로 인덱스 block에 접근하여 조건에 맞는 행만 가져온다.
- 추출된 Table B 행의 조인키 값을 hash value를 생성하여, 앞에서 추출된 Table A의 hash value와 일치 여부에 따라 매칭 시킨다.
결론
- nl join 방식을 사용하든 hash join 방식을 사용하든 Table B를 읽어들일 때 pk 대신 결합인덱스 idx05(거래처코드,작업부서,오더구분)을 사용하는 것을 비효율적이라 판단하였다.
- 따라서 옵티마이저가 협력사코드 컬럼을 인식하지 못하도록 하여 다시 pk를 사용할 수 있게 조치하고자 했다.
- 이 때 서프레싱을 사용하였다.
- 서프레싱이란 컬럼을 의도적으로 변형함으로써 옵티마이저가 인덱스를 사용하지 못하도록 하는 작업이다.
- 결합인덱스(IDX05)의 구성요소 거래처코드(CUST_CD) 컬럼에 SUBSTR 함수를 적용시켜 인덱스를 사용하지 못하도록 하였다.
기존 쿼리
WHERE CUST_CD = :CUST_CD.튜닝 쿼리
WHERE SUBSTR(CUST_CD,1,10) = :CUST_CD3. 튜닝 결과
plan 읽는 법
cardinality: 해당 열에 대한 테이블에 고유한 값의 개수. 0일 경우 NULLcost: CPU 점유, DISK I/O, 수행시간 등을 종합적으로 판단한 예상 지수
- DB에 따라서 통계정보를 수집하지 않는 경우가 있으므로 튜닝 근거로 판단하기 어렵다
last_cr_buffer_gets: 읽어온 블록 갯수
- 메모리에 블록이 있을 경우 갯수가 줄어들 수 있으므로 튜닝 근거로 판단하기 어렵다
last_elapsed_time: 경과시간
- 튜닝 근거가 되는 핵심 요소이다
기존의 쿼리와 튜닝한 쿼리의 AUTOTRACE를 비교해보면 다음과 같다.
a. 데이터 1000건 이하 (조건: ㄷㅅ물류 22.12.01 ~ 22.12.31)
- COST 상승 (
7 -> 1,112) - 읽어온 블록수 증가(
51,968 -> 116,638) - 경과시간 감소 (
1,707,141-> 1,277,872)
| 구분 | 소요시간 | 행 수 | 카디널리티(cardinality) | 비용(cost) | 읽어온블록수(last_cr_buffer_gets) | 경과시간(last_elapsed_time) |
|---|---|---|---|---|---|---|
| 기존쿼리(hash) | 16sec | 679행 | 2 | 7 | 51,968 | 1,707,141 |
| 튜닝쿼리(nl) | 7sec | 679행 | 615 | 1,122 | 116,638 | 1,277,872 |
b. 데이터 4000건 이상 (조건: ㅊㅎ물류 22.09.01 ~ 22.09.30)
- COST 상승 (
7 -> 1,112) - 읽어온 블록수 감소(
480,054 -> 246,767) - 경과시간 1/100로 감소 (
331,123,384-> 3,669,925)
| 구분 | 소요시간 | 행 수 | 카디널리티(cardinality) | 비용(cost) | 읽어온블록수(last_cr_buffer_gets) | 경과시간(last_elapsed_time) |
|---|---|---|---|---|---|---|
| 기존쿼리(hash) | 309sec | 4,072행 | 2 | 7 | 480,054 | 331,123,384 |
| 튜닝쿼리(nl) | 3sec | 4,072행 | 615 | 1,122 | 246,767 | 3,669,925 |
4. 마무리
서프레싱으로 옵티마이저가 택하는 인덱스를 변경하여 결과적으로 경과시간이 1/100까지 감소했다. 굿!
(의문점1) 비용(cost) 증가했는데 왜 경과시간(last_elapsed_time) 감소?
해당 DB는 통계정보를 자동으로 수집하지 않기 때문에 통계가 매우 오래 되어 COST가 정확하지 않을 것이다. DB에 따라서 통계정보를 수집하지 않는 경우가 있으므로 COST만을 튜닝 근거로 사용하기는 어렵다.
통계 정보 자동수집을 하지 않는 DB가 생각보다 많다. 예를 들어, 자동 수집을 하는 경우 어느 날 특정 통계 정보로 인해 실행계획이 바뀌어버리는 경우가 많다. 그렇게 되면 운영 안정성이 매우 떨어지게 되기 때문이다.
(의문점2) 읽어온블록수(last_cr_buffer_gets)와 경과시간(last_elapsed_time)은 항상 비례하는가?
필요한 블록이 이미 메모리에 올려져 있는 경우 경과시간이 적게 걸릴 수 있다. 블록이 메모리에 없는 경우 디스크 I/O 시간이 추가적으로 발생하기 때문에 더 효율적인 쿼리도 일시적으로 경과시간이 오래 걸릴 수 있다.
따라서 항상 비례한다고 할 수 없다.
references
