優化器的侷限
問題:
投產支援時，發現一個生成報表的sql語句執行了2個小時,還是沒有任何輸出。而同樣的語句，在以前只需要10分鐘就可以出來了。
1,問題SQL分析：
SELECT ...
FROM customer cus,
(SELECT DISTINCT cust_id
FROM accounts act
WHERE act.ibknum = '40303'
AND act.TYPE NOT IN ('00', '01', '02', '03')) actt,
bancs_cif_mig bcm,
accounts act,
bocorg org
WHERE cus.cust_id = actt.cust_id
AND cus.cust_id = bcm.cust_id
AND bcm.cp_flag = 'P'
AND bcm.ncif > 1
AND cus.cust_id = act.cust_id
AND cus.bank_id_m = org.bank_id
ORDER BY org.bank_id, cus.cust_id, cus.cif, act.actseq;
等待事件分析：
分析系統當前會話，發現有並行會話；經檢查，發現指令碼中有如下語句：
alter session force parallel query parallel 8;
檢查並行會話當前的等待事件，有幾個會話等待cache buffers chains latch。推測問題的是由於不良的執行計劃，在並行執行時導致熱塊。
檢視執行計劃：
Execution Plan
----------------------------------------------------------
Plan hash value: 4024296431
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 241K| | 821K (1)| 02:44:17 |
| 1 | SORT ORDER BY | | 241K| 85M| 821K (1)| 02:44:17 |
|* 2 | HASH JOIN | | 241K| | 812K (1)| 02:42:26 |
| 3 | TABLE ACCESS FULL | BOCORG | 12212 | | 84 (0)| 00:00:02 |
|* 4 | HASH JOIN | | 241K| 16M| 812K (1)| 02:42:25 |
|* 5 | HASH JOIN | | 222K| 72M| 524K (1)| 01:44:58 |
|* 6 | TABLE ACCESS FULL | BANCS_CIF_MIG | 3326K| | 6520 (4)| 00:01:19 |
|* 7 | HASH JOIN | | 2505K| 43M| 506K (1)| 01:41:17 |
| 8 | VIEW | | 2505K| | 182K (2)| 00:36:32 |
| 9 | HASH UNIQUE | | 2505K| 134M| 182K (2)| 00:36:32 |
|* 10 | TABLE ACCESS FULL| ACCOUNTS | 2512K| | 169K (2)| 00:33:51 |
| 11 | TABLE ACCESS FULL | CUSTOMER | 38M| | 205K (1)| 00:41:11 |
| 12 | TABLE ACCESS FULL | ACCOUNTS | 40M| | 168K (1)| 00:33:46 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CUS"."BANK_ID_M"="ORG"."BANK_ID")
4 - access("CUS"."CUST_ID"="ACT"."CUST_ID")
5 - access("CUS"."CUST_ID"="BCM"."CUST_ID")
6 - filter("BCM"."CP_FLAG"='P' AND "BCM"."NCIF">1)
7 - access("CUS"."CUST_ID"="ACTT"."CUST_ID")
10 - filter("ACT"."IBKNUM"='40303' AND "ACT"."TYPE"<>'00' AND "ACT"."TYPE"<>'01' AND
"ACT"."TYPE"<>'02' AND "ACT"."TYPE"<>'03')
查詢計劃中出現5次全表掃描（其中3個表資料約3000萬）,4次HASH JOIN，成本高達821K。
執行計劃比較:
查詢優化統計資料,發現表上的統計資訊比較準確。對比測試環境（資料量約為當前環境1/2，該SQL執行正常）同樣語句的執行計劃：
SQL> set autotrace trace exp
SQL>
Execution Plan
----------------------------------------------------------
Plan hash value: 3772091147
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 88072 | | 747K (1)| 02:29:35 |
| 1 | SORT ORDER BY | | 88072 | 15M| 747K (1)| 02:29:35 |
| 2 | TABLE ACCESS BY INDEX ROWID| ACCOUNTS | 1 | | 3 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 88072 | | 744K (1)| 02:28:56 |
|* 4 | HASH JOIN | | 81468 | | 500K (1)| 01:40:02 |
| 5 | TABLE ACCESS FULL | BOCORG | 12238 | | 63 (0)| 00:00:01 |
|* 6 | HASH JOIN | | 81468 | 36M| 500K (1)| 01:40:01 |
| 7 | VIEW | | 2121K| | 176K (2)| 00:35:19 |
| 8 | HASH UNIQUE | | 2121K| 57M| 176K (2)| 00:35:19 |
|* 9 | TABLE ACCESS FULL | ACCOUNTS | 2126K| | 165K (2)| 00:33:02 |
|* 10 | HASH JOIN | | 1298K| 28M| 317K (1)| 01:03:29 |
|* 11 | TABLE ACCESS FULL | BANCS_CIF_MIG | 1298K| | 2835 (4)| 00:00:35 |
| 12 | TABLE ACCESS FULL | CUSTOMER | 39M| | 205K (1)| 00:41:03 |
|* 13 | INDEX RANGE SCAN | IDX_ACCOUNTS_CUST | 1 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("CUS"."BANK_ID_M"="ORG"."BANK_ID")
6 - access("CUS"."CUST_ID"="ACTT"."CUST_ID")
9 - filter("ACT"."IBKNUM"='40303' AND "ACT"."TYPE"<>'00' AND "ACT"."TYPE"<>'01' AND
"ACT"."TYPE"<>'02' AND "ACT"."TYPE"<>'03')
10 - access("CUS"."CUST_ID"="BCM"."CUST_ID")
11 - filter("BCM"."CP_FLAG"='P' AND "BCM"."NCIF">1)
13 - access("CUS"."CUST_ID"="ACT"."CUST_ID")
通過比較，會發現仍然涉及4次全表掃描（其中2個表資料約3000萬），3次HASH JOIN，1次NESTED LOOP，成本高達747K。與前面成本821K比較，可以認為：由於資料量變化，優化器通過比較所有可用的訪問路徑，認為成本821K為最低，所以選擇了5次全表掃描、4次HASH JOIN的執行計劃。
2,解決方案：
通過分析應用邏輯，會發現查詢語句中過濾性最強的謂詞為：BCM.NCIF>1。如果能優先執行該表的查詢，所需邏輯讀最少。
建立索引:
create index idx_bancs_cif_mig_ncif on bancs_cif_mig(ncif) parallel 8;
alter index idx_bancs_cif_mig_ncif noparallel;
begin
dbms_stats.gather_table_stats(user,'bancs_cif_mig');
end;
/
----------------------------
Execution Plan
----------------------------------------------------------
Plan hash value: 86001731
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2018 | 146K (1)| 00:29:18 |
| 1 | SORT ORDER BY | | 2018 | 146K (1)| 00:29:18 |
| 2 | VIEW | | 2018 | 146K (1)| 00:29:18 |
| 3 | HASH UNIQUE | | 2018 | 146K (1)| 00:29:18 |
|* 4 | HASH JOIN | | 2018 | 146K (1)| 00:29:18 |
| 5 | TABLE ACCESS BY INDEX ROWID | ACCOUNTS | 1 | 2 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 2018 | 146K (1)| 00:29:17 |
| 7 | NESTED LOOPS | | 1864 | 142K (1)| 00:28:33 |
| 8 | NESTED LOOPS | | 28616 | 57628 (1)| 00:11:32 |
|* 9 | TABLE ACCESS BY INDEX ROWID| BANCS_CIF_MIG | 28616 | 355 (1)| 00:00:05 |
|* 10 | INDEX RANGE SCAN | IDX_BANCS_CIF_MIG_NCIF | 29268 | 61 (2)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID| CUSTOMER | 1 | 2 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | PK_CUSTOMER | 1 | 1 (0)| 00:00:01 |
|* 13 | TABLE ACCESS BY INDEX ROWID | ACCOUNTS | 1 | 3 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | IDX_ACCOUNTS_CUST | 1 | 2 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | IDX_ACCOUNTS_CUST | 1 | 2 (0)| 00:00:01 |
| 16 | TABLE ACCESS FULL | BOCORG | 12212 | 84 (0)| 00:00:02 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("CUS"."BANK_ID_M"="ORG"."BANK_ID")
9 - filter("BCM"."CP_FLAG"='P')
10 - access("BCM"."NCIF">1)
12 - access("CUS"."CUST_ID"="BCM"."CUST_ID")
13 - filter("ACT"."IBKNUM"='40303' AND "ACT"."TYPE"<>'00' AND "ACT"."TYPE"<>'01' AND
"ACT"."TYPE"<>'02' AND "ACT"."TYPE"<>'03')
14 - access("CUS"."CUST_ID"="CUST_ID")
15 - access("CUS"."CUST_ID"="ACT"."CUST_ID")
執行計劃中使用了新建的索引，進行BCM.NCIF>1過濾後，僅有29268行滿足要求，後續通過NESTED LOOP依次訪問其他表，僅有一次對小表的全表掃描。執行查詢，8分鐘後執行完畢。
3,總結：
在本案例中，並不是優化器失效。就目前階段，優化器能做的只是從所有可用的訪問路徑中選擇最優方案；只有在DBA依據應用特點，提供了有效的訪問路徑（索引、物化檢視、約束等），優化器才能提供最優的、滿足需求的執行計劃。
我們不能期望優化器，通過實時取樣分析資料分佈特徵，建立“臨時”索引，提供新的訪問路徑，這就是優化器的侷限，這也是DBA的價值所在。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/18922393/viewspace-697268/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- NUMA的原理與侷限
- java泛型的侷限探究Java泛型
- Oracle Flashback query查詢的侷限Oracle
- 以技術創新為引擎，打破國產化替代的侷限
- Cypress 架構及其決定的侷限架構
- javascript中promise有什麼侷限JavaScriptPromise
- 華納雲：TS伺服器是什麼樣的，好處和侷限有哪些伺服器
- 這些優秀的跨國集團告訴你，如何突破遠端辦公的侷限
- C++ 工程實踐(7)：iostream 的用途與侷限C++iOS
- Angular 17+ 高階教程 – Angular 的侷限 の Query ElementsAngular
- 蜜罐技術：消除防火牆侷限和脆弱(轉)防火牆
- 不侷限技術領域，結交其他行業的精英行業
- 驚豔，Dubbo域名已改，也不再侷限於Java！！Java
- 資料結構之堆 → 不要侷限於堆排序資料結構排序
- 「前端那些事兒」② 極限效能優化前端優化
- 深度學習侷限何在？圖網路的出現並非偶然深度學習
- 由ASO談Apple Search Ads（ASA）人工廣告投放的侷限APP
- 百度MUX:線上問卷調研的侷限與對策UX
- 資料的侷限：大資料分析不能告訴你什麼大資料
- 神奇的 SQL ，高階處理之 Window Functions → 打破我們的侷限！SQLFunction
- Github Copilot 的優點和侷限性 - hrithwikGithub
- oracle 的優化器Oracle優化
- Oracle的優化器Oracle優化
- Hybris Accelerator 的一些侷限介紹和 Spartacus 專案的誕生
- 技術瓶頸、內容侷限，虛擬人還未“成年”
- iPad銷量下滑探因：定位受侷限 硬體很耐用iPad
- 優化器優化
- 12 月機器學習新書：《可解釋機器學習方法的侷限》，免費下載！機器學習新書
- LeCun：深度學習在訊號理解中的強大和侷限（視訊+PPT）LeCun深度學習
- PG 優化器的精彩優化
- 使用智慧最佳化器提高Oracle的效能極限Oracle
- 萬能碼：突破二維碼侷限（安全掃碼專業委員會）
- 【深度學習】深度學習：技術原理、迭代路徑與侷限深度學習
- 索尼公佈電池召回計劃 不侷限於筆記本筆記
- 服裝市場如何利用網際網路來改變當下的侷限？
- 如何基於 OpenKruise 打破原生 Kubernetes 中的容器執行時操作侷限？UI
- 優化器提示優化
- mysql count函式與分頁功能極限優化MySql函式優化