oracle資料庫應用效能最佳化經驗（培訓講義）

這是我給公司同事做的內部培訓ppt的講義，給大家分享一下。這是培訓大綱，ppt在找地方上傳，等找到了會把連結發在這裡 。

 

暫時放在csdn上，賺點下載積分:download.csdn.net/download/kingstarer/10655069

截幾個ppt頁面大家可以先預覽一下

 

大家好，歡迎大家來參加今天的技術交流，而今天打算跟大家分享的是oracle資料庫應用最佳化相關的一些經驗。這是大綱。

 

我們今天要講的內容分五部分。其中1到3是我們今天重點交流的內容，然後四和五的話就可能，稍微快一點就帶過。

 

我先給大家簡單介紹一下每個部分的內容。

 

首先第一部分會給大家介紹一下oracle資料庫的架構及一些內部實現細節。這是為了給後面介紹資料庫最佳化知識做一些鋪墊。

 

接著，我想給大家介紹一些編寫高效能sql的經驗。

 

然後在第三部分，我們會分享一下資料庫的執行計畫相關知識。我們平時發現sql跑得比較慢時，就可以查看執行計畫，找出裡面不合理的環節進行最佳化。

 

第四部分內容是關於資料庫等待事件及awr報告的一些最佳化的內介紹。awr報告相當於資料庫的體檢報告，等待事件就是一些體檢指標。我們做整個資料庫應用最佳化經常用到它。

 

第五部分的話是一些學習資料推薦。

 

下面我們開始進入第一部分內容的介紹。這是，一個oracle資料庫的架構圖。

 

這裡user process是用戶端進程的意思，也就是我們平時自己寫的程式，或者sqlplus sqldevelor這些工具。

 

我們平時說的資料庫是一個比較模糊的概念，其實它是包含兩部分組成。一部分是資料庫檔案，包括資料檔案，記錄檔等。也就是這一部分，這個才是專業術語的資料庫database。 另一部分是資料庫執行個體，一套圍繞真資料庫啟動並執行進程集合。

 

這裡要說明一下的是，不是比較舊的，我從網上抄下來的11g架構圖。現在資料庫都已經出到18c了，可能會有一些變化。另外，這圖裡面有一些不是太重要的資料庫進程，這裡是沒有發出來的。嗯，我們還是看這張圖吧，這圖估計是oracle8i時出的圖，雖然比剛才的圖缺少了一些內容，但我覺得畫的更好一些，所以後面會圍繞這張圖來介紹。

從這圖可以看出來，oracle資料庫主要分兩部分，一部分是資料庫執行個體，由資料庫後台進程和相關的共用記憶體組成。另一部分，就是DataBase這一塊，主要是資料庫檔案。我們常說的資料庫是對這兩塊內容的統稱。

這圖還是挺重要的，大家多看幾眼。接下來我們會對這圖裡面出現的名詞做一些詳細介紹。

接下來，我們對資料庫的一些重要組件，逐個介紹吧。

首先要介紹的是資料庫執行個體這塊。這一塊由兩大部分組成，一個是這個，巨大的共用記憶體塊，我們稱之為SGA。另一塊是oracle後台進程。

SGA是非常重要的概念。它主要是做為oracle庫表資料和重做日誌的緩衝和緩衝。下一頁我們會詳細講裡面具體細節。

這一頁我們重點要介紹的是oracle資料庫後台進程。這些後台進程主要負責資料庫監控，資料與日誌讀寫，故障恢複等功能。

這裡我們列出幾個比較常見的進程介紹。 Pmon和smon，這兩個進程分別用與使用者進程故障和系統故障恢複的。我們開發一般不需要關心它的工作原理，dba才需要關注。

DBWN這個進程負責把SGA裡面的資料寫回磁碟。我們剛才說過SGA緩衝了oracle資料庫表資料。裡面有部分資料是使用者用sql修改過的，需要寫回磁碟。因為資料庫寫磁碟比較耗時，所以資料修改總是先在SGA中修改，達到一定量或者資料庫空閑時才由DBWN統一寫回磁碟。

LGWR也是一個比較重要的進程。它負責把Redo日誌從記憶體中寫到磁碟，用於資料庫恢複。一個事務只有將Redo日誌寫到磁碟才能算完成。LGWR主要工作時間點是使用者發起commit命令時，或者日誌緩衝區超過1M時工作。

Redo日誌大小有限制，寫滿後會通過歸檔日誌進程轉移到歸檔日誌裡面。

