Oracle AWR運(yùn)行日志分析工具詳解

1、 Oracle AWR運(yùn)行日志分析工具詳解 在Oracle數(shù)據(jù)庫學(xué)習(xí)和使用中，遇到性能問題，首要的步驟就是導(dǎo)出AWR分析報(bào)告，AWR是Oracle的一個(gè)腳本工具，通過周期性快照記錄下當(dāng)時(shí)的所有運(yùn)行數(shù)據(jù)，數(shù)據(jù)庫管理員可以導(dǎo)出其中一部分?jǐn)?shù)據(jù)進(jìn)行分析，從而找出來哪些腳本導(dǎo)致了目前的數(shù)據(jù)性能問題。一般情況下，安裝完Oracle服務(wù)端后，默認(rèn)都會(huì)有這個(gè)腳本工具(在數(shù)據(jù)庫管理員HOME目錄下)，進(jìn)入到sqlplus，然后直接運(yùn)行awrrpt腳本，按照提示操作就可以完成日志導(dǎo)出，導(dǎo)出的格式包括txt格式和html格式兩種。 AWR是Oracle 10g版本推出的新特性， 全稱叫Automatic Workl

2、oadRepository自動(dòng)負(fù)載信息庫。AWR是通過對(duì)比兩次快照收集到的統(tǒng)計(jì)信息，來生成報(bào)表數(shù)據(jù)，生成的報(bào)表包括多個(gè)部分。下面將對(duì)AWR報(bào)告的關(guān)鍵部分做詳細(xì)的講解。Workload Repository Report DBTime不包括Oracle后臺(tái)進(jìn)程消耗的時(shí)間。如果DB Time遠(yuǎn)遠(yuǎn)小于Elapsed時(shí)間，說明數(shù)據(jù)庫比較空閑。DBTime= CPU time + Wait time(不包含空閑等待)，DB time就是記錄的服務(wù)器花在數(shù)據(jù)庫運(yùn)算(非后臺(tái)進(jìn)程)和等待(非空閑等待)上的時(shí)間:DB time = CPU time + all of nonidle wait event tim

3、e。 如圖，在79分鐘里(其間收集了3次快照數(shù)據(jù))，數(shù)據(jù)庫耗時(shí)11分鐘，RDA數(shù)據(jù)中顯示系統(tǒng)有8個(gè)邏輯CPU(4個(gè)物理CPU)，平均每個(gè)CPU耗時(shí)1.4分鐘，CPU利用率只有大約2%(1.4/ 79)，說明系統(tǒng)壓力非常小。 如上例子，假設(shè)服務(wù)器是AIX的系統(tǒng)，4個(gè)雙核CPU(共8個(gè)核)，Report A在Snapshot間隔中總共約60分鐘，CPU就共有60*8=480分鐘，DB time為466.37分鐘。說明CPU花費(fèi)了466.37分鐘在處理Oralce非空閑等待和運(yùn)算上(比方邏輯讀)，也就是說CPU有466.37/480*100%花費(fèi)在處理Oracle的操作上，這還不包括后臺(tái)進(jìn)程。Rep

4、ort B總共約60分鐘，CPU有19.49/480*100%花費(fèi)在處理Oracle的操作上，很顯然平均負(fù)載很低。 從AWR Report的Elapsed time和DB Time就能大概了解DB的負(fù)載情況。 對(duì)于批量系統(tǒng)，數(shù)據(jù)庫的工作負(fù)載總是集中在一段時(shí)間內(nèi)。如果快照周期不在這一段時(shí)間內(nèi)，或者快照周期跨度太長而包含了大量的數(shù)據(jù)庫空閑時(shí)間，所得出的分析結(jié)果是沒有意義的。這也說明選擇分析時(shí)間段很關(guān)鍵，要選擇能夠代表性能問題的時(shí)間段。Cache Sizes 顯示SGA中每個(gè)區(qū)域的大小(在AMM改變它們之后)，可用來與初始參數(shù)值比較。 Shared pool主要包括Library cache和Dic

5、tionary cache。Library cache用來存儲(chǔ)最近解析(或編譯)后SQL、PL/SQL和Java Classes等。Dictionary cache用來存儲(chǔ)最近引用的數(shù)據(jù)字典。發(fā)生在Library cache或Dictionary cache的cache miss代價(jià)要比發(fā)生在Buffer cache的代價(jià)高得多。因此Shared pool的設(shè)置要確保最近使用的數(shù)據(jù)都能被cache。Load Profile 顯示數(shù)據(jù)庫負(fù)載概況，將之與基線數(shù)據(jù)比較才具有更多的意義，如果每秒或每事務(wù)的負(fù)載變化不大，說明應(yīng)用運(yùn)行比較穩(wěn)定。單個(gè)的報(bào)告數(shù)據(jù)只說明應(yīng)用的負(fù)載情況，絕大多數(shù)據(jù)并沒有一個(gè)所謂“

6、正確”的值，然而Logons大于每秒12個(gè)、Hard parses大于每秒100、全部parses超過每秒300表明可能有爭用問題。Redo size：每秒產(chǎn)生的日志大小(單位字節(jié))，可標(biāo)志數(shù)據(jù)變更頻率,數(shù)據(jù)庫任務(wù)的繁重與否。Logical reads：代表每秒/每事務(wù)邏輯讀的塊數(shù)。平?jīng)Q每秒產(chǎn)生的邏輯讀的block數(shù)。Block changes：代表每秒/每事務(wù)修改的塊數(shù)。Physical reads：代表每秒/每事務(wù)物理讀的塊數(shù)。Physical writes：代表每秒/每事務(wù)物理寫的塊數(shù)。User calls：每秒/每事務(wù)用戶call次數(shù)。Parses：SQL解析的次數(shù)，包括fast pa

7、rse，soft parse和hard parse三種數(shù)量的綜合。軟解析每秒超過300次意味著你的應(yīng)用程序效率不高，fast parse指的是直接在PGA中命中的情況，soft parse是指在shared pool中命中的情形；hard parse則是指都不命中的情況。Hard parses：其中硬解析的次數(shù)，硬解析太多，說明SQL重用率不高。Sorts：每秒/每事務(wù)的排序次數(shù)Logons：每秒/每事務(wù)登錄的次數(shù)Executes：每秒/每事務(wù)SQL執(zhí)行次數(shù)Transactions：每秒事務(wù)數(shù).每秒產(chǎn)生的事務(wù)數(shù)，反映數(shù)據(jù)庫任務(wù)繁重與否。Blocks changed per Read：表示邏輯讀

8、用于修改數(shù)據(jù)塊的比例.在每一次邏輯讀中更改的塊的百分比。Recursive Call：遞歸調(diào)用占所有操作的比率.遞歸調(diào)用的百分比，如果有很多PL/SQL，那么這個(gè)值就會(huì)比較高。Rollback per transaction：每事務(wù)的回滾率.看回滾率是不是很高，因?yàn)榛貪L很耗資源,如果回滾率過高,可能說明你的數(shù)據(jù)庫經(jīng)歷了太多的無效操作,過多的回滾可能還會(huì)帶來Undo Block的競爭。Rows per Sort：每次排序的行數(shù)Oracle的硬解析和軟解析 提到軟解析(soft prase)和硬解析(hard prase)，就不能不說一下Oracle對(duì)sql的處理過程。當(dāng)你發(fā)出一條sql語句交付O

9、racle，在執(zhí)行和獲取結(jié)果前，Oracle對(duì)此SQL將進(jìn)行幾個(gè)步驟的處理過程。1、語法檢查(syntax check):檢查此sql的拼寫是否語法。2、語義檢查(semantic check):諸如檢查sql語句中的訪問對(duì)象是否存在及該用戶是否具備相應(yīng)的權(quán)限。3、對(duì)sql語句進(jìn)行解析(prase):利用內(nèi)部算法對(duì)sql進(jìn)行解析，生成解析樹(parse tree)及執(zhí)行計(jì)劃(execution plan)。4、執(zhí)行sql，返回結(jié)果(execute and return)。 其中軟、硬解析就發(fā)生在第三個(gè)過程里。Oracle利用內(nèi)部的hash算法來取得該sql的hash值，然后在library c

