378
理論實踐：循序漸進理解AWR細致入微分析性能報告
黃凱耀 (Kaya) ACOUG核心會員，高級技術專家
曾經工作於Oracle Real World Database Performance Group，一個隸屬於Oracle公司總部數據庫產品管理的核心團隊。大學及研究生時期專注於Linux應用開發和Linux內核開發工作。
編輯手記：AWR是Oracle數據庫中一個非常重要的診斷工具，通過度量而展現問題，每一個DBA都應當深入理解這其中的知識，本文通過講解和分析，展示AWR分析的過程。
概述：本篇文章重點對 AWR 報告中的 DB Time、DBCPU、IO 等數據進行了說明，可幫助讀者更加清楚的理解這些數據代表的含義，與數據庫的性能表現有何關係。同時通過兩個簡短的例子，實踐如何分析 AWR 報告。
1. AWR 概述
Automatic Workload Repository(AWR) 是10g引入的一個重要組件。在裏麵存貯著近期一段時間內（默認是7天）數據庫活動狀態的詳細信息。
AWR 報告是對 AWR 視圖進行查詢而得到的一份自動生成的報告。可以通過下麵的腳本手工得到一份 AWR 報告。
通過 AWR 和 AWR 報告，DBA 可以容易地獲知最近數據庫的活動狀態，數據庫的各種性能指標的變化趨勢曲線，最近數據庫可能存在的異常，分析數據庫可能存在的性能瓶頸從而對數據庫進行優化。
AWR 報告所有的數據來源於 AWR 視圖，即以 DBA_HIST_開頭的所有係統表，Database Reference 有對所有這些係統表的描述，這應該是 Oracle 官方對 AWR 報告的官方注釋了。而對於如何有效地去分析 AWR 報告，這可能更需要 DBA 經驗的日積月累。
AWR的前身是Statspack，Statspack在10g和11g中也有提供，同時和AWR一起做了同步更新，而且Statspack是公開源代碼的，因此，關於Statspack的資料，還有Statspack的源代碼，都是理解AWR的一個有用的輔助。
本文著重對AWR中的一些要點進行剖析，歡迎一起討論AWR相關的問題。
2. DB CPU - CPU負載分析
如果關注數據庫的性能，那麼當拿到一份 AWR 報告的時候，最想知道的第一件事情可能就是係統資源的利用情況了，而首當其衝的，就是 CPU。
而細分起來，CPU 可能指的是：
-
OS 級的 User%，Sys%， Idle%
-
DB 所占 OS CPU 資源的 Busy%
-
DBCPU 又可以分為前台所消耗的 CPU 和後台所消耗的 CPU
如果數據庫的版本是11g，那麼很幸運的，這些信息在AWR報告中一目了然：
分析上麵的圖片，我們可以得出下麵的結論：
-
OS 級的 User%，Sys%，Idle%：
OS 級的 %User 為75.4，%Sys 為2.8，%Idle 為21.2，所以 %Busy 應該是 100-21.1=78.8。
-
DB所占OSCPU資源的Busy%
DB占了OS CPU資源的69.1，%Busy CPU則可以通過上麵的數據得到：
%Busy CPU = %Total CPU/(%Busy) * 100 = 69.1/78.8 * 100 = 87.69，和報告的87.7相吻合。
如果是10g呢，則需要手工對 Report 裏的一些數據進行計算了。
Host CPU 的結果來源於 DBA_HIST_OSSTAT，AWR 報告裏已經幫忙整出了這段時間內的絕對數據(這裏的時間單位是centi second,也就是1/100秒)
根據上麵的數據，稍加計算分析便可得出下麵的結果。
-
OS級的User%，Sys%， Idle%：
%User = USER_TIME/ (BUSY_TIME+IDLE_TIME)*100 =146355/ (152946+41230)*100 = 75.37
%Sys = SYS_TIME/ (BUSY_TIME+IDLE_TIME)*100=5462/(152946+41230)*100=2.81
%Idle = IDLE_TIME/ (BUSY_TIME+IDLE_TIME)*100=21230/(152946+41230)*100=10.93
值得注意的，這裏已經隱含著這個AWR報告所捕捉的兩個Snapshot之間的時間長短了。有下麵的公式：
BUSY_TIME + IDLE_TIME = ELAPSED_TIME * CPU_COUNT
注意：正確的理解這個公式可以對係統CPU資源的使用及其度量的方式有更深一步的理解。
因此ELAPSED_TIME =(152946+41230)/8/100 = 242.72 seconds
至於DB對CPU的利用情況，這就涉及到10g新引入的一個關於時間統計的視圖V$SYS_TIME_MODEL，簡單而言，Oracle采用了一個統一的時間模型對一些重要的時間指標進行了記錄，具體而言，這些指標包括：
1) Background elapsed time
2) Background CPU time
3) RMAN CPU time (backup/restore)
1) DB time
2) DB CPU
2) Connection management call elapsed time
2) Sequence load elapsed time
2) SQL execute elapsed time
2) Parse time elapsed
3) Hard parse elapsed time
4) Hard parse (sharingcriteria) elapsed time
5) Hard parse (bindmismatch) elapsed time
3) Failed parse elapsed time
4) Failed parse (out of sharedmemory) elapsed time
2) PL/SQL execution elapsed time
2) Inbound PL/SQL RPC elapsed time
2) PL/SQL compilation elapsed time
2) Java execution elapsed time
2) Repeated bind elapsed time
這裏我們關注的隻有和 CPU 相關的兩個： Background CPU time 和 DB CPU。
這兩個值在 AWR 裏麵也有記錄：
Total DB CPU = DB CPU + Background CPUtime = 1305.89 + 35.91 = 1341.8 seconds
Total DB CPU除以總的 BUSY_TIME + IDLE_TIME可得出% Total CPU。
% Total CPU = 1341.8/1941.76 = 69.1%，這剛好與上麵Report的值相吻合。
其實，在 Load Profile 部分，我們也可以看出DB對係統CPU的資源利用情況。
用 DBCPU per Second 除以 CPU Count 就可以得到 DB 在前台所消耗的 CPU% 了。這裏 5.3/8 = 66.25 %比69.1%稍小，說明DB在後台也消耗了大約3%的 CPU，這是不是一個最簡單的方法了呢？
3. DB Time – 進程消耗時間分析
DB CPU 是一個用於衡量 CPU 的使用率的重要指標。假設係統有N個 CPU，那麼如果 CPU全部處於繁忙狀態的話，一秒鍾內的 DBCPU 就是N秒。
如何去表征一個係統的繁忙程度呢？除了利用 CPU 進行計算外，數據庫還會利用其它計算資源，如網絡、硬盤、內存等等，這些對資源的利用同樣可以利用時間進行度量。假設係統有M個 Session 在運行，同一時刻，有的 Session 可能在利用 CPU，有的 Session 可能在訪問硬盤，那麼，在一秒鍾內，所有 Session 的時間加起來就可以表征係統在這一秒內的繁忙程度，一般的，這個和的最大值應該為 M。這其實就是 Oracle 提供的另一個重要指標：DB Time，它用以衡量前端進程所消耗的總時間。
對除 CPU 以外的計算資源的訪問，Oracle 用等待事件進行描述。同樣地，和 CPU 可分為前台消耗 CPU 和後台消耗 CPU一樣，等待事件也可以分為前台等待事件和後台等待事件。
DB Time 一般的應該等於DB CPU + 前台等待事件所消耗時間的總和。等待時間通過 V$SYSTEM_EVENT 視圖進行統計，DB Time 和 DBCPU 則是通過同一個視圖，即V$SYS_TIME_MODEL 進行統計。
Load Profile 一節就有了對 DB Time 的描述：
這個係統的 CPU 個數是8，因此我們可以知道前台進程用了係統 CPU 的7.1/8=88.75%。 DB Time/s 為11.7，可以看出這個係統是 CPU 非常繁忙的。裏麵 CPU 占了7.1，則其它前台等待事件占了11.7 –7.1 = 4.6 Wait Time/s。DB CPU 占 DB Time 的比重呢？ 7.1/11.7= 60.68%
Top 5 Timed Events，或許很多人都對它有所耳聞，按照 CPU/ 等待事件占 DB Time 的比例大小，這裏列出了 Top 5。如果一個工作負載是 CPU 繁忙型的話，那麼在這裏應該可以看到 DB CPU 的影子。
注意到，我們剛剛已經算出了 DB CPU 的 %DB time 為60%左右。
其它的 externaltable read，direct path write， PX Deq: read credit， PXDeq: Slave Session Stats 這些就是占比重40%的等待事件裏的 Top 4了。
回過頭再研究下這個 Top 5 Timed Foreground Events，如果先不看 Load Profile，你能說出這個一個 CPU-Bound 的工作負載嗎？
答案是否定的，要知道係統 CPU 的繁忙程序，還要知道這個 AWR 所基於兩個 Snapshot 的時間間隔，還要知道係統 CPU 的個數。否則，係統可以是一個很 IDLE 的係統呢。記住 CPU 利用率= DB CPU/(CPU_COUNT*Elapsed TIME)。
這個 Top5 給我們的信息隻是這個工作負載應該是並行查詢，從外部表讀取數據，並用 insert append 的方式寫入磁盤，同時，主要時間耗費在 CPU 的運算上。
上麵提到，DB Time 一般的應該等於 DB CPU + 前台等待事件所消耗時間的總和。在下麵有對這三個值的統計：
-
DB CPU = 6474.65
-
DB TIME = 10711.2
-
FG Wait Time = 1182.63
明顯的，DBCPU + FG Wait Time < DB Time，隻占了71.5%
其它的28.5%被消耗到哪裏去了呢？這裏其實又隱含著一個 Oracle 如何計算 DBCPU 和 DB Time 的問題。當 CPU 很忙時，如果係統裏存在著很多進程，就會發生進程排隊等待 CPU 的現象。在這樣，DB TIME 是把進程排隊等待 CPU 的時間算在內的，而 DB CPU 是不包括這一部分時間。這是造成 DB CPU + FG Wait Time < DB Time 的一個重要原因。如果一個係統 CPU 不忙，這兩者應該就比較接近了。
不要忘了在這個例子中，這是一個 CPU 非常繁忙的係統，而71.5%就是一個信號，它提示著這個係統可能是一個 CPU-Bound 的係統。
4. IO數據分析
除了 DBCPU、DB Time，或許另一個比較常用的指標應該是 IO 的利用情況。關於 IO 的指標就比較多了，單單在 Load Profile 裏麵就有5個，在 DBTime 和 DB CPU 的下麵：
這5個指標的值都來自 V$SYSTAT 視圖，分別是：
-
Redo Size: ‘redo size’
-
Logical reads = ’session logical reads’or (’db block gets’ + ‘consistent gets’)
-
Blocks Changes = ‘db block changes’
-
Physical reads = ‘physical reads’
-
Physical writes = ‘physical writes’
具體指標的解釋可以參考 Database Reference (https://docs.oracle.com/cd/B28359_01/server.111/b28320/stats002.htm)
如何得到係統大致的 MBPS(Megabits Per Second) 呢？
MBPS= (Physical reads + Physical writes) *Block_Size = (196,271.4+2.0)*8*1024/1024/1024 = 1533 MB/s
更準確的 MBPS 可以從 Instance Activity Stats 部分獲得。
Physical IO disk bytes = physical read total bytes+ physical write total bytes
值得注意的是這裏 physical write total bytes 大致是 physical writebytes 的兩倍。這應該是 physical write total bytes 統計的是磁盤的 IO，而這裏，我們做了 ASM，normal redundancy，一份數據寫了兩遍的原因。
Load Profile 剩下的部分主要是關於各種執行情況的統計，除了 W/A MB processed 來自 V$PGASTAT（單位其實也是 Byte，不是 MB），其它數據都是來自於 V$SYSSTAT。
-
Blocks Changes: ‘db block changes’
-
User calls: ‘user calls’
-
Parses: ‘parse count (total)’
-
Hard parses: ‘parse count (hard)’
-
Logons: ‘logons cumulative’
-
Executes: ‘execute count’
-
Rollbacks: ‘user rollbacks’
-
Tranasactions: ‘user rollbacks’ + ‘usercommits’
-
W/A MB processed: ‘bytes processed’
一般而言，Hard parses < Parses < Executes < User Calls。
AWR 的一般性介紹我想差不多就這些了，其它部分的介紹借助於一些更具體的 AWR 報告進行分析可能會更加方便和清晰。
5. AWR 報告分析 – 實戰1
構建 DSS 係統的第一步離不開數據加載，通過文本文件加載是最常見的方式，Oracle 提供了外部表加載的方法，即把一個文本文件當成一個正常的表來進行操作，通過類似 insert /*+ append */ into table select from external_table 的方式進行加載。
數據加載是一個 CPU-Bound 的過程，不過是通過什麼工具，external table 也好，sqlldr 也好，imp 也好，impdp 也好。換句話說，如果連數據加載都出現 IO 瓶頸，這個係統的配置就說不過去了。
這個過程的 AWR 報告會是什麼樣子的呢？
先做個一般的假定，從外部表加載數據到一個本地分區表。
Top 5 TimedEvents 類似下麵：
如果去抓取這段時間 DBA_HIST_ACTIVE_SESS_HISTORY 的數據，並轉換為圖表的話，我們會得到更形象的 Top 10 Wait Events。（如何實現這一步可以參考用 Oracle 實現 ASH 的數據透視圖：https://www.cnblogs.com/rootq/archive/2009/09/24/1573200.html）
enq: HV –contention 是什麼東西呢？
在11.2以前，對於分區表的 parallel direct-path load，Oracle 采用的是 brokered load 的方式，即所有的 PX Slaves 共享對每個分區的 high water mark 的訪問，通過輪流持有 high water mark 實現對每個 segment 添加新的 blocks。這種方法對於充分利用 extent 的空間是有幫助的，不過帶來的問題就是對 high water mark 的競爭，也就是這裏的 enq: HV – contention。在執行計劃中，這以 RANDOM LOCAL 標記。下麵是一個例子：
一個好消息是，11.2引入了一種新的方式，叫做 PKEY distribution。在這種方式下，一個特定的分區隻交給一個或多個特定的 PX slave 負責，這種方式不僅減少了對 high water mark 的爭用，而且可以實現 Partition 內更好的壓縮率。
6. AWR報告分析 – 實戰2
有一次跟一個 QQ 上的朋友一起探討了另一個對係統 CPU 進行度量的指標： CPUused by this session。
他剛好有一份 AWR 報告，在這份報告裏，出現了嚴重的 CPU used by this session 和 DB CPU 不一致的現象。
下麵是這份報告的一些片斷：
再做進一步的歸納：
OS Busy% =1821080/(1821080+5384293) = 25%
Inst CPU% (usingDB CPU) = 8934.22*100/ (1821080+5384293)=12%
Inst CPU% (usingCPU used by this session) = 418035/ (1821080+5384293) = 6%
用 CPU used by this session 計算出的 CPU 利用率竟然隻是用 DB CPU 計算出來的利用通率的一半！
我的第一個反應是在 Jonathan Lewis 網站看到的一篇相關文章，裏麵提到了 DB CPU 和 CPUused by this session 計算時的不同之處： (https://jonathanlewis.wordpress.com/2009/05/26/cpu-used/)
“Prior to10g Oracle usually updated time figures at the end of each database call; butfrom 10g there are some views where time is updated more regularly.
The “DB CPU” from v$sess_time_model increases every six seconds, while the “CPU used by this session” from v$sesstat changes only at the end of the test.”
如何驗證這一點呢？
在瀏覽這份報告的 TOP SQL 時，我們發現了下麵的現象：
這是從 SQL ordered by Elapsed Time 截取出來的 Top 3 SQL。TOP 1 的 SQL 用了 DB Time 的30.10%，用了2517s 的 CPU Time。但請注意它的 Executions 的值卻為0。也就是說，這裏的 CPU Time 是還沒有被計算入 CPU used by this session 這個指標裏麵的。
我們再把2517s加回來，看出誤差縮小多少：
(251700+418035)/(1821080+5384293) = 9%
這時和用 DB CPU 計算出來的12%還是有1/4的差距。
從這個例子可以看出，用 DB CPU 度量還是比用 CPU usedby this session 來得準確的。特別在有大查詢在跑的過程中抓的 AWR，這個誤差很有可能會被放大。這是一個有趣的實際例子。
7. 總結
AWR 是分析數據庫運行狀況的利器，將其運用好可幫助 DBA 提早發現數據庫中存在的問題並加以解決。文中主要對 DB CPU、DB Time、IO 等 AWR 報告中的數據進行了分析說明，當然分析AWR報告不能僅限於此，更需要DBA日積月累的經驗。希望本文對想了解 AWR 的朋友有一定幫助。
本文出自數據和雲公眾號，原文鏈接
最後更新：2017-07-17 18:03:45