Does DB Time include wait on cpu queue time?-白红宇

DB TIME1=DB CPU2+ Foreground NO-Idle wait time 1：Amount of elapsed time (in microseconds) spent performing Database user-level calls. This does not include the elapsed time spent on instance background processes such as PMON. 2:Amount of CPU time (in microseconds) spent on database user-level calls. This does not include the CPU time spent on instance background processes such as PMON. 10g中引入了基于时间统计信息的调优模型，其核心或者说最值得我们关心的大概是DB TIME和AASC 2项指标。DB TIME是Oracle在数据库层对用户级别(不包括后台进程的CPU和非空闲等待时间)各类操作耗时的一个总计，它直接反映了数据库前台的"工时"。DB TIME是否包含了前台进程在CPU队列上的等待时间呢，换而言之我们的命题是"Does DB TIME equal to DB CPU+Foreground NO-Idle wait time + DB CPU ON QUEUE ?" 让我们来看看以下测试：

                         
[maclean@rh2 ~]$ cat /proc/cpuinfo |grep processor|wc -l
2
/*这是一台双核的pc服务器*/
打开session A:
begin
/* 
first
one busy 
on
cpu */
loop
null
;
end
loop;
end
;
/*死循环调用
NULL
，会尝试独占一个逻辑CPU,没有其他等待事件*/
打开SESSION B:
begin
/* 
second
one busy 
on
cpu */
loop
null
;
end
loop;
end
;
打开SESSION C:
begin
/* third one busy 
on
cpu */
loop
null
;
end
loop;
end
;
打开SESSION D:
SQL> 
exec
dbms_workload_repository.create_snapshot;
PL/SQL 
procedure
successfully completed.
/*手动收集AWR 快照*/
/*等待几分钟，以便收集信息*/
SQL> 
exec
dbms_workload_repository.create_snapshot;
PL/SQL 
procedure
successfully completed.
/*再次手动收集快照*/
        
      

我们来看看AWR报告中的Time Model Statistics: DB TIME为344s,而DB CPU为226s。以上匿名PL/SQL块仅引起CPU争用(不会产生其他前台等待事件)，而这里DB TIME要远大于DB CPU，可见DB TIME=DB CPU+Foreground wait time+Cpu on queue;

本文转自maclean_007 51CTO博客，原文链接:http://blog.51cto.com/maclean/1277534