Does DB CPU Statistic include CPU Time consumed by Oracle background processes?

Does DB CPU Statistic include CPU Time consumed by Oracle background processes? 

When analyzing AWR reports or when troubleshooting performance related issues, all DBAs care about DB CPU statistic. If the CPU utilization of server where the database is hosted is really high, we need to check DB CPU statistic over a period of time. 

We all know that in an Oracle database, there are some background processes e.g. DBWR, LGWR, CKPT, ARCn.... These processes also require some CPU resource to perform their operations. So, a question came to my mind that if DB CPU statistics also include CPU time consumed by Oracle background processes? If not, where CPU time consumed by Oracle background processes is recorded? 

To answer these question, I did a little test on my test database on my system. 

I checked the DB CPU statistic value for background processes DBW0 and LGWR and I found that this parameter values was always zero. 

SQL> alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss';

Session altered.

SQL> select startup_time from v$instance;

STARTUP_TIME

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

08-12-2015 22:53:49

SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP

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

09-DEC-15 12.10.30.759355 AM -05:00

SQL> select a.username,a.program,b.stat_name,b.value from v$session a, v$sess_time_model b

  2  where a.sid=b.sid and (a.program like '%DBW%' or a.program like '%LGW%' )

  3  and lower(b.stat_name) like '%db cpu%' order by a.program;

USERNAME                       PROGRAM                                          STAT_NAME                                     VALUE

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

                               oracle@edndr1p1.us.oracle.com (DBW0)             DB CPU                                            0

                               oracle@edndr1p1.us.oracle.com (LGWR)             DB CPU                                            0

Then I performed a checkpoint to ensure some activity by these processes and again checked statistic value for these processes. 

SQL> alter system checkpoint;

System altered.

SQL>  select a.username,a.program,b.stat_name,b.value from v$session a, v$sess_time_model b

  2   where a.sid=b.sid and (a.program like '%DBW%' or a.program like '%LGW%' )

  3   and lower(b.stat_name) like '%db cpu%' order by a.program;

USERNAME                       PROGRAM                                          STAT_NAME                                     VALUE

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

                               oracle@edndr1p1.us.oracle.com (DBW0)             DB CPU                                            0

                               oracle@edndr1p1.us.oracle.com (LGWR)             DB CPU                                            0

Now it is clear, that DB CPU statistic does not include CPU time consumed by Oracle background processes. But if DB CPU does not include CPU time for background processes, then where it is recorded? Somewhere it should be recorded. So, to answer this question, I performed same test again but this time, I checked all CPU related statistics for these background processes. Here is the result - 

SQL> select startup_time from v$instance;

STARTUP_TIME

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

08-12-2015 22:53:49

SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP

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

09-DEC-15 12.18.02.265650 AM -05:00

SQL> select a.username,a.program,b.stat_name,b.value from v$session a, v$sess_time_model b

where a.sid=b.sid and (a.program like '%DBW%' or a.program like '%LGW%') and lower(b.stat_name) like '%cpu%' order by a.program;

  2

USERNAME                       PROGRAM                                          STAT_NAME                                     VALUE

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

                               oracle@edndr1p1.us.oracle.com (DBW0)             DB CPU                                            0

                               oracle@edndr1p1.us.oracle.com (DBW0)             background cpu time                         6098073

                               oracle@edndr1p1.us.oracle.com (DBW0)             RMAN cpu time (backup/restore)                    0

                               oracle@edndr1p1.us.oracle.com (LGWR)             background cpu time                         9618538

                               oracle@edndr1p1.us.oracle.com (LGWR)             RMAN cpu time (backup/restore)                    0

                               oracle@edndr1p1.us.oracle.com (LGWR)             DB CPU                                            0

6 rows selected.

SQL> alter system checkpoint;

System altered.

SQL> select a.username,a.program,b.stat_name,b.value from v$session a, v$sess_time_model b

where a.sid=b.sid and (a.program like '%DBW%' or a.program like '%LGW%') and lower(b.stat_name) like '%cpu%' order by a.program;

  2

USERNAME                       PROGRAM                                          STAT_NAME                                     VALUE

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

                               oracle@edndr1p1.us.oracle.com (DBW0)             DB CPU                                            0

                               oracle@edndr1p1.us.oracle.com (DBW0)             background cpu time                         6115070

                               oracle@edndr1p1.us.oracle.com (DBW0)             RMAN cpu time (backup/restore)                    0

                               oracle@edndr1p1.us.oracle.com (LGWR)             background cpu time                         9656532

                               oracle@edndr1p1.us.oracle.com (LGWR)             RMAN cpu time (backup/restore)                    0

                               oracle@edndr1p1.us.oracle.com (LGWR)             DB CPU                                            0

6 rows selected.

Again, I checked DB CPU statistic value for a user session (non-background process session). - 

SQL>  select a.username,a.program,b.stat_name,b.value from v$session a, v$sess_time_model b

  2  where a.sid=b.sid and a.sid=16 and b.stat_name='DB CPU';

USERNAME                       PROGRAM                                          STAT_NAME                                     VALUE

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

SYS                            sqlplus@edndr1p1.us.oracle.com (TNS V1-V3)       DB CPU                                        72988

Did some activity by user session and check DB CPU value again for this session - 

SQL> conn / as sysdba

Connected.

SQL> select distinct sid from v$mystat;

       SID

----------

        16

SQL> create table mytable as select * from dba_objects;

Table created.

SQL>  select a.username,a.program,b.stat_name,b.value from v$session a, v$sess_time_model b

  2   where a.sid=b.sid and a.sid=16 and b.stat_name='DB CPU';

USERNAME                       PROGRAM                                          STAT_NAME                                     VALUE

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

SYS                            sqlplus@edndr1p1.us.oracle.com (TNS V1-V3)       DB CPU                                      9542548

The DB CPU statistic value for user session (foreground process) is non-zero and increasing over a period of time.

We can see above that we have another statistic named "background cpu time" which is increasing. This is where CPU time consumed by Oracle background processes is recorded. 

So, we can conclude that DB CPU statistic value only includes CPU time which is consumed by foreground processes. It doesn't include CPU time consumed by Oracle background processes. For Oracle background processes, it is recorded in "background cpu time" statistic.

If we need to calculate total CPU consumption by an Oracle database over a period of time, we should add both these statistics values.

Total CPU consumption by database = DB CPU + background CPU time

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