The Importance of Oracle Database Process CPU Consumption
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!
If you have any questions or comments, feel free to email me directly at craig at orapub.com.
|Why Tuning Oracle Database Works And Modeling It||2011 IOUG Presentation: Unit Of Work Time Based Analysis||How To Use The Oracle ASH time_waited column|