BC remote Oracle DBA - Call (800) 766-1884  
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

 

 


 

 

 

 

 
 

Capturing Server Performance Data Inside STATSPACK

Oracle Tips by Burleson Consulting

Now that we have seen that vmstat can provide useful information about the status of the Oracle database server, how can we create a mechanism for monitoring these vmstat statistics? As we noted from our discussion of vmstat, system-level resource contention is transient and fleeting, and it is often very easy to miss a bottleneck unless we are constantly vigilant. For this reason, we need to create an extension to the STATSPACK tables that will constantly poll the hardware and collect any data relating to resource contention.

The concept behind this extension is to execute the vmstat utility and capture the performance information within an Oracle table called stats$vmstat.

While this technique works very well for monitoring the Oracle database server, these operating system statistics can also be used to monitor the other computers in your system. These include the application servers (Web servers) and the Oracle database server. We will show you how to collect vmstats on a remote server later in this chapter.

A Script to Capture vmstat Information

It is a simple matter to create an Oracle table to store this information and use a script to populate the table. Creating the automated vmstat monitor begins by creating an Oracle table to contain the vmstat output.

L 5-20

connect perfstat/perfstat;

drop table stats$vmstat;
create table stats$vmstat
(
     start_date          date,
     duration            number,
     server_name         varchar2(20),
     runque_waits        number,
     page_in             number,
     page_out            number,
     user_cpu            number,
     system_cpu          number,
     idle_cpu            number,
     wait_cpu            number
)
tablespace perfstat
storage (initial   10m
         next       1m
         pctincrease 0)

;

Now that we have defined an Oracle table to capture the vmstat information, we need to write a UNIX script that will execute vmstat, capture the vmstat output, and place it into the Oracle table.

The main script to collect the vmstat information is a Korn shell script called get_vmstat.ksh. As we noted earlier, each dialect of UNIX displays vmstat information in different columns, so we need slightly different scripts for each type of UNIX.

The idea is to write a script that continually runs the vmstat utility and then directs the results into our Oracle table, as shown in Figure 5-5.

Figure 5-27: Capturing vmstat output into a STATSPACK extension table

The script shows the vmstat capture utility script for the Linux operating system. Note that you must change this script in several places to make it work for you:

  • You must set the ORACLE_HOME to your directory:

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

  • You must set your ORACLE_SID in the sqlplus command:

          $ORACLE_HOME/bin/sqlplus -s perfstat/perfstat@testsys1<<EOF

  • You can change the duration of samples by setting SAMPLE_TIME:

SAMPLE_TIME=300

get_vmstat.ksh (Linux version)

L 5-24

#!/bin/ksh

# This is the Linux version

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

PATH=$ORACLE_HOME/bin:$PATH
export PATH
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 perfstat/perfstat@testsys1<<EOF
      insert into perfstat.stats\$vmstat
                           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$$

Because of the differences in implementations of vmstat, the first task is to identify the columns of the vmstat output that contain the information that we want to capture. Once we know the columns that we want to capture, we can add these columns to the vmstat script to put the output into our Table 5-1.

Using this table, you can adjust the capture script according to your operating system. You customize the script by changing the line in the script that reads the vmstat output and places it into the stats$vmstat table. Here is a summary of the UNIX dialect changes to this line.

HP/UX vmstat Columns

L 5-25

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

IBM AIX vmstat Columns

L 5-26

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

Sun Solaris vmstat Columns

L 5-27

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

Linux vmstat Columns

L 5-28

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


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.

 

 

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.



Hit Counter