10、ache里查找是否存在該hash值。假設(shè)存在，則將此sql與cache中的進(jìn)行比較；假設(shè)“相同”，就將利用已有的解析樹與執(zhí)行計(jì)劃，而省略了優(yōu)化器的相關(guān)工作。這也就是軟解析的過程。如果上面的2個(gè)假設(shè)中任有一個(gè)不成立，那么優(yōu)化器都將進(jìn)行創(chuàng)建解析樹、生成執(zhí)行計(jì)劃的動(dòng)作。這個(gè)過程就叫硬解析。創(chuàng)建解析樹、生成執(zhí)行計(jì)劃對(duì)于sql的執(zhí)行來說是開銷昂貴的動(dòng)作，所以，應(yīng)當(dāng)極力避免硬解析，盡量使用軟解析。Instance Efficiency Percentages 包含了Oracle關(guān)鍵指標(biāo)的內(nèi)存命中率及其它數(shù)據(jù)庫實(shí)例操作的效率(OLAP是主要應(yīng)用數(shù)據(jù)倉庫系統(tǒng)，OLTP是一般的項(xiàng)目開發(fā)用到的基本的、日常的事務(wù)處

11、理，比如數(shù)據(jù)庫記錄的增、刪、改、查)。其中Buffer Hit Ratio也稱Cache Hit Ratio，Library Hit ratio也稱Library Cache Hit ratio。同Load Profile一節(jié)相同，這一節(jié)也沒有所謂“正確”的值，而只能根據(jù)應(yīng)用的特點(diǎn)判斷是否合適。 在一個(gè)使用直接讀執(zhí)行大型并行查詢的DSS環(huán)境，20%的Buffer Hit Ratio是可以接受的，而這個(gè)值對(duì)于一個(gè)OLTP系統(tǒng)是完全不能接受的。根據(jù)Oracle的經(jīng)驗(yàn)，對(duì)于OLTP系統(tǒng)，Buffer Hit Ratio理想應(yīng)該在90%以上。 Buffer Nowait表示在內(nèi)存獲得數(shù)據(jù)的未等待比例。

12、在緩沖區(qū)中獲取Buffer的未等待比率。Buffer Nowait的這個(gè)值一般需要大于99%。否則可能存在爭用，可以在后面的等待事件中進(jìn)一步確認(rèn)。 Buffer Hit表示進(jìn)程從內(nèi)存中找到數(shù)據(jù)塊的比率，監(jiān)視這個(gè)值是否發(fā)生重大變化比這個(gè)值本身更重要。對(duì)于一般的OLTP系統(tǒng)，如果此值低于80%，應(yīng)該給數(shù)據(jù)庫分配更多的內(nèi)存。數(shù)據(jù)塊在數(shù)據(jù)緩沖區(qū)中的命中率，通常應(yīng)在95%以上。 但是，一個(gè)高的命中率，不一定代表這個(gè)系統(tǒng)的性能是最優(yōu)的，比如大量的非選擇性的索引被頻繁訪問，就會(huì)造成命中率很高的假相，但是一個(gè)比較低的命中率，一般就會(huì)對(duì)這個(gè)系統(tǒng)的性能產(chǎn)生影響，需要調(diào)整。命中率的突變，往往是一個(gè)不好的信息。如果命

13、中率突然增大，可以檢查top buffer get SQL，查看導(dǎo)致大量邏輯讀的語句和索引，如果命中率突然減小，可以檢查top physical reads SQL，檢查產(chǎn)生大量物理讀的語句(主要是那些沒有使用索引或者索引被刪除的)。 Redo NoWait表示在Log緩沖區(qū)獲得Buffer的未等待比例。如果太低可考慮增加Log Buffer。當(dāng)redo buffer達(dá)到1M時(shí)就需要寫到redo log文件，所以一般當(dāng)redo buffer設(shè)置超過1M，不太可能存在等待buffer空間分配的情況。當(dāng)前，一般設(shè)置為2M的redo buffer，對(duì)于內(nèi)存總量來說，應(yīng)該不是一個(gè)太大的值。 Libra

14、ry Hit表示Oracle從Library Cache中檢索到一個(gè)解析過的SQL或PL/SQL語句的比率，當(dāng)應(yīng)用程序調(diào)用SQL或存儲(chǔ)過程時(shí)，Oracle檢查Library Cache確定是否存在解析過的版本，如果存在Oracle立即執(zhí)行語句；如果不存在Oracle解析此語句，并在Library Cache中為它分配共享SQL區(qū)。低的Library Hit Ratio會(huì)導(dǎo)致過多的解析，增加CPU消耗，降低性能。如果Library Hit Ratio低于90%，可能需要調(diào)大Shared pool區(qū)。Latch Hit：Latch是一種保護(hù)內(nèi)存結(jié)構(gòu)的鎖，可以認(rèn)為是Server進(jìn)程獲取訪問內(nèi)存數(shù)據(jù)結(jié)

15、構(gòu)的許可。要確保Latch Hit99%，否則意味著Shared Pool latch爭用，可能由于未共享的SQL，或者Library Cache太小，可使用綁定變更或調(diào)大Shared Pool解決。要確保99%，否則存在嚴(yán)重的性能問題。當(dāng)該值出現(xiàn)問題的時(shí)候，我們可以借助后面的等待時(shí)間和latch分析來查找解決問題。Parse CPU to Parse Elapsd：解析實(shí)際運(yùn)行時(shí)間/(解析實(shí)際運(yùn)行時(shí)間+解析中等待資源時(shí)間)，越高越好。如果該比率為100%，意味著CPU等待時(shí)間為0，沒有任何等待。Non-Parse CPU：SQL實(shí)際運(yùn)行時(shí)間/(SQL實(shí)際運(yùn)行時(shí)間+SQL解析時(shí)間)，太低表示解