CHKP是協調LGWR和DBWN的進程。詳細協調原理大家有興趣可以上網找找，我這裡主要想介紹它們之間協調需要用到的一個概念，叫系統變更號，也就是這個SCN。這個系統變更號我們可以簡單理解為是oracle事務的編號，它是隨著事務提交與時間變化而增長的。Oracle每個資料區塊都會記錄修改該資料區塊的系統變更號。資料庫做查詢時，可以根據這個系統變更號判斷記錄是否已經最近被修改過。

下面詳細介紹一下SGA的組成。

資料庫塊緩衝緩衝區，這個主要是快取資料庫的資料的。包括讀寫資料，都會先放到這個緩衝區，有空再寫回磁碟。一般交易系統這個緩衝區的命中率是很高的，超過90%。

Library Cache是一個緩衝，但它緩衝的不是資料，而是sql以及執行計畫。Oracle資料庫在執行sql前，先要產生sql的執行計畫，也就是sql的詳細執行步驟。這個步驟產生是相對比較消耗資料庫資源的。所以oracle會把產生的執行計畫暫存起來，後面如果碰上一模一樣的sql，就直接用現成的執行計畫，不用再重建。所以我們平時寫sql經常說要用綁定變數，一個主要原因就是減少資料庫執行計畫產生的時間。

重做日誌剛才有稍微提到，它是資料庫變更的記錄。例如我們發出一個update語句，把一個欄位從0變成1，redo日誌就會記錄這個欄位變成1之後的資料。資料庫修改一條記錄後不會立即寫回磁碟的，而是先寫到緩衝同時登記重做日誌。在事務提交時，只要把重做日誌輸出到磁碟，這條記錄就不會因為斷電丟失。因為一般來說，寫資料時隨機IO，寫日誌是順序IO，順序IO速度比隨機IO快很多。

與Redo日誌對應的是Undo資料。它是存放個Undo資料表空間。Undo就是撤銷，與重做是相反的過程。所以我們把欄位從0變成1時，這裡會記錄的是欄位在改變前，仍然是0的資料。

注意Undo資料表空間的資料是存放在資料庫檔案的，所以資料庫在操作復原段時也會在Database Buffer Cache進行緩衝。

Undo資料有一個非常有用的作用，就是一致性讀。Oracle執行select語句時一般是不鎖表的，但是它還是會保證返回的資料肯定是查詢開始時間點的資料，即使查詢過程中，資料被修改過。這功能實現就是需要通過查詢復原資料，獲得資料在修改前的狀態。

這是檢查點的原理，檢查點是用於資料庫斷電恢複的。大家有興趣自己細看。

資料庫執行個體介紹完，下面我們開始介紹Database這塊。Database是資料庫檔案集合的統稱，一個Database可以對應多個資料庫執行個體。我們常說的rac就是這樣，多個資料庫伺服器，操作同一個資料庫檔案。

資料庫裡面最重要的檔案就是資料檔案和聯機記錄檔。剛才我們有介紹過Redo日誌緩衝，它在使用者提交事務時就會寫到磁碟，就是這個聯機日誌。

這一頁介紹的是oracle資料庫服務進程的知識。也就是處理我們平時寫的用戶端程發出的sql請求的進程。

一個資料庫會有很多個服務進程，這些進程共同享一個SGA。但每個進程都有自己的一塊私人記憶體空間，我們稱之為PGA。PGA預設是比較小的，如果我們需要進行大表串連，可能會嫌記憶體不夠用，這時我們可以申請把PGA加大。做批量任務的同學可以注意一下。

 

但是有兩部分SB哎，就是這一塊的記憶體，然後呢？他還有另外一部分就是這個這些相關的一些後台進程是比較重要的，這是一塊巨大的共用記憶體，然後呢？它主要的功能的話就是做一個化充實這樣這些資料快讓我們愉快這些都會在裡面存，然後這個資料是這些後台進程，後來進程，包括上面這幾個，這個是每一個進程具體布置的東西監控的這個不是太重要，然後這個這個屁嗎？這個是使用者進程監控這個主要用於掛了的話就沒有正常斷開的情況下，他會幫你把這些原來重要，然後這個這個事，let這個這個這個作用的話是吧，就我們曾說過，裡面有幾塊就是把資料庫緩衝的一些資料寫回，然後呢。這個night。我只要。駛入池塘。日誌寫入進程就是跟我們資料庫在定期。寫到記錄檔。這個是匡威。歸檔歸檔進程，這個呢是這個適用於這裡面其實比較重要的話，就是需要關心的是這兩個進程，不要進程的相關知識。這裡。這裡是oracle資料庫的一些原理。這是系統改編號，就這個事，日誌進程他。他有時候。接下來我們介紹這一塊資料庫相關的知識。

