深入解析和定製Oracle優化工具

首先不會Oracle的我覺得也可以聽懂。哈哈，因為我不會專門講oracle裡的太細的東西。這部分的內容比較通用，可以借鑑思路。

我會在我的平臺裡面糅合這些思想，總之有貨有料之後，加上時間和精力，就好比陽光空氣水。

ppt有一部分是我在InfoQ的一次大會上做的一個簡單的分享，今天在原來的ppt基礎上重新做了一番解讀。

這是我眼中的一些問題，有些Oracle已經做好了，對於一個成熟的商業軟體來說，儘管功能上滿足了，還是有些地方值得改進，或者說他們做得還不夠好的地方。

這也體現了處理問題的幾個階段，有些人頭疼止疼，有些人能夠提前發現問題，有些人可以更早的規避問題，如果從這個境界來說，越到高的境界其實會比較尷尬，因為問題完全沒發生就扼殺在搖籃裡了，很難體現出價值，會比較尷尬。

有一個故事是關於扁鵲的，我沒有求證出處，但是能夠說明問題。

用扁鵲的話來說就是:"我大哥的醫術之高，可以防患於未然，一個人的病未起之時，他一望氣色便知，然後用藥將其調理好，所以天下人都以為他不會治病，他便一點名氣都沒有。我二哥的能耐，是能治病初起之時，防止別人釀成大病。病人剛開始感冒咳嗽時，他就用藥將人治好了，所以我二哥的名氣僅止於鄉里，被人認為是治小病的醫生我呢，就因為醫術最差。所以一定要等到這個人病入膏肓、奄奄一息，然後下虎狼之藥，起死回生。這樣，全世界便都以為我是神醫。想想看，像我大哥這樣治病，人的元氣絲毫不傷，我二哥治病，這個人元氣稍有破損就補回來了，像我這麼治病呢，命是救回來了，可元氣大傷，您說，我們家誰醫術最高明？

所以對於運維來說，說句私心話，有些時候甚至希望有些問題讓他發生，才能被大家重視，有些人可能對此有很深的體會。本意上我希望大家能夠多碰到一些問題，多解決一些問題，三觀肯定是正的。

說了這麼多問題，我們來看看Oracle優化工具定製和這個有什麼關係。

我們先來看看Oracle的優化工具，如果你沒聽過其實也沒關係，你可以這樣設想一個場景，有一個資料庫，cpu負載突然在凌晨的一個時間點升高，造成了業務的阻塞，引發了一系列的問題。如果你是個DBA，該如何思考和處理。

假設你早上10點到公司，而問題發生在凌晨2點，這個問題該如何診斷和分析。

因為對於資料庫來說，那個故障狀態已經過去了，如何捕捉那個時間點的問題呢，這個詞語在早些年會被經常提到，那就是診斷。

我們要分析這個問題，如果在oracle9i的版本中，那簡直就是噩夢。我知道早期的阿里DBA裡被這種問題搞得很痛苦。如果凌晨2點出問題，怎麼解決呢。那就是2點的時候守在電腦前面。

有的同學說，這種方式實在太low了。oracle有statspack啊。有這個工具不假，但是問題很可能被平均化，比如資料庫的負載在一個小時內分別是1%，100%，如果平均下來就是50%，問題被平均化，那會遮蔽掉太多的問題，遮蔽不意味著解決。

所以，我說的最基礎的工具就是AWR,能夠監控資料庫的整體負載。週期可以在半個小時到1個小時都有。這樣的缺點很明顯，問題發生了一段時間之後你才能發現屬於後知後覺。

在這個基礎上改進，oracle做了一個相當大的改進，也就是遠超MySQL在這方面的一個工具，ASH.

ASH簡直被稱為神器，它會在後臺收集資訊，頻率是多少，1秒。所以我們診斷問題可以細化到秒級。這個對效能影響大嗎，肯定有，但是非常非常低。

ASH有個缺點就是沒法和一些詳細的資訊關聯起來，因為它只抓取的是活躍會話，有些資訊是沒有的。所以不能說它是萬金油，但是換一個角度來看，引起的問題的大部分都是活躍會話。所以這個覆蓋面基本足夠了。

說完了AWR,ASH,來看看ADDM.

