Monitoring CPU with UNIX Oracle Database Tips by Donald BurlesonMay 12, 2015

 
A common question is "why is my 100% utilization at 100%".  There is a great deal of concern about the measurement of CPU at the Oracle server level.

If you suspect a CPU utilization problem, see these important notes on 100% CPU and Oracle.  Also see Oracle and CPU utilization metrics.

Also see my notes on OS Busy scripts.

Once we understand the CPU resources are scarce (just like RAM resources), and not to be wasted), we need to understand how to tell if our Oracle server is making optimal usage of his computing hardware.

There are many OS utilities that allow us to see CPU utilization statistics, including these, but also with uptime and procinfo.

• vmstat
• sar
• sadc
• watch (w)
• glance
• top

Each of these tools display CPU processor metrics at a finer level of detail than Oracle.  This is because the OS does not reveal all processor details to applications (To UNIX, Oracle is just another application), and the best place to see what's going on inside your server is to use the operating systems CPU monitors.  These will report different metrics on CPU utilization:

• The runqueue - This is the far left-hand column of the vmstat command display (labeled with an "r").  It reports the total length of the CPU dispatcher queue.  When the runqueue exceeds the number of CPU's on the server, you have have an overloaded server with a CPU bottleneck.
   
• The load average - This is defined as the sum of the run queue length and the number of jobs currently running on the CPUs. In each display of the load average consists of three numbers.  Most often, the load average numbers show a descending order from left to right, with the load average for 1, 5, and 15 minutes in the past. Occasionally, however, an ascending order appears (e.g. like that shown in the top output).

There are a host of UNIX commands that display CPU and memory consumption.  While there are dialect-specific utilities such as glance, we will look at the common vmstat and top utilities.

 

Using top to monitor CPU

The "top" command can be used to display CPU utilization.  The metrcis are:

• load average - The load average is computed as
• CPU states - This show percentage metrics for current processor usage.

 

 

 

 

Using svmon on AIX

 

 Where:

 size = the number of real memory frames (size of real memory)

inuse = is the number of frames containing pages

pin = Number of frames containing pinned pages in use

The svmon command can also be used with the -p option to display characteristics for a specific process ID (PID):

 

 

The watch command

The w command shows the "load average" which is computed from the current runqueue values.  Watch also shows the same information uptime did.

$ w
22:42:14 up  2:34,  2 users,  load average: 0.00, 0.00, 0.00
USER     TTY        LOGIN@   IDLE   JCPU   PCPU WHAT
terry    :0        20:10   ?xdm?   5:24   1.49s gnome-session
terry    pts/1     22:22    0.00s  0.24s  0.04s /usr/sbin/sshd

 

 

 

Using SAR

The sar utility (System Activity Reporter) is the system activity reporter that is quite popular in HP/UX, and is widely becoming available for AIX and Solaris systems.  SAR has much of the same functionality as vmstat, but provides additional details.

There are four major flags in sar:

sar -u = to see CPU

sar -w = for swapping

sar -b = for buffer activity

sar -d = for disk usage

 

Sar -w (memory switching and swapping activity)

 

 

 

 

 

 

 

Sar 'u (CPU Report)

 

 

 

 

Sar ?b (buffer activity report)

 

 

 

 

Using sadc

The sadc (System Activity Report Package) is a popular package that can be used inside cron to schedule collections of server statistics.

All of the sadc reports are located in the /usr/lbin/sa directory.  These reports must be run as root and provide detailed server information.  One of the most popular sadc reports is sa1:

 

Using glance to monitor Oracle CPU

For complete details, see my notes on monitoring Oracle with glance.

The glance utility is provided on HP/UX systems to provide a graphical display of server performance.  It displays current CPU, memory, disk and swap consumption, and also reports on the top processes.

 

Using the vmstat utility to monitor Oracle

The UNIX vmstat utility is especially useful for monitoring the performance of Oracle databases. You'll find vmstat on almost all implementations of UNIX, including Linux. Click here for details on monitoring Oracle CPU with vmstat, and building a CPU monitor for Oracle.

The vmstat utility is the most common Unix monitor utility.  It is found on virtually all dialects of UNIX (vmstat is called osview on IRIX), and vmstat quickly display's server values.  These values include:

r = runqueue - When this value exceeds the number of CPUs (lsdev -C|grep Proc|wc -l). then the sever is experiencing an CPU bottleneck

pi = Page in - Any non-zero values indicates that the server is short on memory and RAM memory is being send to the swap disk.  However, this can also occur when numerous programs are accessing their memory for the first time, so always remember to check the scan rate 'sr' column.  If both are non-zero. Then you are short on RAM.

sr = scan rate - If we see 'sr' rising steadily we know that the paging daemon is busy allocating memory pages.

 

For AIX and HP/UX, vmstat provides the following CPU values.  These values are expressed as percentages and will sum to 100 

us = user CPU percentage

sy = system CPU percentage

Id = Idle CPU percentage

wa = wait CPU percentage

When us+sy approaches 100, then the CPUs are busy, but not necessarily overloaded.  Only the run queue values determines CPU overload and only when 'r' exceeds the number of CPUs on the server.

When 'wa' values exceed 20, then 20% of the processing time is waiting for a resource, usually I/O.  It is common to see high wa values during backup and exports, but high wa values can also indicate an I/O bottleneck.

 

 

vmstat for Solaris

The display format for vmstat in Solaris is quite different than AIX and HP/UX.  In Solaris the 'vmstat -n' command is used to display server stats.  The relevant columns are:

 

Pi = page-ins

Us = CPU user time

Sys = CPU system time

Id = CPU idle time

R = runqueue - If this exceeds the number of CPU's then you are CPU-bound

 

In the example below, we sample an overstresses Oracle server.  Note that us + sy = 100, and that the r value far exceeds the 32 CPU's on this server: