Database Performace Monitor

Here is an utility to monitor your database health for a period of time. There are various parameters in the database which could be monitored and improved. Here I take a simple example of monitoring the SGA usage. You may add more monitoring SQLs to these scripts and capture the data. This is how the scripts work.

1. Create table to save performance data. Identify a database and tablespace to save the performance data. The primary key of the table is monitoring date and database name. Since Oracle Date includes date and time, you may collect the statistics any number of time in a day. The script which follows has the monitoring date formatted as date+hour (no minutes or seconds). This helps me in grouping.

drop table sga_monitor;
create table sga_monitor (
  mon_date	date,
  db_name 	varchar2 (8),
  sga_total	number   (8,3), -- in MB
  sga_buffers   number   (8,3), -- in MB
  buf_hit	number   (5,2),
  dd_hit	number   (5,2),
  lib_hit	number   (5,2),
  sort_hit	number   (5,2),
  rbnowait_hit	number   (5,2),
  users_count	number   (5), 
  constraint pk_sga_monitor primary key (mon_date, db_name) using index tablespace
	     stats_data)
  tablespace stats_data
  storage (initial 2M next 2M pctincrease 0 maxextents 249)
  pctfree 0
/

2. sgastat.sh - This UNIX script file reads the tnsnames.ora file and identifies distinct Oracle SID values. Then by connecting to the different ORACLE_SIDs, the required data is collected in a flat file format delimited by "|". Connection to the databases are made using OS authentication. To know more about OS authentication, please refer to Tip of the Month - Aug 98. There are two files called from this shell script - a PL/SQL script to get the statististics from the database and a SQL*Loader control file to load data to the monitoring table. The first few steps in this UNIX shell script can be removed if all the databases to monitor are in the same server. For the databases on one server, the database names can be obtained by reading the /etc/oratab or /var/opt/oracle/oratab file.

Copy the script file (sgastat.sh), the PL/SQL file (sgastat.sql) and the SQL*Loader control file (sgastat.ctl) to your script directory (here the directory name is /scriptdir - modify shell script appropriately). This shell script may run once or twice a day using a UNIX cron. The data collected from various databases are saved in a flat file at /tmp/sgaload.dat, which is the input for the SQL*Loader. Expect SQLs to query the statistics table and see meaningful reports on these pages later.

#!/bin/ksh
#
# Collect SGA statistics from all instances
# Biju Thomas 6/23/98
# Related files : sgastat.sql, sgastat.ctl
#
. /etc/profile
tempfile=/tmp/sidfile1.tmp
sidfile=/tmp/sidfile1.out
wloadfile=/tmp/sgaload.tmp
wdatfile=/tmp/sgaload.dat
rm -f $wloadfile 2> /dev/null
touch $wloadfile
TNS_DIR=$ORACLE_HOME/network/admin - directory where tnsnames.ora file resides
cat $TNS_DIR/tnsnames.ora | grep SID > $tempfile
sort -u -o$sidfile $tempfile
awk '{ print $5 }' $sidfile > $tempfile
tr -d \) > $sidfile
#
# Now $sidfile has all the SIDNAMES from where data collected
#
ORA_BIN=$ORACLE_HOME/bin
cat $sidfile | while read SIDNAME
do
  $ORA_BIN/sqlplus -s /@$SIDNAME 2> /dev/null >> $wloadfile <<EOF
  @/scriptdir/sgastat.sql
EOF
done
# 
# Remove errors
#
grep "|" $wloadfile > $wdatfile
#
# Load data to ORACLE database
ORACLE_SID=ORACLE -- Provide database name where the table is created
$ORACLE_HOME/bin/sqlload / control=/scriptdir/sgastat.ctl
#
# End of Script

3. sgastat.sql - This is a PL/SQL program unit called from sgastat.sh. This script has various queries to get the SGA statistic information. The output from this script is a single line with each statistic seperated by "|". The database account running this script needs DBA role or SELECT ANY TABLE system privilege.

