近期監控數據庫，發現以下語句跑得很慢，原來運行計劃走了導致笛卡爾積，來看以下語句：
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 '%租機%'
8 OR "A2"."ALLO_DISC_TYPE_3" LIKE '%零預存%';
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 '%零預存%')
7 - access("A1"."SERV_ID"="A2"."SERV_ID" AND "A1"."STAT_DATE"="A2"."STAT_DATE")
8 - filter("A2"."ALLO_DISC_TYPE_3" LIKE '%租機%' AND LNNVL("A2"."ALLO_DISC_TYPE_3"
LIKE '%零預存%'))
--表大小的情況：
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，這里肯定是開發者不小心，寫錯了語句，導致跑不出來。
AND "A2"."ALLO_DISC_TYPE_3" LIKE '%租機%' ?OR "A2"."ALLO_DISC_TYPE_3" LIKE '%零預存%';改成
AND （"A2"."ALLO_DISC_TYPE_3" LIKE '%租機%' ?OR "A2"."ALLO_DISC_TYPE_3" LIKE '%零預存%'）;
改動語句后，問題解決。
改寫后：
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 '%租機%' OR "A2"."ALLO_DISC_TYPE_3" LIKE '%零預存%');
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 '%租機%' OR "A2"."ALLO_DISC_TYPE_3" LIKE
'%零預存%')
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
