Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 

 UNIX VMSTAT Utility to the Rescue
Oracle Tips by Burleson Consulting

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by the top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).  The following is an excerpt from the book.

Vmstat to the Rescue


The UNIX vmstat utility is especially useful for monitoring the performance of Oracle databases. Vmstat can be found on almost all implementations of UNIX, including Linux. Run vmstat using the simple UNIX daemon process shown in Listing 4.1.

 

       Listing 4.1 – vmstat capture script

#!/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

done

rm /tmp/msg$$

 

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 interesting details about the server. For example, one can find out usage information about how much RAM and disk I/O is being used on the database server, as well as how many CPUs are being used.

When analyzing vmstat output, there are several metrics to which the DBA 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 it is noticed that the run queue starts exceeding the amount of CPUs, it is a good indication that the server has a CPU bottleneck.


To get an idea of the RAM usage on the 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, this infers 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 when the server is having spikes of RAM usage.

Once captured, it is 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, use Microsoft Excel. Copy and paste the data from the tables into Excel. After that, use the Chart Wizard to create a line chart that will help view server usage information and discover trends.



For more details on Oracle utilities, see the book "Advanced Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.

You can buy it direct from the publisher for 30% off directly from Rampant TechPress.

     

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.