詳解Oracle AWR運行日誌分析工具

在Oracle資料庫學習和使用中，遇到性能問題，首要的步驟就是導出AWR分析報告，AWR是Oracle的一個腳本工具，通過周期性快照記錄下當時的所有運行數據，資料庫管理員可以導出其中一部分數據進行分析，從而找出來哪些腳本導致了目前的數據性能問題。一般情況下，安裝完Oracle服務端後，默認都會有這個腳本工具(在資料庫管理員HOME目錄下)，進入到sqlplus，然後直接運行@awrrpt腳本，按照提示操作就可以完成日誌導出，導出的格式包括txt格式和html格式兩種。

AWR是Oracle 10g版本推出的新特性， 全稱叫Automatic Workload Repository自動負載信息庫。AWR是通過對比兩次快照收集到的統計信息，來生成報表數據，生成的報表包括多個部分。下面將對AWR報告的關鍵部分做詳細的講解。

Workload Repository Report

DBTime不包括Oracle後台進程消耗的時間。如果DB Time遠遠小於Elapsed時間，說明資料庫比較空閑。DBTime= CPU time + Wait time(不包含空閑等待)，DB time就是記錄的伺服器花在資料庫運算(非後台進程)和等待(非空閑等待)上的時間:DB time = CPU time + all of nonidle wait event time。

如圖，在79分鐘里(其間收集了3次快照數據)，資料庫耗時11分鐘，RDA數據中顯示系統有8個邏輯CPU(4個物理CPU)，平均每個CPU耗時1.4分鐘，CPU利用率只有大約2%(1.4/ 79)，說明系統壓力非常小。

如上例子，假設伺服器是AIX的系統，4個雙核CPU(共8個核)，Report A在Snapshot間隔中總共約60分鐘，CPU就共有60*8=480分鐘，DB time為466.37分鐘。說明CPU花費了466.37分鐘在處理Oralce非空閑等待和運算上(比方邏輯讀)，也就是說CPU有 466.37/480*100% 花費在處理Oracle的操作上，這還不包括後台進程。Report B總共約60分鐘，CPU有19.49/480*100% 花費在處理Oracle的操作上，很顯然平均負載很低。

從AWR Report的Elapsed time和DB Time就能大概了解DB的負載情況。

對於批量系統，資料庫的工作負載總是集中在一段時間內。如果快照周期不在這一段時間內，或者快照周期跨度太長而包含了大量的資料庫空閑時間，所得出的分析結果是沒有意義的。這也說明選擇分析時間段很關鍵，要選擇能夠代表性能問題的時間段。

Cache Sizes

顯示SGA中每個區域的大小(在AMM改變它們之後)，可用來與初始參數值比較。

Shared pool主要包括Library cache和Dictionary cache。Library cache用來存儲最近解析(或編譯)後SQL、PL/SQL和Java Classes等。Dictionarycache用來存儲最近引用的數據字典。發生在Library cache或Dictionary cache的cache miss代價要比發生在Buffer cache的代價高得多。因此Shared pool的設置要確保最近使用的數據都能被cache。

Load Profile

顯示資料庫負載概況，將之與基線數據比較才具有更多的意義，如果每秒或每事務的負載變化不大，說明應用運行比較穩定。單個的報告數據只說明應用的負載情況，絕大多數據並沒有一個所謂「正確」的值，然而Logons大於每秒1~2個、Hard parses大於每秒100、全部parses超過每秒300表明可能有爭用問題。

Redo size：每秒產生的日誌大小(單位位元組)，可標誌數據變更頻率, 資料庫任務的繁重與否。

Logical reads：代表每秒/每事務邏輯讀的塊數。平決每秒產生的邏輯讀的block數。

Block changes：代表每秒/每事務修改的塊數。

Physical reads：代表每秒/每事務物理讀的塊數。

Physical writes：代表每秒/每事務物理寫的塊數。

User calls：每秒/每事務用戶call次數。

Parses：SQL解析的次數，包括fast parse，soft parse和hard parse三種數量的綜合。 軟解析每秒超過300次意味著你的"應用程序"效率不高，fast parse指的是直接在PGA中命中的情況，soft parse是指在shared pool中命中的情形；hard parse則是指都不命中的情況。

Hard parses：其中硬解析的次數，硬解析太多，說明SQL重用率不高。

Sorts：每秒/每事務的排序次數

Logons：每秒/每事務登錄的次數

Executes：每秒/每事務SQL執行次數

Transactions：每秒事務數.每秒產生的事務數，反映資料庫任務繁重與否。

Blocks changed per Read：表示邏輯讀用於修改數據塊的比例.在每一次邏輯讀中更改的塊的百分比。

Recursive Call：遞歸調用占所有操作的比率.遞歸調用的百分比，如果有很多PL/SQL，那麼這個值就會比較高。