set serveroutput on feedback off
declare
  WMON_DATE		varchar2 (10);
  WDB_NAME		varchar2 (8);
  WSGA_TOTAL		number   (8,3);
  WSGA_BUFFERS		number   (8,3);
  WBUF_HIT		number   (5,2);
  WDD_HIT		number   (5,2);
  WLIB_HIT		number   (5,2);
  WSORT_HIT		number   (5,2);
  WRBNOWAIT_HIT		number   (5,2);
  WUSERS_COUNT		number   (5);
  WOUT			varchar2 (255);
begin
  SELECT to_char(sysdate,'MMDDYYYYHH24'), substr(global_name,1,8)
  INTO   WMON_DATE, WDB_NAME
  FROM   global_name;

  -- SGA sizes
  SELECT SUM(value)/1048576, 
         SUM(DECODE(name,'Database Buffers',value,0))/1048576
  INTO   WSGA_TOTAL, WSGA_BUFFERS
  FROM   v$sga;

  -- buffer cache hit ratio
  SELECT (1 - (SUM(DECODE(a.name,'physical reads',value,0)) / 
         (SUM(DECODE(a.name,'consistent gets',value,0)) + 
         SUM(DECODE(a.name,'db block gets',value,0))))) * 100 
  INTO   WBUF_HIT
  FROM   v$sysstat a;

  -- data dictionary cache hit %
  SELECT ((sum(gets)-sum(getmisses))/sum(gets))*100 
  INTO   WDD_HIT
  FROM   v$rowcache;

  -- library cache hit %
  SELECT ((SUM(pins)-SUM(reloads))/SUM(pins))*100 
  INTO   WLIB_HIT
  FROM   v$librarycache;

  -- memory sort hit ratio %
  SELECT (SUM(DECODE(name, 'sorts (memory)', value, 0)) / 
         (SUM(DECODE(name, 'sorts (memory)', value, 0)) + 
         SUM(DECODE(name, 'sorts (disk)', value, 0)))) * 100 
  INTO   WSORT_HIT
  FROM   v$sysstat;

  -- rollback nowait hit ratio %
  SELECT ((SUM(gets)-SUM(waits))/SUM(gets))*100 
  INTO   WRBNOWAIT_HIT
  FROM   v$rollstat;

  -- users logged on
  SELECT COUNT(*) 
  INTO   WUSERS_COUNT
  FROM   v$session 
  WHERE  username IS NOT NULL;

  WOUT := WMON_DATE || '|' || WDB_NAME || '|' || WSGA_TOTAL || '|' || WSGA_BUFFERS || '|' 
|| WBUF_HIT || '|' || WDD_HIT || '|' || WLIB_HIT || '|' || WSORT_HIT || '|' || WRBNOWAIT_HIT 
|| '|' || WUSERS_COUNT;

  dbms_output.put_line (wout);

end;
/
set feedback on

4. sgastat.ctl - This is the SQL*Loader control file used to load data to the database after collecting information from all databases. Make sure the column names are in the same order as in your PL/SQL script output line.

load data
infile '/tmp/sgaload.dat'
append
into table sga_monitor
fields terminated by '|'
(mon_date date(10) "MMDDYYYYHH24", db_name, sga_total, sga_buffers,
 buf_hit, dd_hit, lib_hit, sort_hit, rbnowait_hit, users_count)

These scripts are provided to give you an idea of how statistics may be collected. It is vital to have the statistics for a period of time to provide better tuning on the database. Many third party tools are available in the market to capture various performance statistics. This is for the Database Administrators who do not have any such tools.... Let me know if this help you!

Search BijooS.com Exact Match Search      
Home Oracle DBA TKMCE Alumni H1B Info Guestbook

Biju Thomas is Oracle7.3 OCP, Oracle8 OCP, 
Oracle8i OCP and Oracle9i OCA/OCP Certified DBA

Questions/Comments? Write to webmaster@bijoos.com. The scripts, tips and articles appearing on BijooS.com cannot be reproduced elsewhere without the prior permission from the webmaster.