風馳電掣：有效縮短SQL優化過程三步走！

作者介紹

梁敬彬，福富研究院副理事長、公司唯一四星級內訓師，國內一線知名資料庫專家，在資料庫優化和培訓領域有著豐富的經驗。多次應邀擔任國內外資料庫大會的演講嘉賓，在業界有著廣泛的影響力。著有多本暢銷書籍，代表作有《收穫，不止Oracle》。

學習完第一章內容的小王，知道了解決問題要先整體再局部，從此不再盲目解決問題了。關於整體性方面，他不僅知道了五大性能報告的具體用途，還明白了如何獲取到這些報告，甚至還知道如何解讀這些報告。如今的他會對著獲取到的這幾張報告眉頭緊鎖，認真研讀，自言自語，不住點頭，指點江山，激揚文字……彷彿一位住在地下室的青年，正在思考國家下一步該怎麼走？如何突破美國封鎖？如何收復台灣？如何保住南沙釣魚島……

這個結果似乎讓我們有些熱血澎湃，不過提升效率的重點內容會出現在本章，能否從地下室搬出來，就在此一舉了。

故事中有一個細節，便是小王光發現問題就整整花了1個小時，這時間可不短啊。如果系統故障要花整整1小時才能找到，那這1小時的時間對客戶來說，是多麼難熬的1小時啊。

而且，更可悲的是，這1小時小王並沒有解決問題，故障依舊。

有人問，為啥會花費1小時的時間呢？嗯，本章就開始探討這個問題。首先說明調優時間都花在哪兒，接下來分析如何縮短，並結合案例來鞏固知識，最後大家思考回顧。

總體學習思路如下圖所示：

一、SQL調優時間都去哪兒了

我們先來分析調優時間都去哪兒了。1小時才能得到解決方案固然讓人接受不了，事實其實更糟糕，小王是只花費1小時嗎？顯然不止1小時！

他到下午還在解決這個故障，甚至第2天他還在解決這個問題，這已經1天了。小王只花了1天時間嗎？顯然不止1天！

實際情況是小王最後崩潰了無法解決問題。顯然這是無限期！

面對這麼凄慘的事情，我這裡總結出幾點經驗，來分析這個問題。

1、不善於批處理頻頻忙交互

故事中沒有細說小王和求助者之間的交互細節，但是從花了整整1小時才發現需要加索引來看，小王的工作效率肯定不高。我們可以猜測他發生了什麼事。也許小王本身無法訪問這個系統，需要通過對方執行自己的腳本來反饋結果。於是他想了解這個語句的返回記錄的量，又想要了解這個語句的執行計劃，又想了解這個語句對應的表和索引的信息……電話、QQ、郵件不斷地交互，於是1小時就這麼過去了。就像下圖所示這樣：

2、無法抓住主要矛盾瞎折騰

小王沒有從整體出發來考慮問題，沒有想明白是整體問題還是局部問題，一路抓瞎，無端耗費時間卻徒勞無功。

3、未能明確需求目標白費勁

有的SQL其實執行得雖然慢，但是客戶並不是很在意，這樣的SQL除非是耗盡資源影響到全系統，否則不見得就需要立即優化。

不過這是我提出的一個重要意識，在小王的案例里，這個問題倒是不明顯，因為這個SQL客戶已經明顯感覺慢，提出要優化了。

4、沒有分析操作難度亂調優

其實有一個意識非常重要，SQL好調優嗎，調優空間大嗎？小王遇到這個SQL的時候，其實應該首先知道這個SQL返回記錄有多少，如果很少，就說明調優空間很大。反之就要考慮特殊手段了。在小王的案例中，小王根本沒有這個意識。

二、如何縮短SQL調優時間

1、先獲取有助調優的資料庫整體信息

如何縮短SQL調優時間呢？我覺得要先把你進行SQL優化的思路理順。當你要優化SQL時，你的一般流程是什麼？

