從一個小程式指令碼看DB Time

 

從Oracle 10g開始，Oracle資料庫開始進入效能度量收集、自診斷、智慧調整階段。應該說，就資料庫本質特性而言，9i奠定了基礎結構，之後一直到11g，Oracle的體系結構就沒有發生本質性的變化。

各種Oracle10g新特性中，筆者認為比較吸引人的特性是ASMM和AWR基礎資訊庫。ASMM（Automatic Shared Memory Managment）讓DBA從設定多大的記憶體池的糾結問題中解放出來。而AWR就是Statspack的“轉正”版本，為調優人員發揮作用奠定基礎。

AWR（Automatic Workload Repository）建立在Oracle內部的效能度量基礎上。在Oracle執行過程中，我們可以透過很多檢視來檢視資料庫的“real time”情況。例如v$session可以看到資料庫會話情況，v$sql和v$sqlarea記錄了當前shared pool中快取SQL語句和執行計劃情況，v$sysstat記錄了系統啟動至今的諸多等待事件資訊。而AWR就是以特定的間隔頻率（預設為一小時），將其記錄儲存為一系列的Snapshot，從而便於日後診斷的工具。

使用AWR最方便最快捷的方法就是使用Oracle提供的AWR報告生成介面，它是我們可以找到的效能分析、對比報告。應該看到，雖然AWR提供了資料庫特定時間區間詳細的資料情況，但是很多個性化的分析需求還是不能滿足的。

應該看到，AWR本質上是建立在對效能資料的週期性收集上。對我們而言，AWR基礎庫是一個非常寶貴的資訊資源，用好這個資源，可以幫助我們以更好的方式進行效能分析。一些自定義的工具，也是基於AWR庫的基礎資訊。

但是，AWR報告的生成過程是透過呼叫資料庫自身加密儲存過程實現的。很多細節和資料選擇規律，都是對我們封閉的。當我們進行資料選取時候，要注意指標的含義和度量，避免選擇錯誤。

1、DB Time

問題起源自筆者想進行的一個分析，希望將一週內每個小時（或者每幾個）的DB Time抽取出來，形成一個關於DB Time變化趨勢的圖形，從而檢視資料庫應用負載變化趨勢。

在AWR報告分析中，DB Time是首先需要分析的指標。Elapse Time和DB Time也是AWR初學者需要看的第一個重要分析情況。

所謂Elapse Time，就表示生成AWR報告過程中，兩個Snapshot映象之間的時間差。這個值體現的是效能基準，就是告訴分析人員，選擇分析的期間是多長時間。

DB Time的概念應該說是很多的。在AWR中的DB Time表示的前臺服務程式呼叫消耗時間，也就是user-level呼叫。資料庫的消耗包括兩個層面，一個是前臺應用消耗，就是我們連線的應用，比如應用程式訪問的消耗。而後臺消耗，就是後臺程式background process的工作消耗。DB Time應該說是我們分析應用效能消耗的重要考量指標。

但是，單純的看待DB Time是不行的。因為資料庫並行的存在，多個user process是可以並行的。DB Time在計量所有Process度量的時候，是要和CPU的個數相匹配。比較常用的公式是DB Time和Elapse Time * CPU_Count相對比。如果DB Time接近乘積值，就表示資料庫應用負載（注意是應用負載）很高。反之，就表示資料庫比較空閒。

2、第一版指令碼

回到問題本身。直接使用AWR報告，肯定不能實現只分析DB Time的目的。所以一個思路是挖掘（mine）AWR基礎資訊庫，從裡面抽取出來。

AWR庫檢視是有特徵的，代表是以dba_hist_作為字首。資料庫實時的效能檢視，在AWR庫中的對應物基本是同名的。比如我們常用的v$sysstat，對應在AWR庫裡面就是dba_hist_sysstat。

自定義AWR程式指令碼，都需要圍繞檢視dba_hist_snapshot。這個檢視記錄了每次進行AWR Snapshot生成的資訊。應該說，這個檢視是AWR的核心。DB Time資訊從哪裡找呢？

首先想到的是v$sysstat檢視，裡面的stat_name以key-value方式記錄了很多度量。其中有一個專案就是DB time。

於是，第一版指令碼生成了。

select snap_id, snap_time, value, v, (value-v) from(

select snap_id, snap_time, value , (lag(value) over (order by snap_id)) v from

(select sp.snap_id, to_char(begin_interval_time,'yyyy-mm-dd hh24:mi:ss') snap_time, value

from dba_hist_snapshot sp, dba_hist_sysstat sy

where sp.snap_id=sy.snap_id

  and sp.instance_number=sy.instance_number

  and sp.begin_interval_time>=to_timestamp('2013-11-09 00:00:00','YYYY-MM-DD hh24:mi:ss')

  and sp.begin_interval_time<to_timestamp('2013-11-15 00:00:00','YYYY-MM-DD hh24:mi:ss')

  and sy.stat_name='DB time'

  --and mod(sp.snap_id,2)=0

order by sp.snap_id));