Rollback per transaction：每事務的回滾率.看回滾率是不是很高，因為回滾很耗資源 ,如果回滾率過高,可能說明你的資料庫經歷了太多的無效操作 ,過多的回滾可能還會帶來Undo Block的競爭。

Rows per Sort：每次排序的行數

Oracle的硬解析和軟解析

提到軟解析(soft prase)和硬解析(hard prase)，就不能不說一下Oracle對sql的處理過程。當你發出一條sql語句交付Oracle，在執行和獲取結果前，Oracle對此SQL將進行幾個步驟的處理過程。

1、語法檢查(syntax check):檢查此sql的拼寫是否語法。

2、語義檢查(semantic check):諸如檢查sql語句中的訪問對象是否存在及該用戶是否具備相應的許可權。

3、對sql語句進行解析(prase):利用內部演算法對sql進行解析，生成解析樹(parse tree)及執行計劃(execution plan)。

4、執行sql，返回結果(execute and return)。

其中軟、硬解析就發生在第三個過程里。Oracle利用內部的hash演算法來取得該sql的hash值，然後在library cache里查找是否存在該hash值。

假設存在，則將此sql與cache中的進行比較；

假設「相同」，就將利用已有的解析樹與執行計劃，而省略了優化器的相關工作。這也就是軟解析的過程。

如果上面的2個假設中任有一個不成立，那麼優化器都將進行創建解析樹、生成執行計劃的動作。這個過程就叫硬解析。

創建解析樹、生成執行計劃對於sql的執行來說是開銷昂貴的動作，所以，應當極力避免硬解析，盡量使用軟解析。

Instance Efficiency Percentages

包含了Oracle關鍵指標的內存命中率及其它資料庫實例操作的效率(OLAP是主要應用數據倉庫系統，OLTP是一般的項目開發用到的基本的、日常的事務處理，比如資料庫記錄的增、刪、改、查)。其中Buffer Hit Ratio 也稱Cache Hit Ratio，Library Hit ratio也稱Library Cache Hit ratio。同Load Profile一節相同，這一節也沒有所謂「正確」的值，而只能根據應用的特點判斷是否合適。

在一個使用直接讀執行大型並行查詢的DSS環境，20%的Buffer Hit Ratio是可以接受的，而這個值對於一個OLTP系統是完全不能接受的。根據Oracle的經驗，對於OLTP系統，Buffer Hit Ratio理想應該在90%以上。

Buffer Nowait表示在內存獲得數據的未等待比例。在緩衝區中獲取Buffer的未等待比率。Buffer Nowait的這個值一般需要大於99%。否則可能存在爭用，可以在後面的等待事件中進一步確認。

Buffer Hit表示進程從內存中找到數據塊的比率，監視這個值是否發生重大變化比這個值本身更重要。對於一般的OLTP系統，如果此值低於80%，應該給資料庫分配更多的內存。數據塊在數據緩衝區中的命中率，通常應在95%以上。

但是，一個高的命中率，不一定代表這個系統的性能是最優的，比如大量的非選擇性的索引被頻繁訪問，就會造成命中率很高的假相，但是一個比較低的命中率，一般就會對這個系統的性能產生影響，需要調整。命中率的突變，往往是一個不好的信息。如果命中率突然增大，可以檢查top buffer get SQL，查看導致大量邏輯讀的語句和索引，如果命中率突然減小，可以檢查top physical reads SQL，檢查產生大量物理讀的語句(主要是那些沒有使用索引或者索引被刪除的)。

Redo NoWait表示在Log緩衝區獲得Buffer的未等待比例。如果太低可考慮增加Log Buffer。當redo buffer達到1M時就需要寫到redo log文件，所以一般當redo buffer設置超過1M，不太可能存在等待buffer空間分配的情況。當前，一般設置為2M的redo buffer，對於內存總量來說，應該不是一個太大的值。

Library Hit表示Oracle從Library Cache中檢索到一個解析過的SQL或PL/SQL語句的比率，當應用程序調用SQL或存儲過程時，Oracle檢查Library Cache確定是否存在解析過的版本，如果存在Oracle立即執行語句；如果不存在Oracle解析此語句，並在Library Cache中為它分配共享SQL區。低的Library Hit Ratio會導致過多的解析，增加CPU消耗，降低性能。如果Library Hit Ratio低於90%，可能需要調大Shared pool區。

Latch Hit：Latch是一種保護內存結構的鎖，可以認為是Server進程獲取訪問內存數據結構的許可。要確保Latch Hit>99%，否則意味著Shared Pool latch爭用，可能由於未共享的SQL，或者Library Cache太小，可使用綁定變更或調大Shared Pool解決。要確保>99%，否則存在嚴重的性能問題。當該值出現問題的時候，我們可以藉助後面的等待時間和latch分析來查找解決問題。

