Using sar to Monitor Server Statistics Oracle Tips by Burleson Consulting

The sar utility (System Activity Reporter) is a system activity reporter that is quite popular with HP/UX and Solaris, and sar is also available for AIX. Just like top, sar gives detailed information about Oracle tasks from the UNIX level. You will be able to see the overall consumption of CPU, disk, memory, and Journal File System (JFS) buffer usage. There are three major flags that you can use with sar:

• sar –u Shows CPU activity

• sar –w Shows swapping activity

• sar –b Shows buffer activity

NOTE: Each flavor of UNIX has a different implementation of sar. For example, some of the key flags used in the Sun version of sar are not available on HP/UX. The examples in this book show the HP/UX version of sar.

The output from sar reports usually shows a time-based snapshot of activity. This is true for all reports that you'll see in this section. When you issue the sar command, you pass two numeric arguments. The first represents the time interval between samples, and the second represents the number of samples to take. For example:

L 6-4

The sar command in this example is requesting five samples taken at 10-second intervals.

sar –u: The CPU Report

The sar –u command is very useful for seeing the overall CPU consumption over time. In the example that follows, I execute sar –u to see the state of the CPU. CPU time can be allocated into the following four sections: user mode, system mode, waiting on I/O, and idle.

L 6-5

sar –w: The Memory Switching and Swapping Activity Report

The sar –w command is especially useful if you suspect that your database server
is experiencing a memory shortage. The following example shows the swapping activity report that you get from sar:

L 6-6

The column descriptions are as follows:

• swpin/s Number of process swap-ins per second.

• swpot/s Number of process swap-outs per second.

• bswin/s Number of 512-byte swap-ins per second.

• bswot/s Number of 512-byte swap-outs per second.

• pswch/s Number of process context switches per second.

sar –b: The Buffer Activity Report

The sar -b command causes sar to report buffer activity, which equates to disk I/O activity and is especially useful if you suspect that your database is I/O bound. The report shows real disk I/O, and the interaction with the UNIX Journal File System (JFS) buffer. For example, here we see a sample of sar output over a 5-second interval:

L 6-7

In the output shown here, you see the following data columns:

•  Bread/s Number of physical reads from disk per second.

• lread/s Number of reads per second from the UNIX JFS buffer cache.

• %rcache Buffer cache hit ratio (for the UNIX JFS buffer cache) for read requests.

• bwrit/s Number of physical writes to disk per second. This gives the Remote DBA an indication of the overall write activity on the server.

• lwrit/s Number of writes per second to the UNIX JFS buffer cache.

• %wcache Buffer cache hit ratio (for the UNIX JFS buffer cache) for write requests.

• pread/s Number of reads per second from disk. This is an excellent measure of the load on the I/O subsystem.

• pwrit/s Number of writes per second to disk.

The sar –b command is often used in reactive tuning when you want to correlate what is happening inside Oracle with what is happening on the database server. Now let's turn our attention to the detection of server problems. We will begin by examining CPU consumption of the database server.

This is an excerpt from "Oracle9i High Performance tuning with STATSPACK" by Oracle Press.

---

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Expert Remote DBA BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call BC Remote DBA today.