理論實踐：循序漸進理解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 可能指的是：

1、OS 級的 User%，Sys%， Idle%

2、DB 所占 OS CPU 資源的 Busy%

3、DB CPU 又可以分為前臺所消耗的 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 CPU time = 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的資源利用情況。

用 DBC PU 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全部處于繁忙狀態的話，一秒鐘內的 DB CPU 就是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 和 DB CPU 則是通過同一個視圖，即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%左右。

其它的 external table 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

明顯的，DB CPU + FG Wait Time < DB Time，只占了71.5%

其它的28.5%被消耗到哪里去了呢？這里其實又隱含著一個 Oracle 如何計算 DB CPU 和? DB Time 的問題。當 CPU 很忙時，如果系統里存在著很多進程，就會發生進程排隊等待 CPU 的現象。在這樣，DB TIME 是把進程排隊等待 CPU 的時間算在內的，而 DB CPU 是不包括這一部分時間。這是造成 DB CPU + FG Wait Time < DB Time 的一個重要原因。如果一個系統 CPU 不忙，這兩者應該就比較接近了。

不要忘了在這個例子中，這是一個 CPU 非常繁忙的系統，而71.5%就是一個信號，它提示著這個系統可能是一個 CPU-Bound 的系統。

4.? IO數據分析

除了 DB CPU、DB Time，或許另一個比較常用的指標應該是 IO 的利用情況。關于 IO 的指標就比較多了，單單在 Load Profile 里面就有5個，在 DB Time 和 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 (http://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 write bytes 的兩倍。這應該是 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 Timed Events 類似下面：

如果去抓取這段時間 DBA_HIST_ACTIVE_SESS_HISTORY 的數據，并轉換為圖表的話，我們會得到更形象的 Top 10 Wait Events。（如何實現這一步可以參考用 Oracle 實現 ASH 的數據透視圖：http://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 進行度量的指標： CPU used by this session。

他剛好有一份 AWR 報告，在這份報告里，出現了嚴重的 CPU used by this session 和 DB CPU 不一致的現象。

下面是這份報告的一些片斷：

再做進一步的歸納：

OS Busy% =1821080/(1821080+5384293) = 25%

Inst CPU% (using DB CPU) = 8934.22*100/ (1821080+5384293)=12%

Inst CPU% (using CPU used by this session) = 418035/ (1821080+5384293) = 6%

用 CPU used by this session 計算出的 CPU 利用率竟然只是用 DB CPU 計算出來的利用通率的一半！

我的第一個反應是在 Jonathan Lewis 網站看到的一篇相關文章，里面提到了 DB CPU 和 CPU used 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 的朋友有一定幫助。