近期監(jiān)控數(shù)據(jù)庫,發(fā)現(xiàn)以下語句跑得很慢,原來運行計劃走了導(dǎo)致笛卡爾積,來看以下語句:
SQL> explain plan for 2 SELECT COUNT(*) 3 FROM "GD_FS"."TZZ_SJ_DEV_DISC_79073" "A2", 4 "GD_FS"."TZZ_SJ_DEVELOP_MONTH" "A1" 5 WHERE "A1"."SERV_ID" = "A2"."SERV_ID" 6 AND "A1"."STAT_DATE" = "A2"."STAT_DATE" 7 AND "A2"."ALLO_DISC_TYPE_3" LIKE '%租機(jī)%' 8 OR "A2"."ALLO_DISC_TYPE_3" LIKE '%零預(yù)存%'; Explained. SQL> @getplan 'general,outline,starts' Enter value for plan type: PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------------------------- Plan hash value: 1410945947 ------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 42 | 271M (2)|999:59:59 | | 1 | SORT AGGREGATE | | 1 | 42 | | | | 2 | CONCATENATION | | | | | | | 3 | MERGE JOIN CARTESIAN| | 82G| 3239G| 271M (2)|999:59:59 | |* 4 | TABLE ACCESS FULL | TZZ_SJ_DEV_DISC_79073 | 22606 | 640K| 743 (2)| 00:00:11 | | 5 | BUFFER SORT | | 3663K| 45M| 271M (2)|999:59:59 | | 6 | TABLE ACCESS FULL | TZZ_SJ_DEVELOP_MONTH | 3663K| 45M| 11994 (2)| 00:02:48 | |* 7 | HASH JOIN | | 1 | 42 | 12762 (2)| 00:02:59 | |* 8 | TABLE ACCESS FULL | TZZ_SJ_DEV_DISC_79073 | 21476 | 608K| 743 (2)| 00:00:11 | | 9 | TABLE ACCESS FULL | TZZ_SJ_DEVELOP_MONTH | 3663K| 45M| 11996 (2)| 00:02:48 | ------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter("A2"."ALLO_DISC_TYPE_3" LIKE '%零預(yù)存%') 7 - access("A1"."SERV_ID"="A2"."SERV_ID" AND "A1"."STAT_DATE"="A2"."STAT_DATE") 8 - filter("A2"."ALLO_DISC_TYPE_3" LIKE '%租機(jī)%' AND LNNVL("A2"."ALLO_DISC_TYPE_3" LIKE '%零預(yù)存%'))
--表大小的情況:
OWNER SEGMENT_NAME SEGMENT_TYPE Size(Mb) -------------------- ------------------------------ -------------------- ---------- GD_FS TZZ_SJ_DEV_DISC_79073 TABLE 43 GD_FS TZZ_SJ_DEVELOP_MONTH TABLE 577.375
兩個表并不大,一個43M,一個577M,這里肯定是開發(fā)者不小心,寫錯了語句,導(dǎo)致跑不出來。
AND "A2"."ALLO_DISC_TYPE_3" LIKE '%租機(jī)%' ?OR "A2"."ALLO_DISC_TYPE_3" LIKE '%零預(yù)存%';改成
AND ("A2"."ALLO_DISC_TYPE_3" LIKE '%租機(jī)%' ?OR "A2"."ALLO_DISC_TYPE_3" LIKE '%零預(yù)存%');
改動語句后,問題解決。
改寫后:
SQL> explain plan for 2 SELECT COUNT(*) 3 FROM "GD_FS"."TZZ_SJ_DEV_DISC_79073" "A2", 4 "GD_FS"."TZZ_SJ_DEVELOP_MONTH" "A1" 5 WHERE "A1"."SERV_ID" = "A2"."SERV_ID" 6 AND "A1"."STAT_DATE" = "A2"."STAT_DATE" 7 AND ("A2"."ALLO_DISC_TYPE_3" LIKE '%租機(jī)%' OR "A2"."ALLO_DISC_TYPE_3" LIKE '%零預(yù)存%'); Explained. SQL> @getplan 'general,outline,starts' Enter value for plan type: PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------- Plan hash value: 3104770780 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 42 | 12765 (2)| 00:02:59 | | 1 | SORT AGGREGATE | | 1 | 42 | | | |* 2 | HASH JOIN | | 44081 | 1808K| 12765 (2)| 00:02:59 | |* 3 | TABLE ACCESS FULL| TZZ_SJ_DEV_DISC_79073 | 44081 | 1248K| 745 (3)| 00:00:11 | | 4 | TABLE ACCESS FULL| TZZ_SJ_DEVELOP_MONTH | 3663K| 45M| 11996 (2)| 00:02:48 | --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("A1"."SERV_ID"="A2"."SERV_ID" AND "A1"."STAT_DATE"="A2"."STAT_DATE") 3 - filter("A2"."ALLO_DISC_TYPE_3" LIKE '%租機(jī)%' OR "A2"."ALLO_DISC_TYPE_3" LIKE '%零預(yù)存%')
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號聯(lián)系: 360901061
您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機(jī)微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
