如何進(jìn)行ORACLE的AWR報告分析

這期內(nèi)容當(dāng)中小編將會給大家?guī)碛嘘P(guān)如何進(jìn)行ORACLE的AWR報告分析，文章內(nèi)容豐富且以專業(yè)的角度為大家分析和敘述，閱讀完這篇文章希望大家可以有所收獲。

1、什么是AWR？

AWR (Automatic Workload Repository) 是自動負(fù)載信息庫的英文縮寫，AWR報告是Oracle 10g以后版本提供的一種性能收集和分析工具，能提供一個時間段內(nèi)整個系統(tǒng)資源使用情況的報告，通過報告可以了解一個系統(tǒng)的整個運行情況，生成的報告包括多個部分。

AWR每小時對v$active_session_history視圖(內(nèi)存中的ASH采集信息，理論為1小時)進(jìn)行采樣一次，并將信息保存到磁盤中，并且保留7天，7天后舊的記錄才會被覆蓋。這些采樣信息被保存在wrh$_active_session_history視圖(寫入AWR庫中的ASH信息，理論為1小時以上)中。而這個采樣頻率（1小時）和保留時間（7天）是可以根據(jù)實際情況進(jìn)行調(diào)整的，這就給DBA們提供了更加有效的系統(tǒng)監(jiān)測工具。 

2、什么情況下會用到AWR？

DBA對數(shù)據(jù)庫運行狀態(tài)及狀況的監(jiān)控了解、測試過程中發(fā)現(xiàn)數(shù)據(jù)庫出現(xiàn)瓶頸但無法定位到具體原因時，可以借用AWR報告進(jìn)行分析定位。

數(shù)據(jù)庫出現(xiàn)性能問題，一般都在三個地方：IO、內(nèi)存、CPU，這三個地方又是息息相關(guān)的。假設(shè)這個三個地方都沒有物理上的故障，當(dāng)IO負(fù)載增大時，肯定需要更多的內(nèi)存來存放，同時也需要CPU花費更多的時間來過濾這些數(shù)據(jù)。相反，CPU時間花費多的話，有可能是解析SQL語句，也可能是過濾太多的數(shù)據(jù)，倒不一定是和IO或內(nèi)存有關(guān)系。

CPU：解析SQL語句，嘗試多個執(zhí)行計劃，最后生成一個數(shù)據(jù)庫認(rèn)為是比較好的執(zhí)行計劃，但不一定是最優(yōu)的。因為關(guān)聯(lián)表太多的時候，數(shù)據(jù)庫并不會窮舉所有的執(zhí)行計劃，這會消耗太多的時間，oracle怎么知道這條數(shù)據(jù)是你要的，另一個就不是你要的呢，這是需要cpu來過濾的。

內(nèi)存：SQL語句和執(zhí)行計劃都需要在內(nèi)存保留一段時間，還有取到的數(shù)據(jù)，根據(jù)LRU算法也會盡量在內(nèi)存中保留，在執(zhí)行SQL語句過程中，各種表之間的連接，排序等操作也要占用內(nèi)存。

IO：如果需要的數(shù)據(jù)不在內(nèi)存中，則需要到磁盤中去取，就會涉及到物理IO了，還有表之間的連接數(shù)據(jù)太多，以及排序等操作內(nèi)存放不下的時候，需要用到臨時表空間，也會消耗物理io了。

這里說明下，ORACLE分配的內(nèi)存中PGA一般只占20%，對于專用服務(wù)器模式，每次執(zhí)行SQL語句、表數(shù)據(jù)的運算等操作，都在PGA中進(jìn)行的，也就是說只能用ORACL分配內(nèi)存的20%左右，如果多個用戶都執(zhí)行多表關(guān)聯(lián)，而且表數(shù)據(jù)又多，再加上關(guān)聯(lián)不當(dāng)?shù)脑?，?nèi)存就成為瓶頸了，所以優(yōu)化SQL很重要的一點就是，減少邏輯讀和物理讀。

3、如何生成awr報告？

第一步，登錄ORACLE數(shù)據(jù)庫服務(wù)器，記住當(dāng)前目錄或者切換至AWR想要保存的目錄；

