What Elapsed Time, DB Time and DB CPU represent in AWR report and how to calculate Wait time (Non-Idle)

What are Elapsed Time, DB Time and DB CPU and how to calculate Wait Time

Time is a very important factor when we look opportunities for tuning database performance. The most important time parameters when we look into AWR reports or V$ views like v$sys_time_model views are DB Time, DB CPU and Elapsed Time. So, we must understand what are the meaning of these time parameters and what are their relationships with each other.

Elapsed Time - Elapsed time is the time what a user experiences when running a query. When looking into AWR report, the Elapsed Time is the wall clock time of duration for which AWR report has been generated. For example, if we generate AWR report for 1 hour then Elapsed Time in AWR report will be 60 mins.

DB CPU - DB CPU is the CPU consumption by all Oracle server processes/foreground processes during snapshot interval time. The name DB CPU has been taken from statistic name which is found in the view v$sys_time_model.

SQL> select stat_name, value from v$sys_time_model where stat_name='DB CPU'
  2  /

STAT_NAME                           VALUE
------------------------------ ----------
DB CPU                          974752790


If we look at any AWR report, we will find DB CPU statistic value in "Time Model Statistics" Section. The value reported in the AWR report represents total CPU consumption by all Oracle server processes during the snapshot interval time. This statistic value is converted into seconds in AWR report.

DB Time - DB time is a statistic which represents CPU time consumed by all Oracle processes over a period of time plus non-idle wait time. The name DB Time which we see in AWR report has been actually derived from v$views like v$sys_time_model and v$sess_time_model. 

SQL>  select stat_name, value from v$sys_time_model where stat_name='DB time';

STAT_NAME                           VALUE

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

DB time                        1.1922E+10

If we look into any AWR report, we will find DB Time in "Time Model Statistics" section of AWR report. The value reported in the AWR report for this parameter represents the sum of DB CPU and non-idle wait time over a period of snaphot interval.

How to calculate Wait Time (Non-Idle) -

When any Oracle process is not consuming CPU, it will be paused. This time, when a process is not consuming CPU is termed as wait time. The wait time can also be categorized in idle wait time and non-idle wait time. If a process is waiting for something to complete, then it is considered as non-idle wait time. There are lots of idle and non-idle wait events in Oracle database. So, as a DBA, to improve the database performance, we need to focus on non-idle wait events. In the following screenshot, we can see that in my system there are 94 idle wait events.

So the DB time is sum of DB CPU and Non-Idle wait time.

DB Time = DB CPU + Non-Idle wait time

so, Non-Idle wait time = DB Time - DB CPU