Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







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.


#  This is the Linux version

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


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


export PATH

MON=`echo ~oracle/mon`
export MON

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


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


while true

   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

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

      insert into sys.mon_vmstats
                           values (



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.




Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational