Lesson 2	Monitoring CPU usage on a database server
Objective	Monitor CPU usage with the ps command.

Monitor CPU usage via the ps command

In order to maximize CPU performance, you must access and interpret data about CPU usage.

Processes consume CPU resources

As you already know, an Oracle instance consists of a set of background processes that begin at database startup time and continue to run on the database server until the database is stopped. These processes constitute the Oracle instance and are the processes that will consume CPU resources on behalf of the Oracle database.

Binding Oracle processes to specific CPUs

In environments where a database server has multiple CPUs, many DBAs have the option of dedicating Oracle processes to run on specific CPUs. This is an advantage because the Oracle background processes often loose their context when the OS directs the process to use another CPU.
The context of a process is the internal instruction set that consists of the CPU registers and their current contents. When an Oracle process switches CPUs, these instruction sets must be moved to the new CPU, slowing down execution time. The implementation details are different for every OS, but in IBMs AIX operating system, process binding is achieved with the bindprocessor UNIX command. Check the Oracle OS-specific documentation for your environment to see how to do process binding in your environment.

Processes are Competing

These processes are always executing and competing for CPU resources on our database server. In order to check for processes that are consuming an inordinate amount of CPU cycles, you can use the ps command. For example, sometimes Oracle sessions may become disconnected from the database and spin, causing a huge amount of CPU activity. The following simulation shows how to monitor the amount of resources that each process is consuming.

Start by using the ps command to view how each background process is using CPU resources. Type (S1)ps -ef|grep don1(S0).
diogenes>
ps -ef | grep don1
Please check your entry and try again.

Within the ps command we see several relevant columns, including the user who owns the process (UID) and the time that the process started (STIME), and most importantly, the TIME column. This column shows the total amount of CPU consumed by the process. Click anywhere again to continue.
To see the top consumers of CPU on a database server, issue the following command: (S1)ps -ef|grep ora|sort +6|tail(S0).
diogenes:>
ps -ef|grep ora|sort +6|tail
Please check your entry and try again.

Here we have directed UNIX to display the top CPU consumers, and we see that process 150538 is the top CPU consumer with 9:31 minutes of CPU time. This is the end of the simulation. Click the Exit button.

Using the ps command to monitor CPU usage

Start by using the ps command to view how each background process is using CPU resources. Type ps –ef|grep don1.
Here are all of the Oracle processes on our host. Notice that the database writer process (dbw0), the recoverer process (reco) the checkpoint process (ckpt), the log writer (lgwr), the process monitor process (pmon) and the system monitor process (smon) all make up the Oracle instance. Click anywhere on the screen to continue the simulation.
Within the ps command we see several relevant columns, including the user who owns the process (UID) and the time that the process started (STIME), and most importantly, the TIME column. This column shows the total amount of CPU consumed by the process. Click anywhere again to continue.
To see the top consumers of CPU on a database server, issue the following command: ps –ef|grep ora|sort +6|tail.
Here we have directed UNIX to display the top CPU consumers, and we see that process 150538 is the top CPU consumer with 9:31 minutes of CPU time. This is the end of the simulation.

The next lesson demonstrates another way to monitor CPU usage. spinning: Spinning means that the process continues to look for the availability of the latch after fixed intervals of time, during which it sleeps.