大家都知道Oracle的現在版本是12c,12cr1在大概6年前就釋出了，12cr2是被DBA公認為穩定的版本，這個版本大家足足等了6年左右。這對Oracle和很多DBA來說是很難描述的一種窘態。

所以Oracle肯定也意識到了這個問題，這方面我猜是和SQL Server的玩法類似了，那就是一年一個版本，這樣就會弱化大家對版本的敏感性，所以今年會推出18c,也就是說到的自治資料庫，接下來還會有19c,20c（這個是真有）

有的同學說Oracle都自動優化了，是不是沒Oracle DBA什麼事兒了，其實不然，too simple.

Oracle不可能一下子推出一個本來沒有的東西，12c有了cdb，相當於在一個30多年的建築上動了地基，搞出一個自治資料庫，難道改動很大，其實不然，如果我們仔細看會發現，其實這些都會成為自治資料庫的一些關鍵元件。而這些Oracle已經有了一些自動化解決方案。

所以自動化診斷（ADDM）,SQL自動優化建議（SQL Tuning,SQL Monitor,SQL profile）都是迭代完成的，引入了更加動態的處理方式不斷完善而已。

儘管如此，這些優化工具在我看來還是半成品，因為使用起來還是不太爽。所以我就想辦法來做一些改進，哪怕原來步驟需要手工操作3次，簡化到2次，我認為也是優化。

這裡需要大家注意的就是定製的時候，需要明確一把標尺，那就是你解決一個問題，解決的問題更多，還是帶來的問題更多。做一件事情，能夠做到思路共用，我覺得你做Oracle還是MySQL，還是其他資料庫，都會有很大幫助的。

讓我們來看看生成一個AWR報告的步驟，就好比大家去醫院，先抽血（這裡可以叫做取樣），生成驗血報告（AWR報告），然後大夫看哪個指標高了，哪個指標低了，很類似的。

說到這裡，要提到一個人，那就是張曉明，他還真是個大夫，後來轉oracle了。

生成一個awr報告的步驟如下：

我列了五個步驟，那就意味著五個步驟都需要人工操作介入。可以想象你有50個資料庫，那會是多痛苦，這種感覺就好比你是一個班主任，一個班的孩子，你沒法一個一個的去聊天談話，我們只是需要把握一個整體的狀態，然後更多幫大家解決問題（有效能故障的資料庫）

我原來處理效能問題，每天要生成大量的報告，最後受不了了。要搞明白這個優化的定製，就得明白它的實現原理，我決定改進一下。

AWR的指令碼呼叫關係如下，其實明白了呼叫關係，我們就可以有針對性的看看哪些地方可以改進了。

awrinput.sql是負責輸入引數，awrinpunm是負責引數的名字，打問號的地方是關鍵，他的實現就是下面列到的一個包dbms_workload_repository

所以到了這裡我們看看AWR的實現原理，有了這個圖，ADDM,AWR,ASH都能看個大概了。

簡單來說，就是Oracle從記憶體級別去抓取一些資料庫的變化，然後通過MMON後臺程式來協作，把資料寫入快照，快照級別的效能差異就是AWR報告，而ADDM則是對快照級別的資料庫進行分析，ASH略有不同。

明白了這些，開始幹活吧，我們明確定製的方向。不是一口氣吃個大胖子，也不是寫出一個驚天的大作，能用能滿足需求就行.

然後問題來了，我們定製AWR的時候其實還有些事情要做，我們快速得到AWR的意義是什麼，為什麼要看這個AWR報告。這個問題要想明白，比你忙裡忙活定製好多了。

主要是因為這個，DB time，我認為它是看AWR最重要的一個指標，沒有之一，如果沒有這個參考，其他的資料庫都失去了意義。

如果想看個正規的解讀，可以看看這個解釋，不懂也沒關係，略過。

我們的方向明確了之後，定製就很容易了，我們定製輸入的引數就可以了，這些完全可以預先生成。比如你得到了這樣一個列表，你可以很清晰的看到哪個時間點的效能高了，我不用生成所有的快照報告，所以我後面達到的狀態就是上班瞅一眼db time的值，如果高了就生成awr報告，否則就不用太關注了，該幹嘛幹嘛。