首先要知道整個資料庫的運行情況，我們上一章中介紹過資料庫AWR報告等調優工具，不過介紹得並不全面，因為AWR報告等是在資料庫出問題時的利器。可是如果資料庫當前沒有出問題呢？其實不見得，很多時候系統沒問題是因為你沒觸發這個問題，其實是有問題的。比如某表的索引失效了，某SQL訪問該列時一定只能走全表掃描；比如某表的屬性被設置了並行度，這意味著所有掃描該表的SQL都會並行執行，這可能會產生嚴重的資源爭用從而讓系統癱瘓；比如你的全局臨時表被收集了統計信息，訪問該表的SQL就可能會出現錯誤的執行計劃等。

不過你的AWR報告卻可能發現不了這些問題，比如該時段和這些對象相關聯的SQL根本就沒有執行。沒發現問題並不代表沒有問題！

因此我們需要獲取所有可能有問題的對象，同時也需要一鍵獲取所有的相關時段的AWR等資料庫整體性能報告，獲取資料庫的整體信息。假如這些信息能一鍵快速獲取，那解決問題的效率肯定會高很多。

特別提醒：

這裡再次強調獲取的相關時間段問題，我們自動獲取AWR等幾大性能報告是需要選擇時間段的。那麼如何選擇呢，能高效全自動判斷嗎？其實動動腦子就可以想到，我們可以在資料庫里自動判斷哪些時段的資源消耗最大，然後直接就提取該時段的性能報告，甚至可以取前幾名時段，然後我們去自動提取，這不行嗎？

2、快速獲取SQL運行台前信息

接下來，在獲取到資料庫整體信息後，調優的方向就非常明確了，對具體的SQL進行調優。執行計劃是SQL調優的重要武器，通過分析SQL計劃，我們可以判斷SQL的訪問路徑是否高效，從而進行調整優化。關於執行計劃的獲取手段有6種之多，這是為啥呢？各有什麼區別？答案依然展示在後面的章節中。

還需要將執行計劃和運行時的統計信息結合在一起分析，這樣才會更準確。比如SQL產生了多少邏輯讀，多少物理讀，是否有排序，是否有遞歸調用，等等。具體細節依然見後續章節。

3、快速拿到SQL關聯幕後信息

當獲取到SQL的執行計劃後，諸多的確認都和該SQL對應的表和索引有關。比如當我們懷疑驅動表的順序有錯時，我們就會去看看這些表的實際大小和對應的統計信息是否準確；我們也關心表的類型是什麼，比如是否是分區表，在哪個列有分區，分區的類型是什麼，等等。

除了關注表的信息，我們也很關心索引的信息。比如看到執行計劃中非常適合走索引的查詢走了全表掃描，我們就會去看看是否該列無索引，如果發現有，就看看此列索引是否失效了。

一般我們也會關心索引的類型是什麼，是Btree索引還是點陣圖索引還是函數索引；是單列索引還是組合索引，如果是組合索引，哪列在前；如果索引建在分區表上，我們還關心是全局索引還是局部索引等。

總之，我們希望能一目了然地掌握該SQL涉及的所有表和列的相關信息，最好一鍵就展現在我們面前。這樣，解決問題就非常高效了！

大家可能已經猜到了，我們又要來一個一鍵獲取了，這次獲取的是SQL對應表和索引的相關信息，沒錯，你猜對了。乾貨腳本可通過以下鏈接獲取：

https://github.com/liangjingbin99/shouhuo/tree/master/%E7%AC%AC02%E7%AB%A0

總體情況，詳見下圖：

說了這麼多，你是不是有些躍躍欲試了，可能特別想知道到底該如何一鍵獲取SQL整體信息和SQL的相關信息吧。很顯然，這樣效率一定能提升很多！好吧，下面就跟隨我一起看看吧。

三、從案例看快速SQL調優

1、獲取資料庫整體的運行情況

步驟1（構造環境，對當前資料庫進行各種操作）

sqlplus "/ as sysdba" @d:mkdb.sql

步驟2（運行腳本，對當前資料庫進行整體提取）

sqlplus "/ as sysdba" @spooldb.sql

步驟3（輸出對應日誌文件，以供後續進行分析）

輸出如下5個文件，其中ADDM是最近一小時文件，ash是最近半小時文件，而AWR文件是最近一小時和最近7天的兩個文件，spool打頭的文件輸出資料庫所有的相關信息。有了這5個文件，基本上資料庫的情況可以了解得比較清晰。具體文件如下圖所示：

