調優: 一個SQL現在30分鐘,而原來只要2秒

經過對比執行計劃,是執行計劃變成full table scan 和錯誤的表連線了,改用hint提示走索引和用正確的表連線,解決問題.但為什麼在prod上是正確的計劃,而在測試環境計劃變了?

[@more@]

下面在uat上要跑30分鐘, 但在prd上僅僅10秒:

SELECT

cd.case_id,

i.item_identifier,

mt.ext_trade_id,

mt.mirror_id,

mt.book_code,

mt.counterparty,

i.item_status_code,

i.account_code,

i.account_type_code,

cd.case_cause_code,

cd.case_status_code,

cd.case_priority_code,

cd.date_opened,

cd.date_closed,

cd.sec_user_id,

nvl(TRUNC(date_closed),TRUNC(SYSDATE)) - TRUNC(cd.date_opened) days_opened,

cd.amended_user_id,

cd.amended_date_time,

cd.entry_date_time,

summit_fo_version,

summit_bo_version,

item_version_num,

mt.item_id,

mt.desk_code desk_of_book,

b_cpty.desk_code desk_of_cpty,

r.region_desc region_of_book,

r_cpty.region_desc region_of_cpty,

b.trade_capture_owner owner_of_book,

b_cpty.trade_capture_owner owner_of_cpty,

--decode(cd.exclude_reset_date, null, 'N', 'Y') prev_exclude

nvl(cd.prev_exclude, 'N') prev_exclude

FROM case_detail cd,

case_item ci,

item i,

mis_trade mt,

account a,

book b,

book b_cpty,

region r,

region r_cpty

WHERE cd.case_id = ci.case_id

AND ci.item_id = i.item_id

AND i.item_id = mt.item_id

AND i.account_code = a.account_code

AND i.account_type_code = a.account_type_code

AND cd.case_id = 661711 AND

mt.book_code = b.book_code AND

mt.counterparty = b_cpty.book_code AND

b.trade_capture_region_id = r.region_id (+) AND

b_cpty.trade_capture_region_id = r_cpty.region_id (+)

AND i.item_id in (

SELECT /*+ use_nl(i il) */ ci.item_id

FROM case_item ci,

item_link il,

item i

WHERE i.item_id = ci.item_id

AND i.account_type_code = 'TRADE'

AND il.item_id (+) = ci.item_id

AND item_version_num =

(SELECT /*+ use_nl(item case_item) */ max(item_version_num)

FROM item, item_link, case_item

WHERE item.item_id = case_item.item_id

AND item_link.item_id = case_item.item_id

AND item_identifier = i.item_identifier

AND item.account_type_code ='TRADE'

AND source_system_code = i.source_system_code

AND case_id = ci.case_id)

AND ci.case_id = 661711

UNION ALL

SELECT /*+ index(i ix_item_1) use_nl(i il) */ ci.item_id

FROM case_item ci,

item_link il,

item i

WHERE i.item_id = ci.item_id

AND i.account_type_code = 'TRADE'

AND il.item_id (+) = ci.item_id

AND il.link_id IS NULL

AND ci.case_id = 661711

AND item_version_num =

(SELECT max(item_version_num)

FROM item, item_link, case_item

WHERE item.item_id = case_item.item_id

AND item_link.item_id (+) = case_item.item_id

AND item_identifier = i.item_identifier

AND item.account_type_code ='TRADE'

AND source_system_code = i.source_system_code

AND case_id = ci.case_id))

ORDER BY

cd.case_id,

i.item_identifier;

關鍵是後面這個語句:

SELECT ci.item_id

FROM case_item ci,

item_link il,

item i

WHERE i.item_id = ci.item_id

AND i.account_type_code = 'TRADE'

AND il.item_id (+) = ci.item_id

AND item_version_num =

(SELECT max(item_version_num)

FROM item, item_link, case_item

WHERE item.item_id = case_item.item_id

AND item_link.item_id = case_item.item_id

AND item_identifier = i.item_identifier

AND item.account_type_code ='TRADE'

AND source_system_code = i.source_system_code

AND case_id = ci.case_id)

AND ci.case_id = 661711

UNION ALL

SELECT ci.item_id

FROM case_item ci,

item_link il,

item i

WHERE i.item_id = ci.item_id

AND i.account_type_code = 'TRADE'

AND il.item_id (+) = ci.item_id

AND il.link_id IS NULL

AND ci.case_id = 661711

AND item_version_num =

(SELECT max(item_version_num)

FROM item, item_link, case_item

WHERE item.item_id = case_item.item_id

AND item_link.item_id (+) = case_item.item_id

AND item_identifier = i.item_identifier

AND item.account_type_code ='TRADE'

AND source_system_code = i.source_system_code

AND case_id = ci.case_id)

UNION ALL前面的語句在prd計劃是下面這樣:

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3421 Card=1 Bytes=125)

1 0 FILTER

