Oracle AWR報告及統計資料之DB Time說明

轉自：

在Oracle　Database　10g中引入了DB time指標，其實這並不算是新概念。

先摘錄一些官方的說明（Oracle Database Performance Tuning Guide 10g Release 2 (10.2)    B14211-01）

DB Time

Amount of elapsed time (in microseconds) spent performing Database user-level calls. This does not include the time spent on instance background processes such as PMON.

The most important of the time model statistics is DB time. This statistics represents the total time spent in database calls and is a indicator of the total instance workload. It is calculated by aggregating the CPU and wait times of all sessions not waiting on idle wait events (non-idle user sessions). DB time is measured cumulatively from the time that the instance was started.

Because DB time it is calculated by combining the times from all non-idle user sessions, it is possible that the DB time can exceed the actual time elapsed since the instance started up. For example, a instance that has been running for 30 minutes could have four active user sessions whose cumulative DB time is
approximately 120 minutes.
/*記住：如果只有一個cpu,就不可能4個session同時處於no-idle，所以下面這個假設只能在大於4cpu的伺服器上成立。

For example, a instance that has been running for 30 minutes could have four active user sessions whose cumulative DB time is approximately 120 minutes.
*/

By reducing DB time, the database server is able to support more user requests using the same resources, which increases throughput.

   
   
總結出來就是db time= cpu time + wait time（不包含空閒等待） （非後臺程式）
說白了就是db time就是記錄的伺服器花在資料庫運算(非後臺程式)和等待(非空閒等待)上的時間


列出下面這兩個來做解釋：

Report A:
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 4610 24-Jul-08 22:00:54 68 19.1
End Snap: 4612 24-Jul-08 23:00:25 17 1.7
Elapsed: 59.51 (mins)
DB Time: 466.37 (mins)

Report B:
Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 3098 13-Nov-07 21:00:37 39 13.6
End Snap: 3102 13-Nov-07 22:00:15 40 16.4
Elapsed: 59.63 (mins)
DB Time: 19.49 (mins)

伺服器是AIX的系統，4個雙核cpu,共8個核:

/sbin> bindprocessor -q
The available processors are: 0 1 2 3 4 5 6 7

先說Report A,在snapshot間隔中，總共約60分鐘，cpu就共有60*8=480分鐘，DB time為466.37分鐘，則：
cpu花費了466.37分鐘在處理Oralce非空閒等待和運算上(比方邏輯讀)
也就是說cpu有 466.37/480*100% 花費在處理Oracle的操作上，這還不包括後臺程式

看Report B，總共約60分鐘，cpu有 19.49/480*100% 花費在處理Oracle的操作上
很顯然，2中伺服器的平均負載很低。
從awr report的Elapsed time和DB Time就能大概瞭解db的負載