오라클 성능 고도화 원리와 해법 II (2016년)
05.조건절 이행
- (A=B)이고 (B=C)이면 (A=B)이다 처럼 추론을 통해 새로운 조건절을 내부적으로 생성해주는 쿼리 변환 과정
예시
select * from dept d, emp e
where e.job = 'MANAGER'
and e.deptno = 10
and d.deptno = e.deptno;
----------------------------------------------------------------
| Id | Operation | Name | E-Rows |
----------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | NESTED LOOPS | | 20 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 |
|* 3 | INDEX UNIQUE SCAN | DEPT_PK | 1 |
|* 4 | TABLE ACCESS BY INDEX ROWID| EMP | 20 |
|* 5 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 98 |
----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("D"."DEPTNO"=10)
4 - filter("E"."JOB"='MANAGER')
5 - access("E"."DEPTNO"=10)
쿼리에서 e.deptno = 10만 적어주었는데 d.deptno = 10 조건도 붙은 것을 확인할 수 있다.
더불어 d.deptno = e.deptno 조건도 제거되었다.
만약 이러한 조건절 이행 때문에 부작용이 생긴다면 아래와 같이 조건절 이행이 되지 않게 하여야 한다.
select * from scott.dept d, scott.emp e
where e.job = 'MANAGER'
and e.deptno = 10
and d.deptno = e.deptno + 0
조건절 이행이 효과를 발휘하는 사례
select *
from 상품이력 a, 주문 b
where b.거래일자 between '20090101' and '20090131'
and a.상품번호 = b.상품번호
and b.거래일자 between a.시작일자 and a.종료일자
위와 같은 쿼리에서 상품이력의 탐색 범위는
상품이력.시작일자 <= '20090131'
상품이력.종료일자 >= '20090101'
과 같고 해당 조건을 명시적으로 넣어주면 상품이력을 읽는 데이터량을 줄여줄 수 있다.
다만 9i부터는 옵티마이저가 이와 같은 조건을 묵시적으로 추가하여 최적화를 수행한다.
튜닝 사례1
SELECT IP주소, IP연결일자, 시작IP주소, 종료IP주소
,ISP명, IP등록일자, IP사용기관ID, IP사용기관명, IP사용시도명
,사용기관주소, 사용기관우편번호, IP책임자명, IP책임자전화번호
FROM IP주소목록
WHERE 시작IP주소 >= :strtIpAddr
AND 종료IP주소 <= :endIpAddr
입력값 형태
:strtIpAddr := '192.168.000.001'
:endIpAddr := '192.168.000.255'
인덱스 상황
IP주소목록_PK : IP주소
IP주소목록_X01 : 시작IP주소
수행 결과는 아래와 같다.
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.000 0.000 0 0 0 0
Execute 1 0.000 0.001 0 0 0 0
Fetch 9 32.820 1922.797 341291 6940276 0 106
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 32.820 1922.798 341291 6940276 0 106
Rows Row Source Operation
------- ---------------------------------------------------
106 TABLE ACCESS BY INDEX ROWID IP주소목록 (cr=6940276 pr=341291 pw=0 ...)
11985 INDEX RANGE SCAN IP주소목록_X01 (cr=27980 pr=27968 pw=0 time=33450495 us)
이 쿼리가 32분씩이나 걸리게 만든 핵심 부하 요인이 과도한 테이블 Random 액세스이므로 종료IP주소 조건을
인덱스에 추가하는 것만으로도 상당한 개선효과가 있을 것이다.
다만 종료IP주소 컬럼을 추가하면 인덱스 스캔량은 더 늘어날 것이고 바인드 변수가 아래와 같이 입력된다면
인덱스 스캔량 자체도 무시할 수 없을 정도로 늘어난다.
:strtIpAddr := '001.001.000.001'
:endIpAddr := '001.001.000.255'
여기에서 옵티마이저는 모르는 종료IP주소가 시작IP주소보다 크다는 사실을 추가해주면
SELECT IP주소, IP연결일자, 시작IP주소, 종료IP주소
,ISP명, IP등록일자, IP사용기관ID, IP사용기관명, IP사용시도명
,사용기관주소, 사용기관우편번호, IP책임자명, IP책임자전화번호
FROM IP주소목록
WHERE 시작IP주소 >= :strtIpAddr
AND 종료IP주소 <= :endIpAddr
AND 시작IP주소 <= 종료ip주소
세 조건을 조합하면 아래와 같은 관계가 성립된다.
:strtIpAddr <= 시작IP주소 <= 종료IP주소 <= :endIpAddr
이를 시작IP주소와 종료IP주소 컬럼 기준으로 다시 분해해보면
WHERE 시작IP주소 BETWEEN :strtIpAddr AND :endIpAddr
AND 종료IP주소 BETWEEN :strtIPAddr AND :endIpAddr
와 같은 조건식이 만들어진다.
바인드변수를 대입해보면
WHERE 시작IP주소 BETWEEN '192.168.000.001' AND '192.168.000.255'
AND 종료IP주소 BETWEEN '192.168.000.001' AND '192.168.000.255'
시작IP주소만으로도 검색범위가 많이 줄어드는 것을 알 수 있다.
위와 같은 조건식으로 다시 수행해보면..
SELECT IP주소, IP연결일자, 시작IP주소, 종료IP주소
,ISP명, IP등록일자, IP사용기관ID, IP사용기관명, IP사용시도명
,사용기관주소, 사용기관우편번호, IP책임자명, IP책임자전화번호
FROM IP주소목록
WHERE 시작IP주소 BETWEEN '192.168.000.001' AND '192.168.000.255'
AND 종료IP주소 BETWEEN '192.168.000.001' AND '192.168.000.255'
call count cpu elapsed 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 9 0.000 0.001 0 55 0 106
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 11 0.000 0.001 0 55 0 106
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
106 FILTER (cr=55 pr=0 pw=0 time=37 us)
106 TABLE ACCESS BY INDEX ROWID IP주소목록 (cr=55 pr=0 pw=0 time=34 us)
106 INDEX RANGE SCAN IP주소목록_X01 (cr=13 pr=0 pw=0 time=654 us)
위와 같이 between으로 풀어서 기술하지 않더라도 종료IP주소가 시작IP주소보다 크다는 사실만 기입해도
위와 같은 조건을 옵티마이저가 내부적으로 생성해낸다.
SELECT IP주소, IP연결일자, 시작IP주소, 종료IP주소
,ISP명, IP등록일자, IP사용기관ID, IP사용기관명, IP사용시도명
,사용기관주소, 사용기관우편번호, IP책임자명, IP책임자전화번호
FROM IP주소목록
WHERE 시작IP주소 >= :strtIpAddr
AND 종료IP주소 <= :endIpAddr
AND 시작IP주소 <= 종료ip주소
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(:ENDIPADDR>=:STRTIPADDR)
2 - filter("종료IP주소"<=:ENDIPADDR AND "종료IP주소">=:STRTIPADDR
AND "시작IP주소"<="종료IP주소")
3 = access("시작IP주소">=:STRTIPADDR AND "시작IP주소"<=:ENDIPADDR)
튜닝 사례2
아래 쿼리에서 주문상세 테이블에만 상수 조건식이 제공되었다.
select /*+ ordered use_nl(o) use_nl(d) index(0) index(d) */
c.고객명,o.주문일자,o.주문번호,o.배송지,d.상품번호,d.상품가격,d.주문수량
from 고객 c, 주문 o, 주문상세 d
where o.고객번호 = c.고객번호
and d.고객번호 = o.고객번호
and d.주문일자 = o.주문일자
and d.주문번호 = o.주문번호
and d.주문일자 = to_char(sysdate, 'yyyymmdd')
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 1001 0.125 0.084 0 15098 0 10000
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 1003 0.125 0.084 0 15098 0 10000
Rows Row Source Operation
------ ----------------------------------------------------
10000 TABLE ACCESS BY INDEX ROWID 주문상세 (cr=15098 pr=0 pw=0 time=280245 us)
11001 NESTED LOOPS (cr=5098 pr=0 pw=0 time=99435 us)
1000 NESTED LOOPS (cr=2035 pw=0 pw=0 time=20134 us)
10 TABLE ACCESS FULL 고객 (cr=16 pr=0 pw=0 time=156 us)
1000 TABLE ACCESS BY INDEX ROWID 주문 (cr=2019 pr=0 pw=0 time=14212 us)
1000 INDEX RANGE SCAN 주문_PK (cr=1019 pr=0 pw=0 time=6140 us) ...... 1
10000 INDEX RANGE SCAN 주문상세_PK (cr=3063 pr=0 pw=0 time=66267 us) ...... 2
실행계획을 보면 실제 1번과 2번 오퍼레이션 단계에 아래와 같은 Predicate 정보가 나타나는 것을 볼 수 있다.
access(O.주문일자=TO_CHAR(SYSDATE@!,'YYYYMMDD') AND O.고객번호=C.고객번호)
access(D.주문일자=TO_CHAR(SYSDATE@!,'YYYYMMDD') AND D.고객번호=O.고객번호
AND D.주문번호=O.주문번호)
이처럼 상수 및 변수에 대한 조건절은 조인문을 타고 다른 쪽 테이블로 전이된다.
하지만 조인문 자체는 전이되지 않는데 아래 경우가 그것을 말해준다.
select /*+ ordered use_nl(o) use_nl(d) index(0) index(d) */
c.고객명,o.주문일자,o.주문번호,o.배송지,d.상품번호,d.상품가격,d.주문수량
from 고객 c, 주문 o, 주문상세 d
where d.고객번호 = c.고객번호 -> 여기
and d.고객번호 = o.고객번호 -> 여기
and d.주문일자 = o.주문일자
and d.주문번호 = o.주문번호
and d.주문일자 = to_char(sysdate, 'yyyymmdd')
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 1001 0.188 0.220 0 25052 0 10000
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 1003 0.188 0.220 0 25052 0 10000
Rows Row Source Operation
------ ----------------------------------------------------
10000 TABLE ACCESS BY INDEX ROWID 주문상세 (cr=25052 pr=0 pw=0 time=210132 us)
20001 NESTED LOOPS (cr=15052 pr=0 pw=0 time=260362 us)
1000 NESTED LOOPS (cr=2422 pw=0 pw=0 time=150064 us)
10 TABLE ACCESS FULL 고객 (cr=16 pr=0 pw=0 time=74 us)
10000 TABLE ACCESS BY INDEX ROWID 주문 (cr=2406 pr=0 pw=0 time=90156 us)
10000 INDEX RANGE SCAN 주문_PK (cr=1046 pr=0 pw=0 time=30093 us) ...... 1
10000 INDEX RANGE SCAN 주문상세_PK (cr=12630 pr=0 pw=0 time=167571 us) ...... 2
고객번호에 대한 두 조인 조건식을 잘 따져보면, 고객과 주문상세가 연결됐고, 주문과 주문상세도 연결됐다.
그런데 NL조인 순서는 ordered 힌트에 따라 고객->주문->주문상세 순으로 결정된다. 따라서 고객과 주문을
먼저 조인하는 단계에서는 고객번호를 연결 조건으로 사용하지 못하는 문제가 생긴다.
아래 Predicate 정보 1에서 고객번호에 대한 조인 조건식은 없고 주문상세 테이블 조건으로부터 전이된
주문일자 조건만 있는 것이 확인된다.
1 access(O.주문일자=TO_CHAR(SYSDATE@!,'YYYYMMDD'))
2 access(D.주문일자=TO_CHAR(SYSDATE@!,'YYYYMMDD') AND
D.고객번호=O.고객번호 AND D.주문번호=O.주문번호)
filter(D.고객번호=C.고객번호)
이 때문에 주문 테이블과 조인하고 나서 9000개나 더 많은 레코드가 출력되었고, 그만큼 주문상세_PK 인덱스를
여러번 탐색하면서 10,000개 가량의 블록I/O도 추가로 발생했다.
만약 아래처럼 사용자가 기술한 1,2번 조인문을 통해 내부적으로 3번 조인문이 생성되었더라면 위와 같은 문제는
발생하지 않았을 것이다.
where d.고객번호 = c.고객번호 ... 1
and d.고객번호 = o.고객번호 ... 2
and o.고객번호 = c.고객번호 ... 3
"구루비 주주클럽 스터디모임" 에서 2016년에 "오라클 성능 고도화 원리와 해법 II " 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/3361
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.