2 1 SORT (GROUP BY) (Cost=3421 Card=1 Bytes=125)

3 2 NESTED LOOPS (OUTER) (Cost=3416 Card=1 Bytes=125)

4 3 NESTED LOOPS (Cost=3414 Card=1 Bytes=112)

5 4 NESTED LOOPS (Cost=3412 Card=1 Bytes=106)

6 5 HASH JOIN (Cost=1140 Card=1136 Bytes=71568)

7 6 INDEX (RANGE SCAN) OF 'IX_CASE_ITEM_1' (UNIQUE) (Cost=5 Card=1127 Bytes=19159)

8 6 NESTED LOOPS (Cost=1134 Card=1127 Bytes=51842)

9 8 INDEX (RANGE SCAN) OF 'IX_CASE_ITEM_1' (UNIQUE) (Cost=5 Card=1127 Bytes=11270)

10 8 TABLE ACCESS (BY INDEX ROWID) OF 'ITEM' (Cost=2 Card=1 Bytes=36)

11 10 INDEX (UNIQUE SCAN) OF 'PK_ITEM' (UNIQUE)(Cost=1 Card=1)

12 5 TABLE ACCESS (BY INDEX ROWID) OF 'ITEM' (Cost=2Card=1 Bytes=43)

13 12 INDEX (UNIQUE SCAN) OF 'PK_ITEM' (UNIQUE) (Cost=1 Card=1)

14 4 INDEX (RANGE SCAN) OF 'IX_ITEM_LINK_1' (UNIQUE) (Cost=2 Card=31 Bytes=186)

15 3 INDEX (RANGE SCAN) OF 'IX_ITEM_LINK_1' (UNIQUE) (Cost=2 Card=31 Bytes=403)

但在uat上是這樣:

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=87521879491 Card=39 Bytes=4875)

1 0 FILTER

2 1 SORT (GROUP BY) (Cost=87521879491 Card=39 Bytes=4875)

3 2 NESTED LOOPS (Cost=87521879451 Card=7708 Bytes=963500)

4 3 NESTED LOOPS (OUTER) (Cost=72686094715 Card=7417892368 Bytes=608267174176)

5 4 NESTED LOOPS (Cost=72173522563 Card=256286076 Bytes=17683739244)

6 5 HASH JOIN (Cost=72172833547 Card=86127 Bytes=4478604)

7 6 INDEX (RANGE SCAN) OF 'IX_CASE_ITEM_1' (UNIQUE) (Cost=9 Card=2976 Bytes=29760)

8 6 NESTED LOOPS (Cost=65273108311 Card=333788044025241 Bytes=14019097849060100)

9 8 TABLE ACCESS (FULL) OF 'ITEM' (Cost=41265 Card=5811866 Bytes=209227176)

10 8 INDEX (FAST FULL SCAN) OF 'PK_ITEM_LINK' (UNIQUE) (Cost=11231 Card=57432165 Bytes=344592990)

11 5 INDEX (RANGE SCAN) OF 'IX_CASE_ITEM_1' (UNIQUE)(Cost=8 Card=2976 Bytes=50592)

12 4 INDEX (RANGE SCAN) OF 'IX_ITEM_LINK_1' (UNIQUE) (Cost=2 Card=29 Bytes=377)

13 3 TABLE ACCESS (BY INDEX ROWID) OF 'ITEM' (Cost=2 Card=1 Bytes=43)

14 13 INDEX (UNIQUE SCAN) OF 'PK_ITEM' (UNIQUE) (Cost=1 Card=1)

經過加提示改變計劃:

SELECT /*+ use_nl(i il) */ ci.item_id

FROM case_item ci,

item_link il,

item i

WHERE i.item_id = ci.item_id

AND i.account_type_code = 'TRADE'

AND il.item_id (+) = ci.item_id

AND item_version_num =

(SELECT /*+ use_nl(item case_item) */ max(item_version_num)

FROM item, item_link, case_item

WHERE item.item_id = case_item.item_id

AND item_link.item_id = case_item.item_id

AND item_identifier = i.item_identifier

AND item.account_type_code ='TRADE'

AND source_system_code = i.source_system_code

AND case_id = ci.case_id)

AND ci.case_id = 661711

UNION ALL

SELECT /*+ index(i ix_item_1) use_nl(i il) */ ci.item_id

FROM case_item ci,

item_link il,

item i

WHERE i.item_id = ci.item_id

AND i.account_type_code = 'TRADE'

AND il.item_id (+) = ci.item_id

AND il.link_id IS NULL

AND ci.case_id = 661711

AND item_version_num =

(SELECT max(item_version_num)

FROM item, item_link, case_item

WHERE item.item_id = case_item.item_id

AND item_link.item_id (+) = case_item.item_id

AND item_identifier = i.item_identifier

AND item.account_type_code ='TRADE'

AND source_system_code = i.source_system_code

AND case_id = ci.case_id)