下面讓我們通過一個update語句的執行過程介紹來串講剛才介紹過的知識吧。首先，用戶端會發起sql執行請求，資料庫會到緩衝池裡面尋找該sql執行計畫是否存在。如果存在則使用現成的執行計畫，這個過程稱為軟解析。如果執行計畫不存在則需要重建，這個過程叫硬解析。接著資料庫會分析sql需要訪問的資料，看是否在資料區塊緩衝裡面。如果是則直接使用快取資料，如果不是則需要從資料庫檔案讀出來放到記憶體。

接著資料庫會在記憶體中修改資料區塊，並且同時登記redo日誌到日誌緩衝區。

等到資料庫提交時，redo日誌的內容就會被刷進磁碟。資料緩衝區的內容則過一段時間由dbwn寫進磁碟。

關於資料庫的主要架構介紹到這裡。 這幾頁是資料庫塊的細節知識介紹，由於時間關係就不講了。大家主要需要知道，oracle一次硬碟操作都是以資料庫塊為單位的，而不是以一行記錄或者一個欄位為單位的。一個資料庫塊會有n行記錄。

下面進入重點交流的內容，關於資料庫應用最佳化的知識。

首先，我們在資料庫最佳化時要注意系統的類型，不同系統的最佳化目標是不一樣的。一般來說，我們把系統分為兩大類：OLTP和OLAP，也稱DSS。

OLTP全稱是線上交易系統，像我們的收單聯機系統就屬這類。這類系統的特點是：交易非常頻繁，但每次交易涉及的資料量很少。對資料庫回應時間要求比較高，一般要求幾秒甚至幾毫秒內返回。

OLAP全稱是聯機分析系統，我們平時做的批量系統就屬這類。這類系統特點時：交易比較少，但每次交易涉及資料量比較多。資料庫訪問時間要求相對寬鬆點。

Oltp系統常見資源瓶頸是在cpu和記憶體上，而oltp系統常見問題是出在io上面。

這個表格介紹了兩類系統開發的一些經驗。

OLTP由於sql運行頻次較高，一般要求使用綁定變數，減少資料庫產生執行計畫的消耗。另外，OLTP一般要盡量減少訪問資料庫磁碟的次數，盡量提高記憶體命中率。

 

 

線上分析系統一般是讀寫比比較大的系統，為了資料訪問方便會做很多冗餘。並且，為了資料庫能得出最優的執行計畫，使用綁定變數會少一些。

這是一般兩類系統在裝資料庫時的參數配置差異，也是遵循系統業務特點而配置的。

前面一節介紹了系統最佳化的總體原則，這裡會介紹一些最佳化細則。

首先是關於索引的。

索引告訴資料庫有什麼，而不是沒有什麼。所以我們平時用的不等於查詢條件是用不上索引的。

另外，索引列的資料類型我們也要注意。像這種情況tel電話號碼列用的是字串類型，但查詢時卻是用tel=數字這樣的。會導致資料庫索引無效。還有，像這種，我們看起來是數學等價的操作，但卻會因為索引的問題導致效率相差很多。

還有這個也是常見誤區。不是使用索引不一定比不使用索引快。按我們平時經驗，資料庫裡面少於100條記錄，或者索引列選擇比不高，例如性別欄位只有男或者女，使用索引反而慢一些。因為資料庫使用索引查詢時需要先在索引裡面做幾次磁碟操作，然後找到記錄位置後還要再回表資料訪問記錄內容。

這條經驗也比較重要，如果我們資料庫裡面有使用外鍵保證資料一致性，那要注意在建了外鍵的列上建索引。因為oracle修改父表記錄時需要到子表檢查修改後記錄是否會違反外鍵約束，不建索引會很耗時。

這一頁想介紹的是oracle一些特性，這是oracle專屬功能。用好這些特性可以在某些特定情境大大最佳化我們程式。

這一條，講的是oracle的rownum和rowid用法。我們平時寫指令碼經常會出現這樣的情境：如果庫表有記錄，則執行a操作，否則執行b操作。有些同學會很自然地用select count(*)來判斷庫表是否有記錄。其實我們可以在後面加上where rownum < 2這樣的限制，這樣做是告訴資料庫只要找到合格記錄就可以結束查詢，不需要統計完所有資料。

