Tip#2 Setup statspack automated purging

Setup for Statspack

I generally setup a cron job to collect the stats and also make sure that I dont keep ages old stats so I cleanup/purge it after certain days e.g. in following setup I purge all stats which are older than 90 days. This is how I would set up,

STEP 1 : Create PERFSTAT tablespace

Something like this ...

CREATE TABLESPACE PERFSTAT DATAFILE
'/oradata_path/perfstat01.dbf' SIZE 2048M AUTOEXTEND OFF
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO;

STEP 2 : Create perfstat user and related objects

cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
connect / as sysdba
@spcreate

STEP 3 : Customized the sppurge.sql

copy $ORACLE_HOME/rdbms/admin/sppurge.sql in your scripts directory (I also rename it to sppurge_customised.sql ) and Change it to include

column min_snap_id new_val LoSnapId
column max_snap_id new_val HiSnapId
select min(s.snap_id) min_snap_id, max(s.snap_id) max_snap_id
from stats$snapshot s
, stats$database_instance di
where s.dbid = :dbid
and di.dbid = :dbid
and s.instance_number = :inst_num
and di.instance_number = :inst_num
and di.startup_time = s.startup_time
and s.snap_time < sysdate - 90; -- purge anything older than 90 days


BEFORE the following code in the script

--
-- Post warning

prompt
prompt
prompt Warning
prompt ~~~~~~~
prompt sppurge.sql deletes all snapshots ranging between the lower and
prompt upper bound Snapshot Id's specified, for the database instance
prompt you are connected to.


STEP 4 : Automate the collection and the purging

Set up a cron job to execute the following script every hour. Please note that the "sppurge_customised.sql" is executed only once in a day i.e. at midnight.

#!/bin/bash
if pgrep -f ora_
then
. /home/oracle/.bash_profile
SCRIPTPATH=/app/oracle/admin/scripts
LOGFILE=$SCRIPTPATH/LOGS
ORACLE_SID=mysid
export ORACLE_SID

cd $SCRIPTPATH
echo Starting Statsupdate at `date` >> $LOGFILE/statspack.log

# only purge older stats once a day at midnight
cuurent_hour=`date +%H`
if [ $cuurent_hour == "00" ]; then
sqlplus /nolog >> $LOGFILE/statspack.log << EOF
connect perfstat/perfstat
@sppurge_customised.sql
EOF
fi

sqlplus /nolog >> $LOGFILE/statspack.log << EOF
connect perfstat/perfstat
exec statspack.snap;
EOF

echo Finished at `date` >> $LOGFILE/statspack.log
fi