SQL #1
SELECT *
FROM (SELECT T0.*,
FLOOR((ROWNUM – 1) / :1 + 1) PAGE
FROM (
SELECT
a.entry_indexno,
a.eventid,
a.userid,
a.name, a.reg_date,
b.phone,
b.address1, b.address2,
a.value1,
a.value2,
a.value3,
a.value4,
a.value5,
a.value6,
a.value7,
a.value8,
a.value9,
a.winner_status,
a.event_goods_indexno,
e.goods_name
,
(SELECT
count(*)
FROM
DDH_EVENT_ENTRY
WHERE userid = a.userid
AND
winner_status = ‘Y’
) as
old_win_count
FROM DDH_EVENT_ENTRY a
LEFT
JOIN DDH_USERMASTER b
ON
a.userid = b.userid
LEFT
JOIN DDH_EVENT_MASTER c
ON
a.eventid = c.eventid
LEFT
JOIN DDH_EVENT_GOODS d
ON
a.event_goods_indexno = d.event_goods_indexno
LEFT
JOIN DDH_GOODS_MASTER e
ON
d.goods_indexno = e.goods_indexno
WHERE a.eventid = :2
ORDER BY a.reg_date ASC
)
T0
)
WHERE PAGE = :3
바인드 변수 값 없음.
수행 시간 / 일 량 결과
elapsed disk
query current
rows
———- ———- ———- ———- ———-
3.157774 0 241757
0 4078
실제 수행 플랜 및
건수
Row
Row_Source_Operation
———
————————————————————————————————————————
4078
SORT AGGREGATE (cr=90324 pr=0
pw=0 time=468228)
0
TABLE ACCESS BY INDEX ROWID DDH_EVENT_ENTRY (cr=90324 pr=0 pw=0
time=459254)
90121
INDEX RANGE SCAN DDH_EVENT_ENTRY_I1 (cr=8496 pr=0 pw=0 time=65121)
10
VIEW (cr=151433 pr=0 pw=0
time=2689546)
23364
COUNT (cr=151433 pr=0 pw=0
time=2647019)
23364
VIEW (cr=151433 pr=0 pw=0
time=2623644)
23364
SORT ORDER BY (cr=151433 pr=0
pw=0 time=2623636)
23364
HASH JOIN RIGHT OUTER (cr=61109
pr=0 pw=0 time=2071387)
87
TABLE ACCESS FULL DDH_GOODS_MASTER (cr=7 pr=0 pw=0 time=40) <– 풀 스캔
23364
NESTED LOOPS OUTER (cr=61102 pr=0
pw=0 time=2047065)
23364
HASH JOIN RIGHT OUTER (cr=61102
pr=0 pw=0 time=2000328)
1 INDEX UNIQUE SCAN DDH_EVENT_MASTER_PK
(cr=1 pr=0 pw=0 time=13)
23364 HASH JOIN OUTER (cr=61101 pr=0 pw=0 time=1941106)
23364 TABLE
ACCESS FULL DDH_EVENT_ENTRY (cr=2770 pr=0 pw=0 time=23393) <– 풀 스캔
1461816 TABLE ACCESS FULL DDH_USERMASTER#
(cr=58331 pr=0 pw=0 time=1723)<– 풀 스캔
0
TABLE ACCESS BY INDEX ROWID DDH_EVENT_GOODS (cr=0 pr=0 pw=0 time=43401)
0 INDEX UNIQUE SCAN DDH_EVENT_GOODS_PK
(cr=0 pr=0 pw=0 time=18149)
2. 문제점 및 원인
– 테이블을 풀 스캔 리드 되며 시간 소요가 증가
및 피지컬 I/O 수행이 증가 됨
– 앞으로의 Tuning Guide에서 제시하는 개선안을
실제 운영중인 시스템에 적용 시 반드시 원문SQL과 관련 있는 담당자나 개발자 분이 개선전의 실행결과값과
개선방안의 실행결과값을 다방면으로 면밀히 조사하여 결과값의 차이가 없는지 확인한 뒤 적용하여야 하며 개발환경이나 업무 특이사항으로 문제가 될 수
있는지 등에 대한 종합적인 판단을 하여 운영시스템 적용 시 문제가 발생되지 않도록 주의하여야 한다.
3. 개선방안
–
풀 스캔을
막고 인덱스 스캔으로 하도록 수정합니다.
개선
안.
SQL HINT 조정으로 JOIN 방식 및 인덱스 수행 되도록 변경함.
SELECT *
FROM (SELECT T0.*,
FLOOR((ROWNUM – 1) / :1 + 1) PAGE
FROM (
SELECT /*+ leading(a b c d e) use_nl(a b c d e)
index(b DDH_USERMASTER_I1) index(e DDH_GOODS_MASTER_PK) */ a.entry_indexno,
a.eventid,
a.userid,
a.name, a.reg_date,
b.phone,
b.address1, b.address2,
a.value1,
a.value2,
a.value3,
a.value4,
a.value5,
a.value6,
a.value7,
a.value8,
a.value9,
a.winner_status,
a.event_goods_indexno,
e.goods_name
,
(SELECT
count(*)
FROM
DDH_EVENT_ENTRY
WHERE userid = a.userid
AND
winner_status = ‘Y’
) as
old_win_count
FROM DDH_EVENT_ENTRY a
LEFT
JOIN DDH_USERMASTER b
ON
a.userid = b.userid
LEFT
JOIN DDH_EVENT_MASTER c
ON
a.eventid = c.eventid
LEFT
JOIN DDH_EVENT_GOODS d
ON
a.event_goods_indexno = d.event_goods_indexno
LEFT
JOIN DDH_GOODS_MASTER e
ON
d.goods_indexno = e.goods_indexno
WHERE a.eventid = :2
ORDER BY a.reg_date ASC
)
T0
)
WHERE
PAGE = :3
수행 시간 / 일 량 결과
elapsed disk query
current
rows
———-
———- ———- ———- ———-
10.847647
0
1949657 0 18159
실제 수행 플랜 및
건수
Row Row_Source_Operation
——— ————————————————————————————————————————
18159 SORT AGGREGATE (cr=938445 pr=0 pw=0 time=5027074)
0
TABLE ACCESS BY INDEX ROWID DDH_EVENT_ENTRY (cr=938445 pr=0 pw=0 time=4986103)
989129 INDEX RANGE SCAN
DDH_EVENT_ENTRY_I1 (cr=39979 pr=0 pw=0 time=488034)
10
VIEW (cr=1011212 pr=0 pw=0
time=5820573)
23364 COUNT (cr=1011212 pr=0 pw=0 time=5771581)
23364 VIEW (cr=1011212 pr=0 pw=0 time=5748205)
23364 SORT ORDER BY (cr=1011212 pr=0 pw=0 time=5748197)
23364 NESTED LOOPS OUTER (cr=72767 pr=0 pw=0 time=607526)
23364 NESTED LOOPS
OUTER (cr=72767 pr=0 pw=0 time=537432)
23364 NESTED LOOPS
OUTER (cr=72767 pr=0 pw=0 time=467339)
23364 NESTED LOOPS
OUTER (cr=72765 pr=0 pw=0 time=373878)
23364 TABLE ACCESS FULL
DDH_EVENT_ENTRY (cr=2770 pr=0 pw=0 time=46768)
23265 TABLE ACCESS BY
INDEX ROWID DDH_USERMASTER# (cr=69995 pr=0 pw=0 time=296150)
23265 INDEX UNIQUE SCAN
DDH_USERMASTER_I1 (cr=46730 pr=0 pw=0 time=173375)
23364 INDEX UNIQUE SCAN
DDH_EVENT_MASTER_PK (cr=2 pr=0 pw=0 time=68697)
0
TABLE ACCESS BY INDEX ROWID DDH_EVENT_GOODS (cr=0 pr=0 pw=0 time=47826)
0 INDEX UNIQUE SCAN DDH_EVENT_GOODS_PK
(cr=0 pr=0 pw=0 time=18914)
0
TABLE ACCESS BY INDEX ROWID DDH_GOODS_MASTER (cr=0 pr=0 pw=0 time=44078)
0
INDEX UNIQUE SCAN DDH_GOODS_MASTER_PK (cr=0 pr=0 pw=0 time=18043)
<- 풀 스캔 테이블을 제거 하였으나
select 절에 count 문으로 인해 DDH_EVENT_ENTRY
테이블을 수행 건수 만큼 다시 서치하므로 이를 제거