第二步，SQLplus 用戶名/密碼@服務(wù)連接名，連接oracle數(shù)據(jù)庫實例，如下圖所示；

第三步，執(zhí)行@?/rdbms/admin/awrrpt;，會出現(xiàn)提示，

   可以生成以下幾種類型AWR報告，大部分情況下都是生成本實例的AWR報告

    @?/rdbms/admin/awrrpt;  本實例AWR包括

    @?/rdbms/admin/awrrpti; RAC中選擇實例號

    @?/rdbms/admin/awrddrpt; AWR 比對報告

    @?/RDBMS/admin/awrgrpt; RAC全局AWR報告

輸入生成AWR報告的格式是html的，如下圖所示：

輸入開始值與結(jié)束值：（輸入天數(shù)后會列出，snap值）

輸入AWR報告的名稱：名稱自定義 回車后就開始自動生產(chǎn)AWR報告，如下圖所示：

結(jié)束后就可以去指定目錄下照AWR報告文件，文件為 test_awr.lst，如下圖所示：

4、分析AWR報告

AWR報告內(nèi)容很豐富這里選其中一小部分來講解，分析AWR報告前先了解一下Oracle的硬解析和軟解析，首先說一下Oracle對SQL的處理過程。當(dāng)你發(fā)出一條SQL語句交付Oracle，在執(zhí)行和獲取結(jié)果前Oracle對此SQL將進(jìn)行幾個步驟的處理過程：

　　1、語法檢查(syntax check)

　　檢查此SQL的拼寫是否語法。

　　2、語義檢查(semantic check)

　　諸如檢查SQL語句中的訪問對象是否存在及該用戶是否具備相應(yīng)的權(quán)限。

　　3、對SQL語句進(jìn)行解析(prase)

　　利用內(nèi)部算法對SQL進(jìn)行解析，生成解析樹(parse tree)及執(zhí)行計劃(execution plan)。

　　4、執(zhí)行SQL，返回結(jié)果(execute and return)

　　其中，軟、硬解析就發(fā)生在第三個過程里。

　　Oracle利用內(nèi)部的hash算法來取得該SQL的hash值，然后在library cache里查找是否存在該hash值；

　　假設(shè)存在，則將此SQL與cache中的進(jìn)行比較；

　　假設(shè)“相同”，就將利用已有的解析樹與執(zhí)行計劃，而省略了優(yōu)化器的相關(guān)工作。這也就是軟解析的過程。

　　當(dāng)然，如果上面的2個假設(shè)中任有一個不成立，那么優(yōu)化器都將進(jìn)行創(chuàng)建解析樹、生成執(zhí)行計劃的動作。這個過程就叫硬解析。

　　創(chuàng)建解析樹、生成執(zhí)行計劃對于SQL的執(zhí)行來說是開銷昂貴的動作，所以，應(yīng)當(dāng)極力避免硬解析，盡量使用軟解析。

打開AWR報告頭如下圖所示：

顯示SGA中每個區(qū)域的大小，可用來與初始參數(shù)值比較。

shared pool主要包括library cache和dictionary cache。library cache用來存儲最近解析（或編譯）后SQL、PL/SQL和Java classes等。library cache用來存儲最近引用的數(shù)據(jù)字典。發(fā)生在library cache或dictionary cache的cache miss代價要比發(fā)生在buffer cache的代價高得多，因此shared pool的設(shè)置要確保最近使用的數(shù)據(jù)都能被cache。

上圖包含了Oracle關(guān)鍵指標(biāo)的內(nèi)存命中率及其它數(shù)據(jù)庫實例操作的效率，其中Buffer Hit Ratio 也稱Cache Hit Ratio，Library Hit ratio也稱Library Cache Hit ratio。同Load Profile一節(jié)相同，這一節(jié)也沒有所謂“正確”的值，而只能根據(jù)應(yīng)用的特點判斷是否合適。在一個使用直接讀執(zhí)行大型并行查詢的DSS環(huán)境，20%的Buffer Hit Ratio是可以接受的，而這個值對于一個OLTP系統(tǒng)是完全不能接受的。根據(jù)Oracle的經(jīng)驗，對于OLTPT系統(tǒng)，Buffer Hit Ratio理想應(yīng)該在90%以上。