指令碼其實很簡單，明確了痛點，指令碼也很短，其實需要就會轉化為，DB time高不高，如果高生成awr報告。否則不生成。

指令碼可以從這裡下載，https://github.com/jeanron100/dbm_lite

另外提一下awr format,如果一個DBA前端開發能力很強悍，那戰鬥力是很驚人的，awr format就是一個DBA開發的，能夠把awr報告做進一層提煉。

awr的部分其實花了80%的筆墨，如果定製ash，addm就很自然了，就跟大家去駕校考駕照，前面8節課都是練感覺，後面兩節課很快就能學會。

ash的定製也是類似的思路，比awr還要簡單一些。輸入兩個時間戳即可。

定製ADDM需要一些pl/sql的基礎知識，它會在pl/sql裡呼叫幾個流程來建立優化任務，生成優化報告。我們還是動態繫結幾個引數即可搞定。

補充下ASH的原理圖。這個對大家理解ASH很有幫助。

這個資料是分為兩份，一份是落盤，一份是在快取裡。所以一個資料庫如果出現當機，那很可能快取級別的ASH是會丟失的，因為沒有落盤。

這類問題是比較難查的。這方面需要我們在監控的細節上做更多的工作。

SQL優化的部分內容就更多了，說起來都是辛酸淚，限於時間，就先分享到這裡吧。

我來繼續補充一下SQL定製的一些嘗試，算是拋磚引玉。

如果細分，可以分為這四類，怎麼理解呢，ADDM裡面會對潛在的SQL問題進行分析，是基於快照級別的。

他只會分析告訴你某個SQL執行花費了較多的時間，可能有問題，但是不能告訴你具體該怎麼優化。

而SQL Tuning算是一個這方面的專家，他會告訴你哪個SQL有潛在問題，該加索引了，該調整統計資訊了等等。

但是目前詬病比較多的就是這個SQL Tuning Advisor,因為根據我們的實踐，絕大多數情況下，他給出的分析都不是很靠譜，我這麼說可能oracle不樂意了。

為什麼呢，因為有些表設計是基於業務的，我也知道加一個索引，對這個SQL會有幫助，但是其他的SQL,從設計角度來說，這個工具沒法做到下鑽，如果能做到，那麼DBA的崗位就岌岌可危了。

所以我比較喜歡的工作方式就是，如果有效能問題，對某個SQL優化沒有思路的時候，看看oracle怎麼建議。

雖然絕大多數情況下我不會採用它的建議，但是有總有那麼幾次它給的建議是我沒意識到的。所以這就是工具的好處，完全靠經驗，還是會有疏漏，多年前的攻略到了如今，到已經整合到產品裡面了，老DBA的日子其實不好過了。以前的RBO時代，SQL優化真是酸爽，DBA說啥就是啥。

再來看看SQL profile,如果你的優化經歷中沒有SQL Profile的經驗，這個是要減分的。這麼說絕對不是唬人，或者自立flag.

這種場景是DBA的價值被嚴重高估的時候，一般碰到這類問題的時候都是火燒眉毛。

改應用程式碼，根本不可能，甚至說能改，重新部署要重啟服務，那肯定不靠譜，所以不改程式碼，不改SQL,不加索引，而且能優化SQL，這個操作會給你的職業生涯大大加分。

我的職業生涯中碰到過多次，大多數情況下是很拉風的，有兩次比較尷尬，第一次是有個SQL優化後，一年後問題爆發出來，簡單理解就是這個表原來是10萬的資料，用繫結的執行計劃效果很好，但是一年後資料量是1000萬，那原來的執行計劃就不行了，如果弄個併發，搞點負載上來，這個問題就會被放大。所以說SQL profile處理的正確姿勢就是做為臨時解決方案可行，但是絕對不建議做為永久的解決方案，如果一個資料庫裡有大量的執行計劃繫結，就好比打了n多的補丁。就別說優雅了，看起來很簡陋。

另外一個尷尬的情況就是優化過度，這個怎麼理解呢，我給開發優化SQL達到了高效處理，反饋必達，基本他告訴我應用有卡頓的時候，我不到5分鐘就優化好了，給應用同學造成的假象就是這個活很easy啊。開發的主管找我說，讓我給開個許可權，他們自己優化得了。實際情況是，還不成熟。

