Measure Oracle database server performance with vmstat

Oracle Tips by Burleson Consulting

September 16, 2002

When deploying Oracle as the database server platform in your environment, one of the most important things you should be aware of is your server's performance. While Oracle includes some nice tools to help track server performance, the built-in utilities don't give you all the information you need. In this Daily Feature, I'll show you how to effectively measure Oracle database server performance using vmstat.

Author's Note

Oracle has achieved its outstanding popularity as a database primarily because it runs on almost all computing platforms. The Oracle database can be found on everything from mainframes to Linux PCs. Therefore, it's very difficult to come up with generic server guidelines because all of the operating system metrics are different. Because the Oracle Corporation estimates that over 70 percent of data is stored on UNIX, I'll use the UNIX vmstat utility for the purposes of this Daily Feature.

What's wrong with Statspack?

You can capture statistics about resource usage at the server level as well as the Oracle database level. When you start the Oracle database instance, the Oracle program executable is invoked, spawning over a dozen factotum (slave) processes. These background processes, which serve to control all aspects of Oracle, include:

pmon - The process monitor process
smon - The system monitor process
arch - The redo log archive process
dbwr - The database writer process

As required, Oracle requests are passed to these background processes in order to perform a server function. At startup time, Oracle performs all of the initial interactions with the Oracle database server, including RAM region memory allocation, establishing CPU resources, and establishing communication with the disk drives.

You can use the Oracle Statspack utility to capture snapshots showing the differences in system interaction over pre-specified periods of time (usually each hour). Information relating to the server that's available inside Statspack includes:

Physical disk reads.
CPUs used by specific transactions.
RAM memory used by specific transactions.

One shortcoming of the Oracle statistics tool is that it doesn't show the aggregate demand upon the database server. You can use the utility to see resource utilization for a specific task, but you can't directly see the server stress. However, even if Statspack can't give you all of the information you need, you can use some native operating system utilities to find out how many resources are consumed by the Oracle databases.

Vmstat to the rescue

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. You can run vmstat using the simple UNIX daemon process shown in Listing 1.

#!/bin/ksh

# This is the Linux version

# First, we must set the environment . . . .

ORACLE_SID=edm1
export ORACLE_SID

ORACLE_HOME=`cat /etc/oratab|grep \^$ORACLE_SID:|cut -f2 -d':'`
export ORACLE_HOME

ORACLE_HOME=/usr/app/oracle/admin/product/8/1/6
export ORACLE_HOME

PATH=$ORACLE_HOME/bin:$PATH
export PATH

MON=`echo ~oracle/mon`
export MON

SERVER_NAME=`uname -a|awk '{print $2}'`
typeset -u SERVER_NAME
export SERVER_NAME

# sample every five minutes (300 seconds) . . . .

SAMPLE_TIME=300

while true
do

vmstat ${SAMPLE_TIME} 2 > /tmp/msg$$

# run vmstat and direct the output into the Oracle table . . .

cat /tmp/msg$$|sed 1,3d | awk '{ printf("%s %s %s %s %s %s\n", $1, $8, $9, $14, $15, $16) }' | while read RUNQUE PAGE_IN PAGE_OUT USER_CPU SYSTEM_CPU IDLE_CPU
do

$ORACLE_HOME/bin/sqlplus -s system/manager@testb1<<EOF

      insert into sys.mon_vmstats
                           values (
                             sysdate,
                             $SAMPLE_TIME,
                             '$SERVER_NAME',
                             $RUNQUE,
                             $PAGE_IN,
                             $PAGE_OUT,
                             $USER_CPU,
                             $SYSTEM_CPU,
                             $IDLE_CPU,
                             0
                                  );
      EXIT
EOF

done

rm /tmp/msg$$

Listing 1 - An vmstat capture script

This daemon collects server performance information every five minutes (300 seconds) and stores the server data inside Oracle tables. These Oracle vmstat tables, once populated, can give you interesting details about your server. For example, you can find out usage information about how much RAM and disk I/O is being used on our database server, as well as how many CPUs are being used.

Working with the results

When analyzing vmstat output, there are several metrics to which you should pay attention. For example, keep an eye on the CPU run queue column. The run queue should never exceed the number of CPUs on the server. If you do notice the run queue exceeding the amount of CPUs, it's a good indication that your server has a CPU bottleneck.

To get an idea of the RAM usage on your server, watch the page in (pi) and page out (po) columns of vmstat's output. By tracking common virtual memory operations such as page outs, you can infer the times that the Oracle database is performing a lot of work. Even though UNIX page ins must correlate with the vmstat's refresh rate to accurately predict RAM swapping, plotting page ins can tell you when the server is having spikes of RAM usage.

Once captured, it's very easy to take the information about server performance directly from the Oracle tables and plot them in a trend graph. Rather than using an expensive statistical package such as SAS, you can use Microsoft Excel. Copy and paste the data from the tables into Excel. After that, you can use the Chart Wizard to create a line chart that will help you view server usage information and discover trends.

��