Rowid概念前面的ppt頁有，使用rowid訪問記錄是oracle訪問資料最快的方法，因為rowid明確表達了資料在磁碟哪個檔案，哪個資料區塊，哪行記錄。比索引訪問要快，用索引訪問時需要先查出rowid再根據rowid查對應的資料。

還有這個技巧，相信做批量的同學也經常用到。就是使用一些特殊方法讓資料庫不要記錄redo日誌。前面我們介紹過，對資料庫操作都需要記錄redo日誌用於資料庫出現異常時恢複資料，但如果我們確定資料不是太重要，資料庫異常掛掉後不需要恢複，則可以使用這些小技巧減少redo日誌以加快速度。

還有一些其它不成體系的技巧，這裡也介紹一下。

這個是大家比較熟知的，使用綁定變數，可以複用sql執行計畫，減少資料庫cpu消耗。

這個技巧大家應該也知道，就是刪除整個表資料時，盡量考慮使用truncate。因為truncate是直接修改資料庫字典，把庫表佔用資料空間狀態改為未使用。這樣操作是很快的。不過要注意的是truncate不像delete，誤刪後可以復原。Truncate一執行後就會自動認可事務，並且無法閃回查詢。

接下來這一大章想要給大家交流的是關於資料庫執行計畫的一些知識。前面一章介紹的一些最佳化經驗，這一章說的是一個最佳化的通用方法：通過調整執行計畫改善sql運行效率。

首先介紹一下執行計畫的定義。執行計畫就是資料庫執行sql語句的步驟計劃，oracle在執行sql之前會先將複雜sql拆解成一些簡單步驟並依次執行。

同一個sql可以有很多個不同的執行計畫，這些執行計畫效率差別可能很大。

我們怎麼看sql的執行計畫呢，這裡介紹幾個方法：

一個方法是在sqlplus裡面執行set autotrace on，然後再執行sql，執行完畢後sqlplus就會列印sql執行計畫及一些統計資訊。

還有一種，不需要執行sql，只需要執行explain命令，接著就能用sql語句查出它的執行計畫。

還有一種，我比較常用的方法。在plsql develor的執行計畫視窗中運行sql，也可以顯示執行計畫。就像這邊這個圖片一樣。

知道怎麼看執行計畫後，我們還要懂得怎麼分析。

我們看這個簡單的sql的執行計畫，可以看出來，執行計畫是樹型結構，有很多個步驟組成，每個步驟都可能有子結點或者兄弟結點。

我們看執行計畫時需要從上往下看，碰到有子結點先後子結點，如果沒有子結點，則同一層級的兄弟結點中比較靠上的一個，看完再看另一個。

或者我們可以藉助工具來幫我們確定執行計畫執行順序。這個就是plsql裡面看執行計畫的視窗，這裡有一組箭頭，點擊它會依照執行順序選中執行步驟。

我們拿一個具體案例做一下講解：

這個sql作用是從僱員表裡面找出工號小於103的資料，關聯職位表取出職位資訊，關聯部門表取出部門資訊。

它對應的執行計畫是這樣的

我們從上往下看，0 1 2都有子結點，所以先看它們的子結點。一直看到3，3沒有子結點，所以先執行3。從僱員表裡面取出資料。

3執行完後需要執行它的兄弟結點4，4有子結點，所以先執行它的子結點5，用job_id到索引job_id_pk裡面找資料的rowid。5執行完後會根據找到的資料rowid回到職位表裡面找出職位資訊，也就是4這個子節點的操作。

3和4執行完後就要回到2，把資料連線到一起。然後再執行6和7，從部門表裡面取出資料，接著執行1再把部門資料跟前面的僱員與職位表串連結果再合并，最後回到0，返回用戶端。

所以這個執行計畫的執行順序就是3 5 4 2 7 6 1 0

像這個是比較複雜一點的執行計畫，大家有興趣可以自己回去看它的執行順序。答案在這一列。

前面我們說過，同一個sql會有很多不同執行計畫。那資料庫怎麼確定使用哪個執行計畫呢。這就涉及這章要介紹的，最佳化器的概念。

最佳化器是資料庫的一個核心組件，負責將sql根據各種判斷標準轉換成最優的執行計畫。

現在我們用的最佳化器都是cbo，基於成本計算的最佳化器。它會計算每個sql執行步驟的成本，挑選總成本最低的執行計畫。