案例6　“抽絲剝繭”找出問(wèn)題所在

1.案例說(shuō)明

這個(gè)案例本身不是為了說(shuō)明某種技術(shù)，而是展現(xiàn)DBA在分析處理問(wèn)題時(shí)的一種方式。其采用的方法往往是根據(jù)自己掌握的知識(shí)，分析判斷某種可能性，然后再驗(yàn)證是否是這個(gè)原因。在不斷地拋出疑問(wèn)、不斷地驗(yàn)證糾錯(cuò)中，逐步接近問(wèn)題的本質(zhì)。

這是某數(shù)據(jù)倉(cāng)庫(kù)系統(tǒng)，有一個(gè)作業(yè)在某天出現(xiàn)較大延遲。原來(lái)作業(yè)只需要運(yùn)行十幾分鐘，現(xiàn)在需要運(yùn)行2個(gè)多小時(shí)，這是業(yè)務(wù)不能接受的。為了不影響明天的業(yè)務(wù)系統(tǒng)，必須在今天解決這個(gè)問(wèn)題。經(jīng)和開發(fā)人員的溝通，該業(yè)務(wù)的SQL語(yǔ)句沒有修改，相關(guān)的數(shù)據(jù)結(jié)構(gòu)也沒有變更相類似的其他業(yè)務(wù)（SQL語(yǔ)句相似的）也都正常運(yùn)行，數(shù)據(jù)庫(kù)系統(tǒng)本身也沒有異常。

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

看下面的代碼：

---

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;

---

這是一個(gè)多達(dá)15個(gè)表的關(guān)聯(lián)查詢（非常佩服開發(fā)人員，邏輯思維太強(qiáng)了）。查詢的結(jié)果集有400多萬(wàn)條，并插入目標(biāo)表中。其中目標(biāo)表較大，有7億多條記錄，物理大小為380GB。在之前的運(yùn)行過(guò)程中，用時(shí)十幾分鐘。

第一步猜測(cè)——執(zhí)行計(jì)劃異常導(dǎo)致的問(wèn)題？（固化執(zhí)行計(jì)劃）

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

其調(diào)整后的執(zhí)行計(jì)劃如下，跟其他類似SQL的執(zhí)行計(jì)劃相同。整個(gè)執(zhí)行計(jì)劃基本可概括為“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")
      ...
*/
...

---

采用上述方式處理后，整體運(yùn)行時(shí)長(zhǎng)減少了10多分鐘，但仍然超過(guò)了2個(gè)小時(shí)。顯然，對(duì)執(zhí)行計(jì)劃異常的判斷，不是問(wèn)題的主因。

第二步猜測(cè)——緩存搗的鬼？

進(jìn)一步檢查發(fā)現(xiàn)，在執(zhí)行過(guò)程中發(fā)現(xiàn)了大量的“db file sequential read”等待事件。這個(gè)不太尋常。一般情況下，全表掃描會(huì)產(chǎn)生“db file scattered read”等待事件。產(chǎn)生后者的原因通常是在buffer中緩存了大部分?jǐn)?shù)據(jù)，優(yōu)化器才可能決定不使用順序讀的方式從文件中讀取數(shù)據(jù)。因此數(shù)據(jù)庫(kù)版本是10g，不能直接干預(yù)全表掃描是從緩沖區(qū)中讀取還是文件中讀?。?1g是可以的），只能采取其他方式。建議更換相關(guān)作業(yè)執(zhí)行順序，避免緩沖區(qū)干擾。經(jīng)測(cè)試，速度還是沒有明顯提升。第二步猜測(cè)失敗。

第三步猜測(cè)——究竟是哪個(gè)對(duì)象導(dǎo)致的？

進(jìn)一步分析SQL執(zhí)行時(shí)的情況，發(fā)現(xiàn)忽略了一個(gè)關(guān)鍵信息，那就是產(chǎn)生“db file sequential read”等待事件的對(duì)象。我想當(dāng)然地認(rèn)為全表掃描是表，經(jīng)檢查后發(fā)現(xiàn)其是一個(gè)索引，而且這個(gè)索引是目標(biāo)表的全局索引，相關(guān)聚簇因子非常大，接近表的行數(shù)。在插入的過(guò)程中，需要大量維護(hù)索引成本。此表本身還有另外兩個(gè)索引，都是本地分區(qū)索引，維護(hù)成本很低。

跟開發(fā)人員溝通后，該索引是前一天臨時(shí)加入的，且沒有通過(guò)DBA審核。開發(fā)人員個(gè)人覺得全局索引效率較高，因此就建成了全局的。后續(xù)將此索引修改為本地分區(qū)索引。經(jīng)測(cè)試，速度從2個(gè)多小時(shí)縮減到12分鐘，問(wèn)題得到解決。

2.給我們的啟示

·優(yōu)化SQL就是一個(gè)抽絲剝繭找到問(wèn)題本質(zhì)的過(guò)程。在不斷猜測(cè)、不斷試錯(cuò)的過(guò)程中，逐步接近事件的本質(zhì)。你所掌握的知識(shí)點(diǎn)越多，可“猜測(cè)”的可能性就越多。

·數(shù)據(jù)結(jié)構(gòu)的變更要經(jīng)過(guò)DBA的審核，這樣可以避免很多問(wèn)題，也可以盡早發(fā)現(xiàn)問(wèn)題、解決問(wèn)題。