Buffer Nowait表示在內(nèi)存獲得數(shù)據(jù)的未等待比例，在緩沖區(qū)中獲取Buffer的未等待比率，Buffer Nowait的這個值一般需要大于99%。否則可能存在爭用，可以在后面的等待事件中進(jìn)一步確認(rèn)。

Buffer Hit表示進(jìn)程從內(nèi)存中找到數(shù)據(jù)塊的比率，監(jiān)視這個值是否發(fā)生重大變化比這個值本身更重要。對于一般的OLTP系統(tǒng)，如果此值低于80%，應(yīng)該給數(shù)據(jù)庫分配更多的內(nèi)存。數(shù)據(jù)塊在數(shù)據(jù)緩沖區(qū)中的命中率，通常應(yīng)在95%以上。否則，小于95%，需要調(diào)整重要的參數(shù)，小于90%可能是要加db_cache_size。一個高的命中率，不一定代表這個系統(tǒng)的性能是最優(yōu)的，比如大量的非選擇性的索引被頻繁訪問，就會造成命中率很高的假相（大量的db file sequential read），但是一個比較低的命中率，一般就會對這個系統(tǒng)的性能產(chǎn)生影響，需要調(diào)整。命中率的突變，往往是一個不好的信息。如果命中率突然增大，可以檢查TOP buffer get SQL，查看導(dǎo)致大量邏輯讀的語句和索引，如果命中率突然減小，可以檢查TOP physical reads SQL，檢查產(chǎn)生大量物理讀的語句，主要是那些沒有使用索引或者索引被刪除的。

Redo NoWait表示在LOG緩沖區(qū)獲得BUFFER的未等待比例。如果太低（可參考90%閾值），考慮增加LOG BUFFER。當(dāng)redo buffer達(dá)到1M時，就需要寫到redo log文件，所以一般當(dāng)redo buffer設(shè)置超過1M，不太可能存在等待buffer空間分配的情況。當(dāng)前，一般設(shè)置為2M的redo buffer，對于內(nèi)存總量來說，應(yīng)該不是一個太大的值。

Library Hit：表示Oracle從Library Cache中檢索到一個解析過的SQL或PL/SQL語句的比率，當(dāng)應(yīng)用程序調(diào)用SQL或存儲過程時，Oracle檢查Library Cache確定是否存在解析過的版本，如果存在，Oracle立即執(zhí)行語句；如果不存在，Oracle解析此語句，并在Library Cache中為它分配共享SQL區(qū)。低的library hit ratio會導(dǎo)致過多的解析，增加CPU消耗，降低性能。如果library hit ratio低于90%，可能需要調(diào)大shared pool區(qū)。STATEMENT在共享區(qū)的命中率，通常應(yīng)該保持在95%以上，否則需要要考慮：加大共享池；使用綁定變量；修改cursor_sharing等參數(shù)。

Latch Hit：Latch是一種保護(hù)內(nèi)存結(jié)構(gòu)的鎖，可以認(rèn)為是SERVER進(jìn)程獲取訪問內(nèi)存數(shù)據(jù)結(jié)構(gòu)的許可。要確保Latch Hit>99%，否則意味著Shared Pool latch爭用，可能由于未共享的SQL，或者Library Cache太小，可使用綁定變更或調(diào)大Shared Pool解決。要確保>99%，否則存在嚴(yán)重的性能問題。當(dāng)該值出現(xiàn)問題的時候，我們可以借助后面的等待時間和latch分析來查找解決問題。

Parse CPU to Parse Elapsd：解析實際運行時間/(解析實際運行時間+解析中等待資源時間)，越高越好。計算公式為：Parse CPU to Parse Elapsd %= 100*(parse time cpu / parse time elapsed)。即：解析實際運行時間/(解析實際運行時間+解析中等待資源時間)。如果該比率為100%，意味著CPU等待時間為0，沒有任何等待。

Non-Parse CPU ：SQL實際運行時間/(SQL實際運行時間+SQL解析時間)，太低表示解析消耗時間過多。計算公式為：% Non-Parse CPU =round(100*1-PARSE_CPU/TOT_CPU),2)。如果這個值比較小，表示解析消耗的CPU時間過多。與PARSE_CPU相比，如果TOT_CPU很高，這個比值將接近100%，這是很好的，說明計算機(jī)執(zhí)行的大部分工作是執(zhí)行查詢的工作，而不是分析查詢的工作。