透過begin_interval_time來定位選取時間片段的範圍，透過stat_name確定選取的度量名稱。注意，度量中的value記錄的是一個累計值，而非期間值。所以使用前後snapshot之間相減的方法進行求差。

整理結果片段如下：

SNAP_ID	SNAP_TIME	VALUE	V	(VALUE-V)/60/1000000
6967	2013/11/9 1:00	740280500	739899753	380747
6968	2013/11/9 2:00	740766773	740280500	486273
6969	2013/11/9 3:00	741324347	740766773	557574
6970	2013/11/9 4:00	741700063	741324347	375716
6971	2013/11/9 5:00	742080734	741700063	380671

似乎存在問題。筆者的系統是一個典型的OLAP系統，夜間從2點-3點進行ETL動作。這個時間段的DB Time最多是可以理解。其他時候訪問量很少，而且負載不會很高。

結果的時間片中，時間分佈比較均勻。雖然在高負載時間段中有起伏，但是起伏幅度不夠。

另一個層面，就是度量。根據官方資料介紹，DB Time對應的是microsecond，也就是百萬分之一秒。無論如何高峰時間也不能對應上這個取值。

之後，筆者選取了一個小時的snapshot，生成AWR正式報告。看到提取的時間。

	Snap Id	Snap Time	Sessions	Cursors/Session
Begin Snap:	7111	15-11月-13 02:00:36	50	4.0
End Snap:	7112	15-11月-13 03:00:40	60	3.4
Elapsed:		60.07 (mins)
DB Time:		17.03 (mins)

一小時內執行的DB time為17分鐘。而對應的snapshot資訊很難對應到17分鐘。

SNAP_ID	SNAP_TIME	VALUE	V	(VALUE-V)
7111	2013/11/15 1:00	797983510
7112	2013/11/15 2:00	798476477	797983510	492967

3、第二個指令碼

問題可能出現的是兩個方面，一個是筆者選取的檢視位置有問題。另一個筆者對指標取值的分析解析錯誤。

後者可能性不大。雖然AWR解析資料庫是在後臺加密情況下完成，但是特殊的解析演算法沒有過多的意義。

檢視位置問題，只有一種可能：就是AWR選擇這個DB Time而非v$sysstat中的DB Time。

在度量Oracle過程中，方法論也有很多流派過程。從最早的Hit Rate，到現在的Event Based，相同的資料庫度量概念，曾經被賦予不同的度量含義。

Oracle曾經提出過一個system time model的概念，基於檢視sys_time_model。筆者猜想是不是AWR選取的是這個模型裡面取值呢？

SQL> desc dba_hist_sys_time_model

Name            Type         Nullable Default Comments

--------------- ------------ -------- ------- --------

SNAP_ID         NUMBER                                

DBID            NUMBER                                

INSTANCE_NUMBER NUMBER                                 

STAT_ID         NUMBER                                

STAT_NAME       VARCHAR2(64)                          

VALUE           NUMBER       Y                        

修改的第二個版本指令碼。

select snap_id, snap_time, value, v, (value-v)/60/1000000 from(

select snap_id, snap_time, value , (lag(value) over (order by snap_id)) v from

(select sp.snap_id, to_char(begin_interval_time,'yyyy-mm-dd hh24:mi:ss') snap_time, value

from dba_hist_snapshot sp, dba_hist_sys_time_model sy

where sp.snap_id=sy.snap_id

  and sp.instance_number=sy.instance_number

  and sp.snap_id in (7111, 7112)

  and sy.stat_name='DB time' 

order by sp.snap_id));

結果：

SNAP_ID	SNAP_TIME	VALUE	V	(VALUE-V)/60/1000000
7111	2013/11/15 1:00	3.24849E+11
7112	2013/11/15 2:00	3.25871E+11	3.24849E+11	17.02656

出現期望的17.02分鐘。對應的轉換公式也是百萬分之一秒轉換。

4、結論

問題解決了。有幾個方面需要額外說明：

首先，使用db_hist_sysstat不適應的情況，目前只發現在DB Time中存在。其他經常統計的指標，如redo size、hard parse (count)之類的，依然可以使用這個檢視。

另外，db_hist_sysstat中的DB Time，筆者認為是一個累計時間，也就是前後臺程式累積的度量情況。而非我們目前已經接受的前臺應用時間。