经过对比执行计划,是执行计划变成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)