Internals of the vmstat Capture Script Oracle Application Server 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 you server is rebooted, so it is a good idea to place a crontab entry to make sure that the get_vmstat.ksh script is always running. Below 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:

* Set the file location variable vmstat to the directory that contains your get_vmstat.ksh script:

vmstat=`echo ~oracle/vmstat`

* Create a small file in your UNIX file directory ($vmstat) called mysid. This file will contain one line and specify the name of your ORACLE_SID.

ORACLE_SID=`cat ${vmstat}/mysid`

run_vmstat.ksh
#!/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 have an understanding of Oracle9iAS server monitoring, let?s drill-down and see how OEM can be used to monitor Oracle9iAS components.

Oracle9iAS Server Load Balancing

Tracking the performance of the UNIX server is critical for the Oracle9iAS administrator because no amount of tuning is going to solve a server-related performance problem.  Of course, Oracle9iAS parameter changes may reduce server load, but we always need to pay careful attention to the performance of every server in our Oracle9iAS enterprise.

When tuning an UNIX Oracle9iAS server, we must always remember the goal of fully loading the CPUs and RAM on the server. Unused processing and RAM power can never be reclaimed, and with the significant depreciation of the value of most servers, maximizing the utilization is a noble goal. On any server that is dedicated to a Oracle9iAS component, we want to dedicate as much hardware resources to Oracle as possible without causing a server-related slowdown.

This section will cover the following topics.

• UNIX Monitoring Goals - This section to examine the goals of UNIX server tuning and show tools for displaying UNIX performance metrics.

• Extending vmstat to capture server statistics - Here we will review a method for capturing vmstat data inside tables on the Infrastructure (iasdb) database.

• Reporting on server statistics - This section will look at some handy scripts that will alert you to server exceptions and show you how to create trend and usage reports for your server.

      Let?s begin with a brief review of the goals of UNIX server monitoring.

UNIX Monitoring Goals

The monitoring of Oracle9iAS servers involves monitoring disk, RAM, CPU and network components.  CPU consumption on an Oracle server is a simple matter because the server manages all CPU transactions automatically. All servers are configured to use CPU cycles on an as-needed basis, and all Oracle9iAS components will use CPU resources freely. The internal machine code will manage the assignment of processors to active tasks and ensure that the maximum amount of processing power is applied to each task.

CPU shortages are evidenced in cases where the CPU run queue is greater than the number of CPUs. In these cases, the only solutions are to increase the number of CPUs on the processor or reduce the CPU demands on the server, mainly by changing configuration parameters or adding another application server. You can also decrease CPU demands on the Oracle database and Infrastructure by turning off Oracle Parallel Query, replacing the standard Oracle listener with the multithreaded server (MTS), and other actions that would reduce the processing demands on the hardware.

Tasks are serviced in UNIX according to their internal dispatching priority. Important tasks such as the UNIX operating system tasks will always have a more favorable dispatching priority because the UNIX system tasks drive the operating system

CPU overload is usually evidenced by high values in the vmstat runqueue column. Whenever the runqueue value exceeds the number of CPUs of the server, some task may be waiting for service. When we see a CPU overload, we have several alternatives:

1. Add additional CPUs - This is usually the best solution, because an Oracle9iAS server that is overloading the CPU will always run faster with additional processors.

2. Reduce server load/Add more Oracle9iAS servers - If the CPU overload is not constant, task load balancing may be the solution. For example, it is not uncommon to see a server overloaded during peak work hours, and then return to 80-percent idle in the evenings. In these cases, batch tasks can be rescheduled to execute when there are more idle CPU resources available.

3. Alter task dispatching priorities - Almost all UNIX operating systems allow the root user to change the dispatching priority for tasks. As a general rule, the online database background tasks are given more priority (a smaller priority value), while less critical batch processes are placed with less priority (a higher priority value). However, altering the default dispatching priorities is not a good long-term solution, and it should only be undertaken in emergency situations.

This is an excerpt from "Oracle 10g Application Server Administration Handbook" by Don Burleson and John Garmany.