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
Thanks for Sharing .. Good one
ReplyDeleteThanks for providing such detailed explanation...keep sharing
ReplyDelete