What is DB time in AWR?

AWR中有 DB time这个术语，那么什么是DB time呢？

 

Oracle10gR2 官方文档 给出了详细解释(Oracle10gPerformance Tuning Guide 5.1.1.2 Time Model Statistics)

 

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=DB CPU+ DB waiting time(no-idle time)

 

那么下面我挑一个session来验证一下

 

SQL> select sid,stat_id,stat_name,value/1000000/60 from V$sess_TIME_MODEL where sid=194;

       SID    STAT_ID STAT_NAME                                          VALUE/1000000/60
---------- ---------- -------------------------------------------------- ----------------
       194 3649082374 DB time                                                  33.5681982
       194 2748282437 DB CPU                                                   32.9633455
       194 4157170894 background elapsed time                                           0
       194 2451517896 background cpu time                                               0
       194 4127043053 sequence load elapsed time                                        0
       194 1431595225 parse time elapsed                                         .0005325
       194  372226525 hard parse elapsed time                                  .000498433
       194 2821698184 sql execute elapsed time                                 33.5674656
       194 1990024365 connection management call elapsed time                  .000047933
       194 1824284809 failed parse elapsed time                                         0
       194 4125607023 failed parse (out of shared memory) elapsed time                  0
       194 3138706091 hard parse (sharing criteria) elapsed time                        0
       194  268357648 hard parse (bind mismatch) elapsed time                           0
       194 2643905994 PL/SQL execution elapsed time                              .0000051
       194  290749718 inbound PL/SQL rpc elapsed time                                   0
       194 1311180441 PL/SQL compilation elapsed time                                   0
       194  751169994 Java execution elapsed time                                       0
       194 1159091985 repeated bind elapsed time                                 .0000001
       194 2411117902 RMAN cpu time (backup/restore)                                    0

19 rows selected.

Elapsed: 00:00:01.61

SQL> select (sysdate-logon_time)*24*60 minutes, username,machine,sid,serial#,event,p1,p2,p3
  2  from v$session where sid=194;

   MINUTES USERNAME             MACHINE                     SID    SERIAL# EVENT                     P1    P2         P3
---------- -------------------- -------------------- ---------- ---------- -------------------- ---------- ---------- ----------
34.4833333 WHSUSRGL             NA/BDC-KALIDO001            194      28391 direct path read tem        202      89709          1
                                                                           p

Elapsed: 00:00:01.15

Session 194连接到数据库已经34.4833333分钟，DB time=33.5681982 ， CPU time=32.9633455

 

现在来查询一下ASH，我们知道ASH是每隔1秒钟采样一次

 

SQL> select count(*)
  2         from v$active_session_history ash, v$event_name enm
  3         where ash.event#=enm.event#
  4         and SESSION_ID=194;

  COUNT(*)
----------
        47

 

那么194这个session大概等待了47秒，也就是等待了大约。78333333分钟

 

SQL> select 47/60 from dual;

     47/60
----------
.783333333

 

 

SQL> select 32.9633455+.783333333 from dual;

32.9633455+.783333333
---------------------
           33.7466788

 

DB CPU + 等待时间=33.7466788 ，而 DB time=33.5681982 他们相差不大，基本上可以说明

 

DB time=DB CPU+ DB waiting time(no-idle time)

 

注意：AWR中的DB time等于所有session DB time之和(除去后台进程)

  

SQL> select SESSION_ID,NAME,P1,P2,P3
  2         from v$active_session_history ash, v$event_name enm
  3         where ash.event#=enm.event#
  4         and SESSION_ID=194;

SESSION_ID NAME                                        P1         P2         P3
---------- ----------------------------------- ---------- ---------- ----------
       194 db file sequential read                    202     106634          1
       194 db file sequential read                    202      53541          1
       194 db file sequential read                    202     204387          1
       194 db file sequential read                    202     242316          1
       194 db file sequential read                    202     251848          1
       194 db file sequential read                    202     201689          1
       194 db file scattered read                      45     480505         16
       194 db file scattered read                      44     169145         16
       194 db file scattered read                      45      32489         16
       194 db file scattered read                      44     316585         16
       194 db file scattered read                      46     255817         16
       194 db file scattered read                      47     204105         16
       194 db file scattered read                      44     236633         16
       194 db file scattered read                      46     222761         16
       194 db file scattered read                      44     232969         16
       194 db file scattered read                      44     230489         16
       194 db file scattered read                      44     227673         16
       194 db file scattered read                      45     177785         16
       194 db file scattered read                      44     292121         16
       194 db file scattered read                      45     136041         16
       194 db file scattered read                      28      17177         16
       194 db file scattered read                      47     233513         16
       194 db file scattered read                      44     222233         16
       194 db file scattered read                      46     211289         16
       194 db file scattered read                      47     152889         16
       194 db file scattered read                      45     164921         16
       194 db file scattered read                      47     149609         16
       194 db file scattered read                      44     312873         16
       194 db file scattered read                      45     157289         16
       194 db file scattered read                      45     155225         16
       194 db file sequential read                     50      30400          1
       194 db file sequential read                     43       8763          1
       194 db file scattered read                      36     518441         16
       194 db file scattered read                      37     504992          4
       194 db file scattered read                      38     195257         16
       194 db file scattered read                      37     209776          9
       194 db file scattered read                      41     119561         16
       194 db file scattered read                      39      62041         16
       194 db file scattered read                      41       4765          2
       194 db file scattered read                      38     438857         16
       194 read by other session                       38     335097          1
       194 latch: cache buffers chains         1.3835E+19        122          0
       194 db file sequential read                     27      52122          1
       194 db file sequential read                     41     140105          1
       194 read by other session                       36      97230          1
       194 db file scattered read                      38      17369         16
       194 db file sequential read                     37       1889          1

47 rows selected.

 

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

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

因为等待时间过多。