Execute to Parse：是語句執(zhí)行與分析的比例，如果要SQL重用率高，則這個比例會很高。該值越高表示一次解析后被重復(fù)執(zhí)行的次數(shù)越多。計算公式為：Execute to Parse =100 * (1 - Parses/Executions)。本例中，差不多每execution 5次需要一次parse。所以如果系統(tǒng)Parses > Executions，就可能出現(xiàn)該比率小于0的情況。該值<0通常說明shared pool設(shè)置或者語句效率存在問題，造成反復(fù)解析，reparse可能較嚴(yán)重,或者是可能同snapshot有關(guān)，通常說明數(shù)據(jù)庫性能存在問題。

In-memory Sort：在內(nèi)存中排序的比率，如果過低說明有大量的排序在臨時表空間中進(jìn)行?？紤]調(diào)大PGA(10g)。如果低于95%，可以通過適當(dāng)調(diào)大初始化參數(shù)PGA_AGGREGATE_TARGET或者SORT_AREA_SIZE來解決，注意這兩個參數(shù)設(shè)置作用的范圍時不同的，SORT_AREA_SIZE是針對每個session設(shè)置的，PGA_AGGREGATE_TARGET則時針對所有的sesion的。

Soft Parse：軟解析的百分比（softs/softs+hards），近似當(dāng)作SQL在共享區(qū)的命中率，太低則需要調(diào)整應(yīng)用使用綁定變量。 SQL在共享區(qū)的命中率，小于<95%,需要考慮綁定，如果低于80%，那么就可以認(rèn)為SQL基本沒有被重用。

TOP5 time Event是AWR報告概要的最后一節(jié)，顯示了系統(tǒng)中最嚴(yán)重的5個等待，按所占等待時間的比例倒序列示。一個性能良好的系統(tǒng)，CPU Time應(yīng)該在TOP 5的前面，否則說明你的系統(tǒng)大部分時間都用在等待上。當(dāng)我們調(diào)優(yōu)時，總希望觀察到最顯著的效果，因此應(yīng)當(dāng)從這里入手確定下一步做什么。例如‘buffer busy wait’是較嚴(yán)重的等待事件，應(yīng)當(dāng)繼續(xù)研究報告中Buffer Wait和File/Tablespace IO區(qū)的內(nèi)容，識別哪些文件導(dǎo)致了問題。如果最嚴(yán)重的等待事件是I/O事件，應(yīng)當(dāng)研究按物理讀排序的SQL語句區(qū)以識別哪些語句在執(zhí)行大量I/O，并研究Tablespace和I/O區(qū)觀察較慢響應(yīng)時間的文件。如果有較高的LATCH等待，就需要察看詳細(xì)的LATCH統(tǒng)計識別哪些LATCH產(chǎn)生的問題。

在這里，log file parallel write是相對比較多的等待，占用了7%的CPU時間。通常借助AWR報告尋找耗時比較長或資源使用比較高的SQL語句，按各種資源分別列出對資源消耗最嚴(yán)重的SQL語句，并顯示它們所占統(tǒng)計期內(nèi)全部資源的比例，這給出我們調(diào)優(yōu)指南。例如在一個系統(tǒng)中，CPU資源是系統(tǒng)性能瓶頸所在，那么優(yōu)化buffer gets最多的SQL語句將獲得最大效果。在一個I/O等待是最嚴(yán)重事件的系統(tǒng)中，調(diào)優(yōu)的目標(biāo)應(yīng)該是physical IO最多的SQL語句。

這里列出了耗時比較長的SQL，從高到低排序TOP100，在AWR報告中點擊SQL ID連接即可跳轉(zhuǎn)到詳細(xì)的SQL語句的地方。

上述就是小編為大家分享的如何進(jìn)行ORACLE的AWR報告分析了，如果剛好有類似的疑惑，不妨參照上述分析進(jìn)行理解。如果想知道更多相關(guān)知識，歡迎關(guān)注億速云行業(yè)資訊頻道。