2016-09-13 09:51 來源：喜訊財經網 作者:admin8 點擊:75次
1、什麼是AWR？
AWR (Automatic Workload Repository) 是自動負載信息庫的英文縮寫，AWR報告是Oracle 10g以後版本提供的一種性能收集和分析工具，能提供一個時間段內整個系統資源使用情況的報告，通過報告可以了解一個系統的整個運行情況，生成的報告包括多個部分。
AWR每小時對v$active_session_history視圖(內存中的ASH採集信息，理論為1小時)進行採樣一次，並將信息保存到磁碟中，並且保留7天，7天後舊的記錄才會被覆蓋。這些採樣信息被保存在wrh$_active_session_history視圖(寫入AWR庫中的ASH信息，理論為1小時以上)中。而這個採樣頻率（1小時）和保留時間（7天）是可以根據實際情況進行調整的，這就給DBA們提供了更加有效的系統監測工具。
2、什麼情況下會用到AWR？
DBA對資料庫運行狀態及狀況的監控了解、測試過程中發現資料庫出現瓶頸但無法定位到具體原因時，可以借用AWR報告進行分析定位。
資料庫出現性能問題，一般都在三個地方：IO、內存、CPU，這三個地方又是息息相關的。假設這個三個地方都沒有物理上的故障，當IO負載增大時，肯定需要更多的內存來存放，同時也需要CPU花費更多的時間來過濾這些數據。相反，CPU時間花費多的話，有可能是解析SQL語句，也可能是過濾太多的數據，倒不一定是和IO或內存有關係。
CPU：解析SQL語句，嘗試多個執行計劃，最後生成一個資料庫認為是比較好的執行計劃，但不一定是最優的。因為關聯表太多的時候，資料庫並不會窮舉所有的執行計劃，這會消耗太多的時間，oracle怎麼知道這條數據是你要的，另一個就不是你要的呢，這是需要cpu來過濾的。
內存：SQL語句和執行計劃都需要在內存保留一段時間，還有取到的數據，根據LRU算法也會儘量在內存中保留，在執行SQL語句過程中，各種表之間的連接，排序等操作也要佔用內存。
IO：如果需要的數據不在內存中，則需要到磁碟中去取，就會涉及到物理IO了，還有表之間的連接數據太多，以及排序等操作內存放不下的時候，需要用到臨時表空間，也會消耗物理io了。
這裡說明下，ORACLE分配的內存中PGA一般只佔20%，對於專用伺服器模式，每次執行SQL語句、表數據的運算等操作，都在PGA中進行的，也就是說只能用ORACL分配內存的20%左右，如果多個用戶都執行多表關聯，而且表數據又多，再加上關聯不當的話，內存就成為瓶頸了，所以優化SQL很重要的一點就是，減少邏輯讀和物理讀。
3、如何生成awr報告？
第一步，登錄ORACLE資料庫伺服器，記住當前目錄或者切換至AWR想要保存的目錄；
第二步，SQLplus 用戶名/密碼@服務連接名，連接oracle資料庫實例，如下圖所示；
第三步，執行@?/rdbms/admin/awrrpt;，會出現提示，
可以生成以下幾種類型AWR報告，大部分情況下都是生成本實例的AWR報告
@?/rdbms/admin/awrrpt; 本實例AWR包括
@?/rdbms/admin/awrrpti; RAC中選擇實例號
@?/rdbms/admin/awrddrpt; AWR 比對報告
@?/RDBMS/admin/awrgrpt; RAC全局AWR報告
輸入生成AWR報告的格式是html的，如下圖所示：
輸入天數： 根據實際情況輸入（如1，代表當天，如果2，代表今天和昨天，以此往前推）如下圖所示：
輸入開始值與結束值：（輸入天數後會列出，snap值）
輸入AWR報告的名稱：名稱自定義 回車後就開始自動生產AWR報告，如下圖所示：
這裡說明一下快照節點可以手工創建，根據實際情況執行如下命令：
exec dbms_workload_repository.create_snapshot;就可以手工創建一個快照。
結束後就可以去指定目錄下照AWR報告文件，文件為 test_awr.lst，如下圖所示：
修改擴展名為HTML，下載到Windows平臺即可查看，即可用IE打開AWR報告，如下圖所示：
4、分析AWR報告
AWR報告內容很豐富這裡選其中一小部分來講解，分析AWR報告前先了解一下Oracle的硬解析和軟解析，首先說一下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的執行來說是開銷昂貴的動作，所以，應當極力避免硬解析，儘量使用軟解析。
打開AWR報告頭如下圖所示：
Elapsed快照監控時間：如果為了診斷特定時段性能問題則Elapsed不宜過長15分鐘~2、3個小時。如果是看全天負載那麼可以長一些，最常見是60分鐘後者120分鐘。
DB Time：不包括Oracle後臺進程消耗的時間，如果DB Time遠遠小於Elapsed時間，說明資料庫比較空閒。
DB Time= 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），說明系統壓力非常小。
但是對於批量系統，資料庫的工作負載總是集中在一段時間內，如果快照周期不在這一段時間內，或者快照周期跨度太長而包含了大量的資料庫空閒時間，所得出的分析結果是沒有意義的，這也說明選擇分析時間段很關鍵，要選擇能夠代表性能問題的時間段。
顯示SGA中每個區域的大小，可用來與初始參數值比較。
shared pool主要包括library cache和dictionary cache。library cache用來存儲最近解析（或編譯）後SQL、PL/SQL和Java classes等。library cache用來存儲最近引用的數據字典。發生在library cache或dictionary cache的cache miss代價要比發生在buffer cache的代價高得多，因此shared pool的設置要確保最近使用的數據都能被cache。
顯示資料庫負載概況，將之與基線數據比較才具有更多的意義，如果每秒或每事務的負載變化不大，說明應用運行比較穩定。單個的報告數據只說明應用的負載情況，絕大多數據並沒有一個所謂「正確」的值，然而Logons大於每秒1~2個、Hard parses大於每秒100、全部parses超過每秒300表明可能有爭用問題。
Redo size：每秒產生的日誌大小(單位字節)，可標誌數據變更頻率, 資料庫任務的繁重與否。
Logical reads：每秒/每事務邏輯讀的塊數.平決每秒產生的邏輯讀的block數。Logical Reads= Consistent Gets + DB Block Gets；
Block changes：每秒/每事務修改的塊數；
Physical reads：每秒/每事務物理讀的塊數；
Physical writes：每秒/每事務物理寫的塊數；
User calls：每秒/每事務用戶call次數；
Parses：SQL解析的次數.每秒解析次數，包括fast parse，soft parse和hard parse三種數量的綜合。 軟解析每秒超過300次意味著你的"應用程式"效率不高，調整session_cursor_cache。在這裡，fast parse指的是直接在PGA中命中的情況（設置了session_cached_cursors=n）；soft parse是指在shared pool中命中的情形；hard parse則是指都不命中的情況。
Hard parses：其中硬解析的次數，硬解析太多，說明SQL重用率不高。每秒產生的硬解析次數, 每秒超過100次，就可能說明你綁定使用的不好，也可能是共享池設置不合理。這時候可以啟用參數cursor_sharing=similar|force，該參數默認值為exact。但該參數設置為similar時，存在bug，可能導致執行計劃的不優。
Sorts：每秒/每事務的排序次數；
Logons：每秒/每事務登錄的次數；
Executes：每秒/每事務SQL執行次數；
Transactions：每秒事務數.每秒產生的事務數，反映資料庫任務繁重與否。
Blocks changed per Read：表示邏輯讀用於修改數據塊的比例.在每一次邏輯讀中更改的塊的百分比。
Recursive Call：遞歸調用佔所有操作的比率.遞歸調用的百分比，如果有很多PL/SQL，那麼這個值就會比較高。
Rollback per transaction：每事務的回滾率.看回滾率是不是很高，因為回滾很耗資源 ,如果回滾率過高,可能說明你的資料庫經歷了太多的無效操作 ,過多的回滾可能還會帶來Undo Block的競爭 該參數計算公式如下:
Round(User rollbacks / (user commits + user rollbacks) ,4)* 100% 。
Rows per Sort：每次排序的行數
上圖包含了Oracle關鍵指標的內存命中率及其它資料庫實例操作的效率，其中Buffer Hit Ratio 也稱Cache Hit Ratio，Library Hit ratio也稱Library Cache Hit ratio。同Load Profile一節相同，這一節也沒有所謂「正確」的值，而只能根據應用的特點判斷是否合適。在一個使用直接讀執行大型並行查詢的DSS環境，20%的Buffer Hit Ratio是可以接受的，而這個值對於一個OLTP系統是完全不能接受的。根據Oracle的經驗，對於OLTPT系統，Buffer Hit Ratio理想應該在90%以上。
Buffer Nowait表示在內存獲得數據的未等待比例，在緩衝區中獲取Buffer的未等待比率，Buffer Nowait的這個值一般需要大於99%。否則可能存在爭用，可以在後面的等待事件中進一步確認。
Buffer Hit表示進程從內存中找到數據塊的比率，監視這個值是否發生重大變化比這個值本身更重要。對於一般的OLTP系統，如果此值低於80%，應該給資料庫分配更多的內存。數據塊在數據緩衝區中的命中率，通常應在95%以上。否則，小於95%，需要調整重要的參數，小於90%可能是要加db_cache_size。一個高的命中率，不一定代表這個系統的性能是最優的，比如大量的非選擇性的索引被頻繁訪問，就會造成命中率很高的假相（大量的db file sequential read），但是一個比較低的命中率，一般就會對這個系統的性能產生影響，需要調整。命中率的突變，往往是一個不好的信息。如果命中率突然增大，可以檢查TOP buffer get SQL，查看導致大量邏輯讀的語句和索引，如果命中率突然減小，可以檢查TOP physical reads SQL，檢查產生大量物理讀的語句，主要是那些沒有使用索引或者索引被刪除的。
Redo NoWait表示在LOG緩衝區獲得BUFFER的未等待比例。如果太低（可參考90%閾值），考慮增加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區。STATEMENT在共享區的命中率，通常應該保持在95%以上，否則需要要考慮：加大共享池；使用綁定變量；修改cursor_sharing等參數。
Latch Hit：Latch是一種保護內存結構的鎖，可以認為是SERVER進程獲取訪問內存數據結構的許可。要確保Latch Hit>99%，否則意味著Shared Pool latch爭用，可能由於未共享的SQL，或者Library Cache太小，可使用綁定變更或調大Shared Pool解決。要確保>99%，否則存在嚴重的性能問題。當該值出現問題的時候，我們可以藉助後面的等待時間和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%，這是很好的，說明計算機執行的大部分工作是執行查詢的工作，而不是分析查詢的工作。
Execute to Parse：是語句執行與分析的比例，如果要SQL重用率高，則這個比例會很高。該值越高表示一次解析後被重複執行的次數越多。計算公式為：Execute to Parse =100 * (1 - Parses/Executions)。本例中，差不多每execution 5次需要一次parse。所以如果系統Parses > Executions，就可能出現該比率小於0的情況。該值<0通常說明shared pool設置或者語句效率存在問題，造成反覆解析，reparse可能較嚴重,或者是可能同snapshot有關，通常說明資料庫性能存在問題。
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基本沒有被重用。
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%。這是一個受觀察之間持續時間影響的統計數字。可以期望它隨觀察之間的時間長度增大而增大。
通過ORACLE的實例有效性統計數據，我們可以獲得大概的印象，然而並不能由此確定數據運行的性能。當前性能問題的確定，主要還是依靠下面的等待事件來確認。我們可以這樣理解兩部分的內容，hit統計幫助發現和預測一些系統將要產生的性能問題，由此可以做到未雨綢繆。而wait事件，就是表明當前資料庫已經出現了性能問題需要解決，是亡羊補牢的性質。
TOP5 time Event是AWR報告概要的最後一節，顯示了系統中最嚴重的5個等待，按所佔等待時間的比例倒序列示。一個性能良好的系統，CPU Time應該在TOP 5的前面，否則說明你的系統大部分時間都用在等待上。當我們調優時，總希望觀察到最顯著的效果，因此應當從這裡入手確定下一步做什麼。例如『buffer busy wait』是較嚴重的等待事件，應當繼續研究報告中Buffer Wait和File/Tablespace IO區的內容，識別哪些文件導致了問題。如果最嚴重的等待事件是I/O事件，應當研究按物理讀排序的SQL語句區以識別哪些語句在執行大量I/O，並研究Tablespace和I/O區觀察較慢響應時間的文件。如果有較高的LATCH等待，就需要察看詳細的LATCH統計識別哪些LATCH產生的問題。
在這裡，log file parallel write是相對比較多的等待，佔用了7%的CPU時間。通常藉助AWR報告尋找耗時比較長或資源使用比較高的SQL語句，按各種資源分別列出對資源消耗最嚴重的SQL語句，並顯示它們所佔統計期內全部資源的比例，這給出我們調優指南。例如在一個系統中，CPU資源是系統性能瓶頸所在，那麼優化buffer gets最多的SQL語句將獲得最大效果。在一個I/O等待是最嚴重事件的系統中，調優的目標應該是physical IO最多的SQL語句。
這裡列出了耗時比較長的SQL，從高到低排序TOP100，在AWR報告中點擊SQL ID連接即可跳轉到詳細的SQL語句的地方。
這裡其實和雲智慧透視寶的一個功能很像，就是在應用請求信息中，透視寶可以抓取到MYSQL資料庫的每個SQL語句的耗時，而且是實時的。這點比AWR報告要方便很多，不用設置快照節點實時查看。
這個地方是根據CPU time 排序的TOP 100的SQL語句，同樣點擊SQL ID中的連接可以展開詳細的SQL語句。
AWR報告的內容很豐富，本文的內容希望能起到一個拋磚引玉的作用，大家可以動手實際操作一下，對AWR進一步的研究學習。