Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Remove Statspack Snapshots
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.
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!
#!/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
mailx -s "$HOST-$ORACLE_SID-$f" "$TO" < $f.lst
fi
rm -f $f 2> /dev/null
rm -f $f.lst 2> /dev/null
done
define report_name=spreport.txt
define begin_snap=&1
define end_snap=&2
@$ORACLE_HOME/rdbms/admin/spreport.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.
>>
>>===============================================================
>>=============
-- 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