Oracle AWR报告及统计数据之DB Time说明

http://www.eygle.com/digest/2009/02/awr_db_time.html

在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的负载

 

结论：看AWR的时候，可以拿DB time和采样时间间隔做比较，如果DB time高出采样时间间隔很多，说明数据库负载很大

同样，可以拿DB time和DB CPU比较，如果 DB time高出DB CPU很多，说明数据库出现了显而易见的性能问题

因为等待时间过多。