Parse CPU to Parse Elapsd：解析實際運行時間/(解析實際運行時間+解析中等待資源時間)，越高越好。如果該比率為100%，意味著CPU等待時間為0，沒有任何等待。

Non-Parse CPU ：SQL實際運行時間/(SQL實際運行時間+SQL解析時間)，太低表示解析消耗時間過多。如果這個值比較小，表示解析消耗的CPU時間過多。

Execute to Parse：是語句執行與分析的比例，如果要SQL重用率高，則這個比例會很高。該值越高表示一次解析後被重複執行的次數越多。

In-memory Sort：在內存中排序的比率，如果過低說明有大量的排序在臨時表空間中進行。考慮調大PGA(10g)。如果低於95%，可以通過適當調大初始化參數PGA_AGGREGATE_TARGET或者SORT_AREA_SIZE來解決，注意這兩個參數設置作用的範圍時不同的，SORT_AREA_SIZE是針對每個session設置的，PGA_AGGREGATE_TARGET則時針對所有的sesion的。

Soft Parse：軟解析的百分比(Softs/Softs+Hards)，近似當作sql在共享區的命中率，太低則需要調整應用使用綁定變數。sql在共享區的命中率，小於

Shared Pool Statistics

Memory Usage %：對於一個已經運行一段時間的資料庫來說，共享池內存使用率，應該穩定在75%-90%間，如果太小，說明Shared Pool有浪費，而如果高於90，說明共享池中有爭用，內存不足。這個數字應該長時間穩定在75%～90%。如果這個百分比太低，表明共享池設置過大，帶來額外的管理上的負擔，從而在某些條件下會導致性能的下降。如果這個百分率太高，會使共享池外部的組件老化，如果SQL語句被再次執行，這將使得SQL語句被硬解析。在一個大小合適的系統中，共享池的使用率將處於75%到略低於90%的範圍內。

SQL with executions>1：執行次數大於1的sql比率，如果此值太小，說明需要在應用中更多使用綁定變數，避免過多SQL解析。在一個趨向於循環運行的系統中，必須認真考慮這個數字。在這個循環系統中，在一天中相對於另一部分時間的部分時間裡執行了一組不同的SQL語句。在共享池中，在觀察期間將有一組未被執行過的SQL語句，這僅僅是因為要執行它們的語句在觀察期間沒有運行。只有系統連續運行相同的SQL語句組，這個數字才會接近100%。

Memory for SQL w/exec>1：執行次數大於1的SQL消耗內存的佔比。這是與不頻繁使用的SQL語句相比，頻繁使用的SQL語句消耗內存多少的一個度量。這個數字將在總體上與% SQL with executions>1非常接近，除非有某些查詢任務消耗的內存沒有規律。在穩定狀態下，總體上會看見隨著時間的推移大約有75%～85%的共享池被使用。如果Statspack報表的時間窗口足夠大到覆蓋所有的周期，執行次數大於一次的SQL語句的百分率應該接近於100%。這是一個受觀察之間持續時間影響的統計數字。可以期望它隨觀察之間的時間長度增大而增大。

Top 5 Timed Events

通過Oracle的實例有效性統計數據，我們可以獲得大概的一個整體印象，然而我們並不能由此來確定數據運行的性能。當前性能問題的確定，我們主要還是依靠下面的等待事件來確認。

我們可以這樣理解兩部分的內容，Hit統計幫助我們發現和預測一些系統將要產生的性能問題，由此我們可以做到未雨綢繆。而wait事件，就是表明當前資料庫已經出現了性能問題需要解決，所以是亡羊補牢的性質。

上面顯示了系統中最嚴重的5個等待，按所佔等待時間的比例倒序列示。當我們調優時，總希望觀察到最顯著的效果，因此應當從這裡入手確定我們下一步做什麼。

如果Buffer Busy Wait是較嚴重的等待事件，我們應當繼續研究報告中Buffer Wait和File/Tablespace IO區的內容，識別哪些文件導致了問題。

如果最嚴重的等待事件是I/O事件，我們應當研究按物理讀排序的SQL語句區以識別哪些語句在執行大量I/O，並研究Tablespace和I/O區觀察較慢響應時間的文件。

如果有較高的LATCH等待，就需要察看詳細的LATCH統計識別哪些LATCH產生的問題。

一個性能良好的系統，CPU Time應該在Top 5的前面，否則說明你的系統大部分時間都用在等待上。在這裡Log file parallel write是相對比較多的等待，佔用了7%的CPU時間。通常，在沒有問題的資料庫中，CPU time總是列在第一個。

好了，作者分享結束了，作者也開始玩微信小程序了，藉此機會測試下小程序在小伙們人群中的普及度。

資料庫專題

求知若渴, 虛心若愚(Stay hungry,Stay foolish)

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