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.
1. 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.
2. 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
3. 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:[EMAIL PROTECTED]]
>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:[EMAIL PROTECTED]]
>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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
