Oracle UNIX Monitoring RAM Memory Consumption Administration Oracle UNIX/Linux Tips by Burleson Consulting

Monitoring RAM Memory Consumption

In the UNIX environment, RAM memory is automatically managed by the operating system. In systems with ?virtual? memory, a special disk called swap is used to hold chunks of RAM that cannot fit within the available RAM on the server. In this fashion, a virtual memory server can allow tasks to allocate memory above the RAM capacity on the server. As the server is used, the operating system will move some memory pages out to the swap disk in case the server exceeds its physical capacity. This is called a page-out operation.  Remember, page-out operations occur even when the database server has not exceeded the RAM capacity.

RAM memory shortages are evidenced by page-in operations. Page-in operations cause Oracle slowdowns because tasks must wait until their memory region is moved back into RAM from the swap disk. The remedy for memory overload is to add more memory or to reduce the demands on memory by reducing sort_area_size, implementing the multithreaded server, or reducing the values for shared_pool or db_block_buffers.

As we briefly noted in Chapter 1, there are several remedies for overloaded RAM memory:

* Add RAM - Add additional RAM to the server

* Reduce Oracle RAM - Reduce the size of the SGA regions by down-sizing the shared pool or data block buffers

* Implement the Multi-threaded server ? Implementing the MTS will reduce RAM demands by shifting RAM demand from individual Program Global Areas (PGS) into a shared large_pool inside the Oracle SGA.

* Tune SQL ? You can often tune expensive SQL statements to reduce the demands on the server

Next, let?s move on and take a look at how to build an easy UNIX server monitor by extending the Oracle STATSPACK tables.

Capturing Server Performance Data Inside STATSPACK

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:

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 and then directs the results into our Oracle table, as shown in Figure 3-4.

Figure 4: Capturing vmstat output into a STATSPACK extension 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 www.oraclepressbooks.com to get the code.

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 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@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.

Run queue

Page-in

Page-out

 

User

System

Idle

Wait

Dialect

 

column

column

column

 

column

column

column

column

HP/UX

 

1

8

9

 

16

17

18

NA

AIX

 

1

6

7

 

14

15

16

17

Solaris 

 

1

8

9

 

20

21

22

NA

Linux

 

1

8

9

 

14

15

16

NA

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

Internals of the vmstat Capture Script

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 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 see how to monitor the Oracle database server, let?s examine how we can use this technique to report on other Oracle-related server. This technique is very handy for reporting on Oracle Web servers and application servers.

 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.