You can invoke the sppurge.sql script to remove specific snapshots
from STATSPACK. This script will prompt you for the snapshot to remove
and then issue the appropriate SQL to remove the specified snapshot.
When sppurge.sql is executed, all available snapshots are displayed.
You are then prompted for the low Snap Id and high Snap Id. All
snapshots which fall within this range will be purged. Note that
purging may require the use of a large rollback segment, as all data
relating each snapshot to be purged will be deleted. You can get
around the issue by issuing the alter session set rollback segment
command before running sppurge.sql. The example below shows a sample
execution of this script:
SQL> connect perfstat/perfstat
Connected.
SQL> set transaction use rollback segment big_rbs;
Session altered.
SQL> @$ORACLE_HOME/rdbms/admin/sppurge
Manually removing STATSPACK snapshots
Fortunately, STATSPACK uses foreign-key referential integrity
constraints with the ON CASCADE DELETE option. This means that all
information for a given snapshot can be deleted by deleting the
corresponding stats$snapshot record. For example, suppose that you
wanted to delete all snapshots for 2002, and these were snapshots that
have a snap_id of less than 10,000. The following DELETE would remove
all of these snapshots, and all subordinate detail rows:
SQL > delete from stats$snapshot where snap_id < 10000;
28923563 rows deleted.
Of course, you can selectively delete rows from STATSPACK. For
example, you might want to keep all of the system statistics and
delete all of the SQL statements that were more than six months old.
In this case, you could selectively remove the rows from
stats$sql_summary:
SQL > delete from stats$sql_summary where snap_time < sysdate - 180;
2888363 rows deleted.
Now let's take a look at some handy shell scripts that will make
your use of STATSPACK easier.
Removing ranges of snapshots
The STATSPACK utility provides a batch mode purging function. To
run the sppurge.sql script in batch mode, you must assign SQL*Plus
variables to identify the low snapshot and the high snapshot for
purging.
#!/bin/ksh
# First, we must set the environment . . . .
ORACLE_SID=$1
export ORACLE_SID
ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH
$ORACLE_HOME/bin/sqlplus system/manager<<!
select * from v\$database;
connect perfstat/perfstat
define losnapid=$2
define hisnapid=$3
@sppurge
exit
!
and include the URL for the page.