但是退一步來說，這種事情最終還是要被替代的，你不改進，那就oracle改進，這不18c來了，這部分肯定會有改動和優化。

簡單舉個例子。

如果某一個SQL執行計劃很好，消耗很低，但是執行的時候效率很差，一定是哪裡出了問題。

這就好比一個人簡歷看起來亮亮堂堂，但是工作能力一般。問題的瓶頸在那裡呢，怎麼下鑽呢，從hr的角度來說，招人的工作完成了，但是用人部門來說，帶來的影響是很大的。所以要找到這個瓶頸點，我們就需要做資訊下鑽。根據真實的執行情況來得到整個SQL的執行計劃情況。

如上圖所示，可明顯看到做索引掃描的時候，估算是2000多條記錄，但是實際上4G的記錄，這可以分為幾個分支來考慮，比如索引使用不當，統計資訊不合理，查詢條件不合理等等。逐個去排查下鑽，很快就能定位到問題。

我知道一些高手做優化是反著來的，就是先看執行計劃，然後反推SQL是什麼，如果能達到這個水平，說明你是在和優化器在一起賽跑了。

要得到一個SQL的html報告，還是很簡單的，直接呼叫這個包就好。

SQL monitor用好了，你的職業幸福度會大大提升，當然我花了不少時間琢磨這個東西，可以看看之前的總結。http://dbaplus.cn/news-10-705-1.html

SQL Monitor的報告分為幾種級別，比如簡化的文字，略有紫色的html，還有豐富的active版本，就好比小米手機一樣，標準版，高配版，尊享版

說了SQL Monitor的優點，我更希望說說他的缺點

缺點也很明顯，到目前為止，這部分都有待改進，儘管報告的內容豐富了很多，但是如果我說幾天之前的一次效能問題，我想看看SQL monitor的報告，抱歉這個拿不到，勉強能實現需求的就是AWR中抽取的一個SQL報告。

所以這一點mysql做的好一些，有慢日誌，都記下來了。oracle也有慢日誌類似的實現，oracle裡面，有些業務可能3秒都不是事兒。所以SQL Monitor預設的是5秒，當然這個可以調的。

我是個比較喜歡折騰的人，所以我就準備做一些改進，我的改進比慢日誌優雅一些，那就是週期性掃描，如果發現SQL效能問題，就把對應的SQL生成一個SQL monitor報告，如果反覆存在就生成一次。這個細節上其實還可以優化。

後期實現的一個目標如下。生成了大量的sql報告，我真是快到到上班的時候喝喝茶，看看報告的地步了。

有問題的時候就把SQL優化好，然後發給開發確認下，所以這樣我和開發接下來戰鬥友誼，因為他們的問題基本上都脫不出我的眼睛。

sql profile的地方再囉嗦一句

如果你要高效的優化，sqlt是需要會用的，這是oracle coe部門在用的

簡單展望一下，我覺得多年前的這個展望如今依舊適用。有的同學說我沒有阿里那樣的體量，優化工作難有動力，沒有困難，你要給自己創造困難，我這裡說的不是你把資料庫停了，製造故障。

而是你做精細化運維，如果你把一個關鍵業務的系統優化到了極致，什麼時候效能高，什麼時候效能低，高是什麼原因，都能摸得熟，那你已經在一個至高點了。

所以在這方面我完全不用羨慕那些高大上公司的兄弟，至少在oracle的體量上，堆不了太大的規模，主要是做關鍵業務，集中化管理。和MySQL管理的思路不大一樣。

另外就是半自動化，現在自動化提的太多，有些關鍵的地方一定要做到半自動化，不怕慢，就怕錯。這個地方一定要慎重。

我是不希望我的主庫被隨意改動，哪怕它的設定確實不合理，奇葩，如果在維護時間裡，我可以把它掰正，但是除此之外主庫就是主庫。

對大家的優化來說，這只是開始，現在的時代給大家的挑戰很大，不要輕視這些挑戰，不要莫名的排斥，現在的發展變化已經遠超出了我的預期，所所以我能想到更多的挑戰和可能出現的很多問題，溫水煮青蛙的故事我不希望在我們的身邊發生。

我的分享就到這裡，感謝大家。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23718752/viewspace-2152336/，如需轉載，請註明出處，否則將追究法律責任。