RE: Remove Statspack Snapshots

From: Post, Ethan <epost_at_kcc.com>
Date: Mon, 24 Sep 2001 11:03:27 -0700
Message-ID: <F001.00396980.20010924105518@fatcity.com>

It is part of a more complete package of utilities that I created for in house use only. It is not too hard to create however you only need a few components.

Shell script to monitor a directory for .sql files. Have the script spool output adn check for data, if it finds it email it. I will include a copy (sqlmon.sh) of this below. My script also sources some environmentment variables from a file.
A .sql file (statspack.sql) that uses define to set report name, start snapshot it and end snapshot it. File can accepts each of these as parameters. Then it should run spreport.sql
Stored precedure to write a out a .sql file to the directory being monitored using UTL_FILE. The file will contain a call to the .sql file mentioned in step 2.

Then you can write a procedure that takes a snapshot, waits N minutes takes and ending snapshot, use the snapshot it'd to write out .sql file to process the snapshots. You will need to know a little about PL/SQL and shell to make sense of all this. Good luck!

Ethan Post
http://www.geocities.com/epost1
- sqlmon.sh ****

#!/usr/bin/ksh

ENVDIR=/home/oracle/shark

. $ENVDIR/env.sh

cd $SQLDIR

if [ $# -eq 1 ]; then

if [ -r $1 ]; then

cp $1 .
fi
fi
for f in `ls -ur *.sql 2> /dev/null`
do

SQL=`cat $f`
sqlplus -s 2> /dev/null <<EOF

      $USER/$PASS
      set term off
      set feedback off
      set pagesize 1000
      spool $f.lst
      set heading on
      $SQL
      spool off

EOF
if [ -s $f.lst ]; then

mailx -s "$HOST-$ORACLE_SID-$f" "$TO" < $f.lst fi
rm -f $f 2> /dev/null
rm -f $f.lst 2> /dev/null
done

end sqlmon.sh ****
statspack.sql ****

define report_name=spreport.txt
define begin_snap=&1
define end_snap=&2
@$ORACLE_HOME/rdbms/admin/spreport.sql

end statspack.sql ****

>-----Original Message-----
>From: Janardhana Babu [mailto:jbdonga_at_ucdavis.edu]
>Sent: Monday, September 24, 2001 10:36 AM
>To: 'Post, Ethan'
>Subject: RE: Remove Statspack Snapshots
>
>
>Ethan,
>
>Could you please send the automated scripts to me sothat I can
>make my life
>easier.
>
>Thanks,
>-- Janardhana Babu
>
>-----Original Message-----
>From: Post, Ethan [mailto:epost_at_kcc.com]
>Sent: Monday, September 24, 2001 10:35 AM
>To: Multiple recipients of list ORACLE-L
>Subject: RE: Remove Statspack Snapshots
>
>
>How are you using the 168 snapshots to gather information? I
>saw a tool out
>there that will turn your statspack info into a graphical
>representation. I
>would certainly hate pouring through tons of output from
>spreport.sql. I
>have my own performance monitor for watching most of this
>stuff. I have
>automated the ability to schedule a statspack snapshot. The report is
>processed automatically and sent to me via email. This allows me to do
>things like place a trigger on a job queue table for our ERP
>system that
>takes a snapshot before and after a specific job. A few
>minutes after the
>job runs I get a statpack report in my inbox. This way I can
>help diagnose
>problems with the job without constantly being in contact with the
>application consultants about when they will run the job next.
>
>- Ethan Post
>- http://www.geocities.com/epost1
>
>>disclosure under law. If you have received this message in
>>error, please
>>inform us promptly by reply e-mail, then delete the e-mail and
>>destroy any
>>printed copy. Thank you.
>>
>>===============================================================
>>=============

This e-mail is intended for the use of the addressee(s) only and may contain privileged, confidential, or proprietary information that is exempt from disclosure under law. If you have received this message in error, please inform us promptly by reply e-mail, then delete the e-mail and destroy any printed copy. Thank you.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Post, Ethan
  INET: epost_at_kcc.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Mon Sep 24 2001 - 13:03:27 CDT