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

架構師技術聯盟發表於2018-04-01

640?wx_fmt=png&wxfrom=5&wx_lazy=1


640?wx_fmt=gif&wxfrom=5&wx_lazy=1

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


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


Workload Repository Report 


640?wx_fmt=png


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


640?wx_fmt=png


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


640?wx_fmt=png


      如上例子,假設伺服器是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 timeDB Time就能大概瞭解DB的負載情況。


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


Cache Sizes


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


640?wx_fmt=png


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


Load Profile


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


640?wx_fmt=png

640?wx_fmt=png


  • 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%以上。


640?wx_fmt=png


      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在共享區的命中率,小於<95%,需要考慮繫結,如果低於80%,那麼就可以認為sql基本沒有被重用。


Shared Pool Statistics


640?wx_fmt=png


  • 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事件,就是表明當前資料庫已經出現了效能問題需要解決,所以是亡羊補牢的性質。


640?wx_fmt=png


      上面顯示了系統中最嚴重的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總是列在第一個。


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

640?wx_fmt=png

資料庫專題



溫馨提示:
搜尋“ICT_Architect”“掃一掃”二維碼關注公眾號,點原文連結獲取更多技術資料

640?wx_fmt=png

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

640?wx_fmt=gif

相關文章