BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation








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.sqlThis 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.sqlThis 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

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.


# First, we must set the environment . . . .
ORACLE_HOME=`cat /etc/oratab|grep ^$ORACLE_SID:|cut -f2 -d':'`
export PATH
$ORACLE_HOME/bin/sqlplus system/manager<<!

select * from v\$database;
connect perfstat/perfstat
define losnapid=$2
define hisnapid=$3



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.



# **********************************************
#  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" ]
   echo "Usage: sprem.ksh <ORACLE_SID>"
   exit 99


#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 PATH

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

select min(snap_id) + 24

delete from
  snap_id <

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.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.



Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software







BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.

Hit Counter