The Importance of Oracle Database Process CPU Consumption

Posted on 30-Aug-2013 by Craig Shallahamer, craig@orapub.com

Oracle DBAs tend to talk a lot about Oracle wait time but less about Oracle CPU consumption. But think about this: Is part of a user's experience CPU consumption? If so, then CPU consumption is something we need to know about. And something we usually want to minimize! Look at the big-bar chart below.

This was created using the Stori command, get chart bigbar oracle. It shows both the CPU and non-idle wait time for all Oracle processes over the analysis scope snapshot interval.

Starting your Oracle performance analysis at this high-level total-time perspective is perfect for a time based analysis! (more: FAQs | Using Stori | Time Based Analysis)

How does Oracle know the CPU time? Each Oracle process frequently asks the operating system how much CPU time it has consumed. Oracle DBAs can query the CPU consumption from the time model views for a connected session and also for all sessions since the database instance has started. Oracle server process CPU consumption is labeled as DB CPU and Oracle background process CPU consumption is labeled background cpu time. Here is an example:

SQL> select stat_id,stat_name,value
  2  from v$sys_time_model
  3  where stat_name in ('DB CPU','background cpu time');

   STAT_ID STAT_NAME                                    VALUE
---------- -------------------------------------------- -----------
2748282437 DB CPU                                       63689552819
2451517896 background cpu time                             90669532

Asking Stori for CPU consumption will result in a different value. Why? Because v$sys_time_model shows the total CPU consumption since the Oracle database has started. In contrast, a Statspack report and Stori will report the total CPU consumption over the snapshot interval. The result is commonly referred to as a delta value because it shows the difference between the ending value and the beginning value.

Let's ask Stori to get the raw CPU consumption statistics delta value.

How can I help you? get stats systimemodel db%cpu
db cpu, 12689550897

How can I help you? get stats systimemodel background%cpu%
background cpu time, 75689503

Let's ask Stori the CPU consumption over the snapshot interval.

How can I help you? get cpu consumed oracle
12765.24

What? The 12765.24 value does not match the raw CPU consumption delta values. In fact, there is only one value returned! That's because Stori sums the delta DB CPU and background cpu time from v$sys_time_model and converts the native result (which is in micro-seconds) to seconds. Go ahead and check the math: (12689550897+75689503)/1000000

Now let's see this CPU consumption in relation to the non-idle Oracle wait time.

How can I help you? get oracle wait time by class nonidle
io,4358.5107
nonio,2671.060637

The total non-idle Oracle wait time is 7029.6 seconds: (4358.5+2671.1). Now let's see the CPU consumption and non-idle Oracle wait time together graphically.


How can I help you? get chart pie oracle 
work/1377804394_oracle_x_pie.png

And here is the chart!


I like to view time as a performance improving opportunity. So when I see CPU consumption as a large portion of the total time, I see reducing CPU consumption as an opportunity to improve performance!

Thanks for reading and enjoy using Stori!

Craig.

If you have any questions or comments, feel free to email me directly at craig at orapub.com.

How To Quickly Install The Free Statistics Package R Ouch! Log File Switch Checkpoint Incomplete Important Statistical Distributions For Oracle Database Tuning...Really