|
|
|
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!
|
