案例6　“抽絲剝繭”找出問題所在

1.案例說明

這個案例本身不是為了說明某種技術，而是展現DBA在分析處理問題時的一種處理方式。其采用的方法往往是根據自己掌握的知識，分析判斷某種可能性，然后再驗證是否是這個原因。在不斷地拋出疑問，不斷地驗證糾錯中，逐步接近問題的本質。

這是某數據倉庫系統，有一個作業在某天出現較大延遲。原來作業只需要運行10幾分鐘，現在需要運行2個多小時，這是業務不能接受的。為了不影響明天的業務系統，必須在今天解決這個問題。經和開發人員的溝通，該業務的SQL語句沒有修改，相關的數據結構也沒有變更相類似的其他業務（SQL語句相似的）也都正常運行，數據庫系統本身也沒有異常。

在排除了諸多異常后，這個問題似乎變得很棘手，原本運行正常的SQL語句，忽然在某一天變得異常緩慢。針對這個問題，我采取步步為營的策略，逐步排除可能的原因，并最終找到問題本質，圓滿地解決了該問題。

看下面的代碼：

INSERT INTO xxx
SELECT  ...
FROM ...
LEFT JOIN t1 a ON t.product_id = a.product_id AND ...
LEFT JOIN t2 b ON t.product_id = b.product_id AND ...
LEFT JOIN t3 c ON t.product_id = c.product_id AND ...
LEFT JOIN t4 d ON t.product_id = d.spxxid AND ...
LEFT JOIN t5 e ON t.product_id = e.spxxid AND ...
LEFT JOIN t6 f ON t.product_id = f.spxxid AND ...
LEFT JOIN t7 g ON t.product_id = g.spxxid AND ...
LEFT JOIN t8 h ON t.product_id = h.product_idAND ...
LEFT JOIN t9 I ON t.product_id = i.prod_id
LEFT JOIN t10 j ON t.product_id = j.prod_id AND ...
LEFT JOIN t11 k ON t.product_id = k.prod_id AND ...
LEFT JOIN t12 l ON t.product_id = l.prod_id AND ...
LEFT JOIN t13 m ON t.product_id = m.prod_id AND ...
LEFT JOIN t14 o ON t.product_id = o.product_id；

這是一個多達15個表的關聯查詢（非常佩服開發人員，邏輯思維太強了）。查詢的結果集有400多萬條，并插入目標表中。其中目標表較大，有7億多條記錄，物理大小為380GB。在之前的運行過程中，用時十幾分鐘。

第一步猜測——執行計劃異常導致的問題？（固化執行計劃）

最開始想到的方法很簡單，既然類似的SQL執行效率沒問題，而這個SQL由于其他SQL執行計劃偏差較大，可以手工采取固化執行計劃的方法。這里使用了抽取OUTLINE的方式，具體方法可參見后面的內容。

其調整后的執行計劃如下，跟其他類似SQL的執行計劃相同。整個執行計劃基本可概括為“HASH JOIN”+“FULL TABLE SCAN”。

INSERT INTO RPT_PROD_DAY
SELECT
/*+
     ...
     ...
     FULL（@"SEL$30069D69" "T"@"SEL$4"）
     FULL（@"SEL$30069D69" "O"@"SEL$1"）
     FULL（@"SEL$30069D69" "J"@"SEL$21"）
     FULL（@"SEL$30069D69" "I"@"SEL$19"）
     FULL（@"SEL$30069D69" "F"@"SEL$13"）
     ...
     LEADING（@"SEL$30069D69" "T"@"SEL$4" ...
     USE_HASH（@"SEL$30069D69" "O"@"SEL$1"）
     USE_HASH（@"SEL$30069D69" "J"@"SEL$21"）
     USE_HASH（@"SEL$30069D69" "I"@"SEL$19"）
     ...
*/
...

采用上述方式處理后，整體運行時長減少了10多分鐘，但仍然超過了2個小時。顯然，對執行計劃異常的判斷，不是問題的主因。

第二步猜測——緩存搗的鬼？

進一步檢查發現，在執行過程中發現了大量的“db file sequential read”等待事件。這個不太尋常。一般情況下，全表掃描會產生“db file scattered read”等待事件。產生后者的原因通常是在buffer中緩存了大部分數據，優化器才可能決定不使用順序讀的方式從文件中讀取數據。因此數據庫版本是10g，不能直接干預全表掃描是從緩沖區中讀取還是文件中讀取（11g是可以的），只能采取其他方式。建議更換相關作業執行順序，避免緩沖區干擾。經測試，速度還是沒有明顯提升。第二步猜測失敗。

第三步猜測——究竟是哪個對象導致的？

進一步分析SQL執行時的情況，發現忽略了一個關鍵信息，那就是產生“db file sequential read”等待事件的對象。人們往往想當然地認為全表掃描是表，經檢查后發現其是一個索引，而且這個索引是目標表的全局索引，相關聚簇因子非常大，接近表的行數。在插入的過程中，需要大量維護索引成本。此表本身還有另外兩個索引，都是本地分區索引，維護成本很低。

跟開發人員溝通后，該索引是前一天臨時加入的，且沒有通過DBA審核。開發人員個人覺得全局索引效率較高，因此就建成了全局的。后續將此索引修改為本地分區索引。經測試，速度從2個多小時縮減到12分鐘，問題得到解決。

2.給我們的啟示

• 優化SQL就是一個抽絲剝繭找到問題本質的過程。在不斷猜測、不斷試錯的過程中，逐步接近事件的本質。你所掌握的知識點越多，可“猜測”的可能性就越多。
• 數據結構的變更要經過DBA的審核，這樣可以避免很多問題，也可以盡早發現問題、解決問題。