具體這5個文件我們就不一一打開給大家看了，否則篇幅顯得過大。其中spool打頭的這個文件基本涵蓋了你想獲取的所有相關資料庫信息。如：資料庫版本、資料庫參數、主機參數、異常的表和索引信息（表分區有無異常、並行度問題、索引是否失效、是否過大、是否為分區索引、索引類型、哪個列上有索引、索引過多、組合索引的組合列過多、全局臨時表的異常信息收集情況，等等）、日誌切換情況、序列情況、異常觸發器、異常外鍵設置等。

如果要在書中展現，預計要有20多面，這裡只告訴讀者大致的內容。在線上會展現腳本，並進行視頻演示，給讀者看實際操作。

2、獲取SQL的各種詳細信息

步驟1（構造環境，執行部分效率低下的SQL）

sqlplus "/ as sysdba" @d:mksql.sql

步驟2（對當前的性能低下的SQL進行收集）

步驟3（對當前的性能低下的SQL進行收集）

我們獲取到了這個SQL的詳細執行計劃和對應的表及索引信息，從而大大提升了效率，如下圖所示：

大家不要小看了這個一鍵獲取SQL相關詳細信息的威力。當你認為一條SQL有問題時，只要獲取到這些信息，根本就不需要進行任何與現場人員的交互動作，因為你要的東西都已經被收集在一起了。讓我們一起看看下面一個技術人員解決問題的一個思維片段。

這語句該如何優化，讓我先瞧瞧這語句的執行計劃是啥？

哦，原來執行計劃就在這裡，太詳細了！

奇怪，這裡的執行計劃不對啊，應該是要用索引啊，怎麼會沒有，難道是統計信息不準確，還是說沒建索引，還是說索引失效了？

咦，相關統計信息的收集是正常的，奇怪。我再看看，這列有索引嗎？咦，是有，奇怪了。哦，索引失效了，原來如此。看來我重建一下索引問題應該可以解決……

看完這段話有何感觸，如果沒有高效的一鍵收集手段，你的所有信息都要在資料庫中查詢而獲取，如果需要讓他人給你信息，那就更麻煩了，小王1小時後才得出如何優化的事情，正是因為無法直接獲取全部有效信息，不斷交互導致的。

四、總結與延伸

1、場景再現

我們來假想一下學過這兩章內容和擁有工具腳本的小王，是什麼樣的場景吧。

場景1：

一鍵獲取資料庫整體信息，發現整體主機資源不足，並定位到耗盡資源的外部應用程序，然後協調相關人員，將外部應用移走或者優化，問題迅速準確地得到解決。

場景2：

一鍵獲取資料庫整體信息，根據在第1章中學到的相關知識，發現是某些SQL需要優化，然後根據一鍵所得的SQL詳細信息，判定SQL問題在哪裡，問題迅速準確地得到解決。

場景3：

一鍵獲取資料庫整體信息，也獲取到SQL相關詳細信息，不過不知該如何下手優化，求助他人。由於提交的信息詳細，無須交互迅速得到對方的建議幫助，問題迅速準確地得到解決。

2、腦洞大開

學到這裡，大家已經明白筆者的用意了，後續請讀者下載一下腳本（鏈接https://github.com/liangjingbin99/shouhuo/tree/master/%E7%AC%AC02%E7%AB%A0），體驗一下高效的過程。其實完善是沒有盡頭的，這只是一個大概，更細節的實現會一直在公眾號上更新，比如一鍵獲取資料庫整體信息中我就沒有提到資料庫告警日誌、監聽日誌、主機和資料庫是否需要打哪些補丁。比如獲取的AWR和ASH等信息里，能否直接對這些報告的SQL進行超鏈接，進入到詳細SQL信息中……有沒有腦洞大開的感覺？

這兩章如果展開講，可以單獨成書。這兩章的知識落地到軟體中，就是一個非常好的資料庫高效分析診斷工具。是不是再次腦洞大開？

嗯，筆者會無償地將自己的經驗分享給大家，能幫到大家也是筆者最大的快樂！

喜歡這篇文章嗎？立刻分享出去讓更多人知道吧！