Oracle9iAS Load Balancing Oracle Application Server Tips by Burleson Consulting

Oracle9iAS has several points where load balancing occurs (Figure 10.16)  Here we see the typical 3-tiered Oracle9iAS architecture with the Web Cache layer, the application server layer and the database layer.  Within this architecture we have the following areas of load balancing:

• Web cache to HTTP server ? The Web Cache interrogates HTTP server statistics and routes transaction to the least-loaded HTTP server.

• HTTP server to database listener ? The HTTP server load balances transactions to multiple database listeners.

• Database Listener to MTS Dispatcher ? When using the Oracle multithreaded server (MTS), numerous dispatcher processes funnel transactions to the database.  The listener will route an incoming database transaction to the least loaded dispatcher.

• MTS dispatcher to database instance ? When using Oracle real application clusters (RAC), the dispatcher can route a database transaction to the least loaded Oracle instance.  Each instance in the RAC cluster accesses the same back-end database files.

Figure 10.16 ? Load balancing points within Oracle9iAS

In addition, we also have the ability to perform hardware load balancing by defining a pool of spare servers and starting either a Web Cache or an application server on these servers.  This allows the Oracle9iAS administrator to re-allocate processing resources depending on the nature of the system load.

Oracle9iAS Server Load Balancing

Measuring server stress (RAM and CPU) is a critical part of Oracle9iAS tuning.  Most Oracle9iAS administrators use the common vmstat utility to monitor server stress because it is common to all dialects of UNIX and easy to invoke and interpret.

When capturing server metrics it is important to note that server-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 can create an Oracle table that will accept vmstat data from all of our Oracle9iAS servers and collect all data relating to resource contention.  The concept behind this collection 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 all of the Oracle9iAS servers in your farm.

If you want to monitor vmstat data on your Oracle database of Infrastructure server, it is quite easy to writer a vmstat scripts that will collect elapsed-time vmstat information and store it inside the oracle database.

Note: You must install the Oracle*Net client software on all of the Oracle9iAS servers with a tnsnames.ora file pointing to the Infrastructure database (iasdb).  This establishes connectivity for the vmstat scripts to place entries into your centralized Oracle9iAS repository.

Capturing vmstat Information for all Oracle9iAS server

It is a simple matter to create an Oracle table on the Infrastructure Oracle database server to store the vmstat server statistics from each server in the Oracle9iAS enterprise.  Creating the automated vmstat monitor begins by creating an Oracle table to contain the vmstat output from each server:

cr_vmstat_tab.sql
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 on every Oracle9iAS server and then directs the results into our Oracle table on the Infrastructure database (iasdb), as shown in Figure 10.17.

Figure 10.17: Capturing vmstat output into the Infrastructure table

The script shows the vmstat capture utility script for the Linux operating system. The scripts at the Oracle Press Web site contain complete code for a vmstat script for all of the major UNIX dialects.  Go to http://shop.osborne.com/cgi-bin/oraclepress/downloads.html to get the code samples.

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@iasdb<<EOF

* You can change the duration of samples by re-setting SAMPLE_TIME UNIX variable:

SAMPLE_TIME=300

get_vmstat.ksh (Linux version)
#!/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
DLE_CPU
   do

      $ORACLE_HOME/bin/sqlplus -s perfstat/perfstat@iasdb<<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.  In Oracle9iAS, it is common to have multiple types of server types, and Linux server are very popular with Oracle9iAS HTTP server (OHS), while the database back-end may be Sun or HP/UX (table 10.3).

Run queue Page-in Page-out User System
IdleWait Dialect column column column column column column column HP/UX 189
161718NAAIX 16714151617Solaris  189202122NALinux  189141516NA

Table 10.3: vmstat columns for Oracle9iAS server types

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
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
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
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
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 "Oracle 10g Application Server Administration Handbook" by Don Burleson and John Garmany.