理論實踐：循序漸進理解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()

如何得到系統大致的 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 的資料透檢視：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 進行度量的指標： 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 計算時的不同之處：()

"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 的朋友有一定幫助。

About Me

.........................................................................................................................................................................................................

本文來自於微信公眾號轉載文章，若有侵權，請聯絡小麥苗及時刪除

本文在ITpub（http://blog.itpub.net/26736162）和部落格園(http://www.cnblogs.com/lhrbest)有同步更新

聯絡我請加QQ好友(642808185)，註明新增緣由

.........................................................................................................................................................................................................