16、析消耗時(shí)間過多。如果這個(gè)值比較小，表示解析消耗的CPU時(shí)間過多。Execute to Parse：是語句執(zhí)行與分析的比例，如果要SQL重用率高，則這個(gè)比例會(huì)很高。該值越高表示一次解析后被重復(fù)執(zhí)行的次數(shù)越多。In-memory Sort：在內(nèi)存中排序的比率，如果過低說明有大量的排序在臨時(shí)表空間中進(jìn)行。考慮調(diào)大PGA(10g)。如果低于95%，可以通過適當(dāng)調(diào)大初始化參數(shù)PGA_AGGREGATE_TARGET或者SORT_AREA_SIZE來解決，注意這兩個(gè)參數(shù)設(shè)置作用的范圍時(shí)不同的，SORT_AREA_SIZE是針對(duì)每個(gè)session設(shè)置的，PGA_AGGREGATE_TARGET則時(shí)針對(duì)所有的

17、sesion的。Soft Parse：軟解析的百分比(Softs/Softs+Hards)，近似當(dāng)作sql在共享區(qū)的命中率，太低則需要調(diào)整應(yīng)用使用綁定變量。sql在共享區(qū)的命中率，小于1：執(zhí)行次數(shù)大于1的sql比率，如果此值太小，說明需要在應(yīng)用中更多使用綁定變量，避免過多SQL解析。在一個(gè)趨向于循環(huán)運(yùn)行的系統(tǒng)中，必須認(rèn)真考慮這個(gè)數(shù)字。在這個(gè)循環(huán)系統(tǒng)中，在一天中相對(duì)于另一部分時(shí)間的部分時(shí)間里執(zhí)行了一組不同的SQL語句。在共享池中，在觀察期間將有一組未被執(zhí)行過的SQL語句，這僅僅是因?yàn)橐獔?zhí)行它們的語句在觀察期間沒有運(yùn)行。只有系統(tǒng)連續(xù)運(yùn)行相同的SQL語句組，這個(gè)數(shù)字才會(huì)接近100%。Memory f

18、or SQL w/exec1：執(zhí)行次數(shù)大于1的SQL消耗內(nèi)存的占比。這是與不頻繁使用的SQL語句相比，頻繁使用的SQL語句消耗內(nèi)存多少的一個(gè)度量。這個(gè)數(shù)字將在總體上與% SQL with executions1非常接近，除非有某些查詢?nèi)蝿?wù)消耗的內(nèi)存沒有規(guī)律。在穩(wěn)定狀態(tài)下，總體上會(huì)看見隨著時(shí)間的推移大約有75%85%的共享池被使用。如果Statspack報(bào)表的時(shí)間窗口足夠大到覆蓋所有的周期，執(zhí)行次數(shù)大于一次的SQL語句的百分率應(yīng)該接近于100%。這是一個(gè)受觀察之間持續(xù)時(shí)間影響的統(tǒng)計(jì)數(shù)字?？梢云谕S觀察之間的時(shí)間長度增大而增大。Top 5 Timed Events 通過Oracle的實(shí)例有效性統(tǒng)計(jì)數(shù)據(jù)，我們可以獲得大概的一個(gè)整體印象，然而我們并不能由此來確定數(shù)據(jù)運(yùn)行的性能。當(dāng)前性能問題的確定，我們主要還是依靠下面的等待事件來確認(rèn)。 我們可以這樣理解兩部分的內(nèi)容，Hit統(tǒng)計(jì)幫助我們發(fā)現(xiàn)和預(yù)測(cè)一些系統(tǒng)將要產(chǎn)生的性能問題，由此我們可以做到未雨綢繆。而wait事件，就是表明當(dāng)前數(shù)據(jù)庫已經(jīng)出現(xiàn)了性能問題需要解決，所以是亡羊補(bǔ)牢的性質(zhì)。 上面顯示了系統(tǒng)中最嚴(yán)重的5個(gè)等待，按所占等待時(shí)間的比例倒序列示。當(dāng)我們調(diào)優(yōu)時(shí)，總希望觀察到最顯著的效果，因此應(yīng)當(dāng)從這里入手確定我們下一步做什么。如果Buffer Busy Wait是較嚴(yán)重的等待事件，我們應(yīng)當(dāng)繼續(xù)研究報(bào)告中Buf