Wednesday, January 27, 2010

Understanding CPU Time as an Oracle Wait event

We have all seen "CPU Time" as a Top 5 wait event at one point or the other when troubleshooting performance issues. From an administrator (DBA/SA) perspective, conventional thinking would be that CPU is a bottleneck.

But what if the stats from the system show that CPU Utilization (% Util and Run queue) are well within thresholds and show plenty of available capacity, but Oracle continues to report CPU time as a Top 5 wait event?

We are also seeing high degree of involuntary context switching (using mpstat in Solaris) or context switches (using vmstat in Linux). Obviously, something is not computing right.

CPU Time could mean that the process is either

On a CPU run queue waiting to be scheduled

Or currently running on a CPU.

Obviously, we are interested in

Minimizing the wait time on the run queue so that the session can run on the CPU as soon as possible. This is determined by the priority of the process.

And once running on the CPU, be allowed to run on the CPU to complete its tasks. The amount of time available for the process on the CPU is defined as the Time Quanta.

"Scheduling is a key concept in computer multitasking, multiprocessing operating system and real-time operating system designs. In modern operating systems, there are typically many more processes running than there are CPUs available to run them. Scheduling refers to the way processes are assigned to run on the available CPUs. This assignment is carried out by software known as a scheduler or is sometimes referred to as a dispatcher"

Understanding how the scheduler shares CPU resources is key to understanding and influencing the wait event "CPU Time".

In any Unix platform, there are processes which take higher priority than others. Labeling a process as higher priority can be done through the implementation of Scheduling classes and with the nice command. Both can have different effects on the process.

An easy method to identify the scheduling class and current priority for a process is to use the ps command. Used with the "-flycae" arguments, it shows both the scheduling class and current priority. However it does not show the CPU time quanta associated with a process.

In the above example, you would be interested in the CLS and PRI column. The above example shows that the oracle background processes as running under the TS Scheduling class with a priority of 24. The higher the number reported in the PRI column, the higher the priority.

The default Scheduler for user processes is TS or Time Share and is common across Solaris and Linux. The TS scheduler changes priorities and CPU time quantas for processes based on recent processor usage.

Since we appear to have plenty of CPU resources, we could draw the conclusion that the default (TS) scheduling class does not appear to be good enough for us. Either the scheduler is not allocating sufficient CPU time quanta (resulting in involuntary context switching) or not giving the process a sufficiently higher priority so that it can be scheduled earlier than other processes.

So how do we change it? Obviously we would want to

set a fixed priority for Oracle processes so that they are able to run on the CPU ahead of other competing processes.

set a fixed time quanta for Oracle processes so that they can run to completion on the CPU.

With either Solaris or Linux, the easiest way to implement this is to change the Scheduling class for the oracle processes. Both the Operating systems offer a Scheduling class with Fixed Priorities and Fixed CPU Time Quantas - Fixed in the sense it is fixed throughout the lifetime of the process, but can be changed to suit your requirements at any time.

In Linux, it is the RR class and in Solaris it is the FX class. The simplest way to change the scheduling class is to use the priocntl tool. While it is a native binary on Solaris, it is available on Linux through the Heirloom Project.

On Linux, you would need to use the renice command to change the CPU time quantas and on Solaris, priocntl does both - scheduling class and time quanta.

Let us look at a few examples -

On Linux - Let us try and change the Scheduling Class and Time Quanta for the Log Writer.[root@dbrac root]# ./priocntl -l CONFIGURED CLASSES ==================

Now the time quanta is 459. Higher the time quanta, the more time the process can spend on the CPU before being context switched out.

For Solaris - priocntl can be used to set the Scheduling class and the time quanta simultaneously. I am not going to show any examples here as it would be the same as above.

Now, as to which processes (background/shadow) need to have a higher priority than others, that is a decision which requires significant amount of testing. I have seen 30% improvements in load timings when changing scheduling properties, however it has the potential to completely break the environment if not done correctly.

Interestingly enough, when running Oracle RAC on Linux, you would notice that the lms process are now running under the RR Scheduling class.

5 comments:

"it has the potential to completely break the environment if not done correctly." is very significant.

Besides fixed time another reason why a process would be switched out of a processor would be when it makes an I/O call. The process would then relinquish the CPU. Would changing the "fixed quanta" affect this behaviour ?

(Also, I need to recheck how a Priority is listed. I thought a higher absolute value for PRI meant a lower relative priority).

"Besides fixed time another reason why a process would be switched out of a processor would be when it makes an I/O call. The process would then relinquish the CPU. Would changing the "fixed quanta" affect this behaviour ?"

I do not believe so. The time quanta would apply only when the process is running on the CPU actively.

The correlation of priorities with the numbers as displayed by the "ps" command depends on the argument passed to "ps".

With the -c flag, higher numbers in the PRI column means higher priority. Otherwise, it would mean the opposite - lower numbers are higher priority.

As a DBA I want to limit myself to database front and let the SA figureout what's wrong with CPU if I confirm that nothing is wrong from Oracle Database prespecitive . What in your view is the best way to monitor CPU usage within oracle Database. As you said , CPU as top event (time) is a indication that something is consuming more resource. I would like to quickly run some queries against v$ views to get the information about the same . Do you thing V$OSSTAT or V$SYSSTAT are good starting point ? what should be the chronological steps to come to a conclude the bottleneck ?