 |
|
Internals of the vmstat Capture
Script
Oracle Tips by Burleson Consulting
|
It is important to understand how the
get_vmstat.ksh script functions, so let's examine the steps in
this script:
1.
It executes the vmstat utility for the specified elapsed-time
interval (SAMPLE_TIME=300).
2.
The output of the vmstat is directed into the /tmp directory.
3.
The output is then parsed using the awk utility, and the values
are inserted into the mon_vmstats table.
Once started, the get_vmstat.ksh script
will run continually and capture the vmstats into your stats$vmstat
table. This script is an example of a UNIX daemon process, and it will
run continually to sample the server status. However, the script may
be terminated if your server is rebooted, so it is a good idea to
place a crontab entry to make sure that the get_vmstat script
is always running. Next is a script called run_vmstat.ksh that
will ensure that the vmstat utility is always running on your server.
Note that you must make the following changes
to this script:
vmstat=`echo
~oracle/vmstat`
ORACLE_SID=`cat
${vmstat}/mysid`
run_vmstat.ksh
L5-28a
#!/bin/ksh
# First, we must set the environment . . . .
vmstat=`echo ~oracle/vmstat`
export vmstat
ORACLE_SID=`cat ${vmstat}/mysid`
export ORACLE_SID
ORACLE_HOME=`cat /etc/oratab|grep $ORACLE_SID:|cut -f2 -d':'`
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
#----------------------------------------
# If it is not running, then start it . . .
#----------------------------------------
check_stat=`ps -ef|grep get_vmstat|grep -v grep|wc -l`;
oracle_num=`expr $check_stat`
if [ $oracle_num -le 0 ]
then nohup $vmstat/get_vmstat_linux.ksh > /dev/null 2>&1 &
fi
The run_vmstat.ksh script can be
scheduled to run hourly on the server. As we can see by examining the
code, this script checks to see if the get_vmstat.ksh script is
executing. If it is not executing, the script resubmits it for
execution. In practice, the get_vmstat.ksh script will not
abort, but if the server is shut down and restarted, the script will
need to be restarted.
Here is an example of the UNIX crontab file.
For those not familiar with cron, the cron facility is a UNIX
scheduling facility that allows tasks to be submitted at specific
times. Note that it schedules the run_vmstat.ksh script every
hour, and runs a vmstat exception report every day at 7:00
a.m.
00 * * * *
/home/vmstat/run_vmstat.ksh > /home/vmstat/r.lst
00 7 * * * /home/vmstat/run_vmstat_alert.ksh prodb1 > /home/vmstat/v.lst
Now that we see how to monitor the Oracle
database server, let's examine how we can use this technique to report
on other Oracle-related servers. This technique is very handy for
reporting on Oracle Web servers and application servers.
Reporting vmstat Information on Other Oracle Servers
To get a complete picture of the performance of
your total Oracle system, you must also monitor the behavior of all of
the servers that communicate with Oracle. For example, many Oracle
environments have other servers:
-
Oracle
Applications In Oracle Applications products, you generally have
separate application servers communicating with the database server.
-
SAP with
Oracle In SAP, you have separate application servers that
communicate with Oracle.
-
Real
Application Clusters (Oracle Parallel Server) With RAC, you have
multiple Oracle database servers, all sharing the same database.
-
Oracle Web
Applications When using Oracle databases on the Web, you have
separate Web servers that direct the communications into the
database.
This technique in get_vmstat.ksh can
easily be extended to measure the performance of other servers in your
Oracle environment. Note that the stats$vmstat table has a column to
store the server name. Since we can separate vmstat metrics by server,
we simply need to create a remote vmstat script that will capture the
performance of the other servers and send the data to a central
database. Because only the database server contains an Oracle
database, the vmstat data will be sent to the database from the remote
server using database links. Any server that has a Net8 client can be
used to capture vmstat information.
If we take a close look at the get_vmstat
script from before, we see that this script can be executed on a
remote server. The script will send the vmstat data to the server that
contains our Oracle database using a database link. Note where the
script enters sqlplus using sqlplus perfstat/perfstat@prod.
By collecting the data remotely, we can capture
a complete picture of the performance of all of the components of the
Oracle environment, not just the database server. This is important in
cases where you need to track slow performance of e-commerce systems.
Using this vmstat information, you can go back to the time of the
slowdown and see which Web servers may have been overloaded and also
examine the load on 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. |
 |
|
