Remote DBA Plans
Remote DBA Service

Back-Porting STATSPACK

Oracle Tips by Burleson Consulting

(1)Back-Porting STATSPACK for Oracle 8.0 through 8.1.5

STATSPACK was officially introduced with Oracle 8.1.6, but it can be back-ported to run on Oracle 8.0–Oracle 8.1.5. If you are planning to use STATSPACK with pre-8.1.6 versions of Oracle, you will need the following modifications:

· statscbps.sql This script adds a v$buffer_pool_statistics view that is required for pre-8.1.6 versions of Oracle. This script should be run after connect internal and before running the statscre.sql script.

· statsrep80.sql This is the only STATSPACK supplied report for Oracle 8.0–8.1.5.

These SQL scripts to back-port STATSPACK can be downloaded from http://www.oracle.com/oramag/oracle/00-Mar/statspack-other.html.

CAUTION: While you can back-port STATSPACK to Oracle 8.0 releases by using the statsrep80.sql report, there are some known problems with the utility. The report from STATSPACK on an Oracle release level prior to 8.1.6 has known problems with the data and cannot always be trusted. Check Oracle MetaLink for details about the reporting problems.

(1)Removing Old STATSPACK Snapshots

At some point, your collection of STATSPACK data will be too old to be useful. Some shops choose to roll up old STATSPACK data into average values and store these summaries in extensions to the STATSPACK tables, and we will be presenting scripts in later chapters for that purpose.

(2)Using the STATSPACK purge utility

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

(2)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:

L 3-16

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:

L 3-17

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.

(2)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.

Once assigned, the script can be run and it will not prompt for the snapshot values. The sample script below can be referenced from a crontab file to ensure that older snapshots are always removed.

run_snap_purge.ksh

#!/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

In this example, the script is passed three parameters: the ORACLE_SID, the low snapshot, and the high snapshot. Below, we direct STATSPACK to remove snapshots 3,300 through 3,345 from the PROD database:

root> run_snap_purge.ksh PROD 3300 3345

(3)A basic UNIX method for batch removal of snapshots

Below is a simple UNIX script to remove oldest week’s worth of snapshots. This script removes snapshots 24 at a time because we do not want to encounter problems with the rollback segments.

In many cases, this script is preferable to the Oracle-supplied STATSPACK snapshot removal script (sppurge.sql) because it can be placed into a crontab file to run weekly and automatically remove the oldest weeks snapshots.

sprem.ksh

#!/bin/ksh

# **********************************************
#
# This will purge the 168 oldest snapshots
# (7 oldest days worth @ one snapshot per hour)
#
# This should be cronned weekly
#
# sample crontab entry
#
# 00 7 1 * * /export/home/oracle/sprem.ksh MY_SID > /export/home/r.lst
#
#   4/3/01 By Donald K. Burleson
#
# **********************************************

# First, we must set the environment . . . .
# First, we must set the environment . . . .
# Exit if no first parameter $1
if [ -z "$1" ]
then
   echo "Usage: sprem.ksh <ORACLE_SID>"
   exit 99
fi

ORACLE_SID=$1
export ORACLE_SID

#ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
ORACLE_HOME=`cat /var/opt/oracle/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:$PATH
export PATH

for i in `echo 1 2 3 4 5 6 7` # This is to spare the rollback segs
do
$ORACLE_HOME/bin/sqlplus -s perfstat/perfstat<<!

select min(snap_id) + 24
   from
      perfstat.stats\$snapshot;

delete from
   perfstat.stats\$snapshot
where
snap_id <
(select
      min(snap_id)+24
   from
      perfstat.stats\$snapshot)
;
commit;
exit
!
done

This is an excerpt from "Oracle9i High Performance tuning with STATSPACK" by Oracle Press.

Get the Complete
Oracle Tuning Details

The landmark book "Oracle Tuning: The Definitive Reference Second Edition" has been updated with over 1,500 pages of expert performance tuning tips. It's packed with scripts and tools to hypercharge Oracle 11g performance and you can buy it for 40% off directly from the publisher.