 |
|
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:
ORACLE_HOME=/usr/app/oracle/admin/product/8/1/6
$ORACLE_HOME/bin/sqlplus
-s perfstat/perfstat@testsys1<<EOF
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. |
 |
|
