How does STATSPACK work?
Statspack is a set of SQL, PL/SQL and SQL*Plus scripts which allow the collection, automation, storage and viewing of performance data.  A user is automatically created by the installation script - this user, PERFSTAT, owns all objects needed by this package

Once snapshots are taken, it is possible to run the performance report. The performance report will prompt for the two snapshot id's the report will process.  The report produced calculates the activity on the instance between the two snapshot periods specified.

1. Configuration
The default initial and next extent size is 1MB or 5MB for all tables and indexes which contain changeable data.  The minimum default tablespace requirement is approximately 65MB.

Dictionary Managed Tablespaces
  If you install the package in a dictionary-managed tablespace, Oracle suggests you monitor the space used by the objects created, and adjust the storage clauses of the segments, if required.

Locally Managed Tablespaces
  If you install the package in a locally-managed tablespace, storage clauses are not required, as the storage characteristics are automatically managed.

To install the package, either change directory to the ORACLE_HOME rdbms/admin directory, or fully specify the ORACLE_HOME/rdbms/admin directory when calling the installation script, spcreate. To run the installation script, you must use SQL*Plus and connect as a user with SYSDBA privilege.  Do not use Server Manager (svrmgrl) to install Statspack, as the installation will fail.
    e.g.  Start SQL*Plus, then:
      on Unix:
        $ cd $ORACLE_HOME/rdbms/admin
        SQL>  connect / as sysdba
        SQL>  @spcreate

      on NT:
        SQL>  connect / as sysdba
        SQL>  @%ORACLE_HOME%\rdbms\admin\spcreate

Check each of the three output files produced (spcusr.lis, spctab.lis, spcpkg.lis) by the installation to ensure no errors were encountered, before continuing on to the next step.

Errors during installation
A common error made during Statspack installation is running the install script from Server Manager (svrmgrl) rather than from SQL*Plus.  If you use svrmgrl, the installation will fail.  To correctly install Statspack after such errors, first run the de-install script, then the install script.  Both scripts must be run from SQL*Plus.
    e.g.  Start SQL*Plus, connect as a user with SYSDBA privilege, then:
       SQL> @spdrop
       SQL> @spcreate

2. Gathering data - taking a snapshot
The simplest interactive way to take a snapshot is to login to SQL*Plus as the PERFSTAT user, and execute the procedure statspack.snap:
      SQL>  connect perfstat/perfstat
      SQL>  execute statspack.snap;

  Note:  In an OPS environment, you must connect to the instance you wish to collect data for.

This will store the current values for the performance statistics in the STATSPACK tables, and can be used as a baseline snapshot for comparison with another snapshot taken at a later time.

For better performance analysis, set the init.ora parameter timed_statistics to true;  this way, Statspack data collected will include important timing information.  The timed_statistics parameter is also dynamically changable using the 'alter system' command.  Timing data is important and is usually required by Oracle support to diagnose performance problems.

2.1 Automating statistics gathering
To be able to make comparisons of performance from one day, week or year to the next, there must be multiple snapshots taken over a period of time.
The best method to gather snapshots is to automate the collection on a regular time interval.  It is possible to do this:
    - within the database, using the Oracle dbms_job procedure to schedule the snapshots
    - using Operating System utlities (such as 'cron' on Unix or 'at' on NT) to schedule the snapshot

2.2.  Using dbms_job
To use an Oracle-automated method for collecting statistics, you can use dbms_job. 
A sample script on how to do this is supplied in spauto.sql, which schedules a snapshot every hour, on the hour.
You may wish to schedule snapshots at regular times each day to reflect your system's OLTP and/or batch peak loads. For example take snapshots at 9am, 10am, 11am, 12 midday and 6pm for the OLTP load, then a snapshot at 12 midnight and  another at 6am for the batch window.
In order to use dbms_job to schedule snapshots, the job_queue_processes initialization parameter must be set to greater than 0 in the init.ora file for the job to be run automatically.
Example of an init.ora entry:
    #  Set to enable the job queue process to start.  This allows dbms_job
    #  to schedule automatic statistics collection using STATSPACK

If using spauto.sql in OPS enviroment, the spauto.sql script must be run once on each instance in the cluster.  Similarly, the  job_queue_processes parameter must also be set for each instance. 

Changing the interval of statistics collection
To change the interval of statistics collection use the dbms_job.interval procedure e.g.
    execute dbms_job.interval(1,'SYSDATE+(1/48)');

Where 'SYSDATE+(1/48)' will result in the statistics being gathered each1/48 hours (i.e. every half hour).

To force the job to run immediately,
    execute<job number>);

To remove the autocollect job,
    execute dbms_job.remove(<job number>);

3.   Running a Performance report
Once snapshots are taken, it is possible to generate a performance report. The SQL script which generates the report prompts for the two snapshot id's to be processed.
The first will be the beginning snapshot id, the second will be the ending snapshot id.  The report will then calculate and print ratios, increases etc. for all statistics between the two snapshot periods, in a similar way to the BSTAT/ESTAT report.

Note:  It is not correct to specify begin and end snapshots where the
         begin snapshot and end snapshot were taken from different
         instance startups.  In other words, the instance must not have
         been shutdown between the times that the begin and end snapshots
         were taken.

You will be prompted for:
    1. The beginning snapshot Id
    2. The ending    snapshot Id
    3. The name of the report text file to be created

    e.g. on Unix
      $ cd $ORACLE_HOME/rdbms/admin
      SQL>  connect perfstat/perfstat
      SQL>  @spreport

    e.g. on NT
      SQL>  connect perfstat/perfstat
      SQL>  @%ORACLE_HOME%\rdbms\admin\spreport

Gathering Optimizer statistics on the PERFSTAT schema
For best performance when running spreport, collect optimizer statistics for tables and indexes owned by the PERFSTAT.  This should be performed whenever significant change in data volumes in PERFSTAT's tables. The easiest way to do this, is either to use dbms_utility, or dbms_stats, and specify the PERFSTAT user:
      execute dbms_utility.analyze_schema('PERFSTAT','COMPUTE');
      execute dbms_stats.gather_schema_stats('PERFSTAT');

4.  Configuring the amount of data captured
Snapshot Level
It is possible to change the amount of information gathered by the package, by specifying a different snapshot 'level'.  In other words, the level chosen (or defaulted) will decide the amount of data collected. The higher the snapshot level, the more data is gathered.  The default level set by the installation is level 5.

Snapshot Levels - details
Levels  >= 0   General performance statistics
    Statistics gathered:
    This level and any level greater than 0 collects general
    performance statistics, such as: wait statistics, system events,
    system statistics, rollback segment data, row cache, SGA,
    background events, session events, lock statistics,
    buffer pool statistics, parent latch statistics.

 Levels  >= 5  Additional data:  SQL Statements
    This level includes all statistics gathered in the lower level(s),
    and additionally gathers the performance data on high resource
    usage SQL statements.

    In a level 5 snapshot, note that the time required for the snapshot
    to complete is dependant on the shared_pool_size and on the number of
    SQL statements in the shared pool at the time the snapshot is taken:
    the larger the shared pool, the longer the time taken to complete
    the snapshot.

    SQL 'Thresholds'
       The SQL statements gathered by Statspack are those which exceed one of
       four predefined threshold parameters:
        - number of executions of the SQL statement            (default 100)
        - number of disk reads performed by the SQL statement  (default 1,000)
        - number of parse calls performed by the SQL statement (default 1,000)
        - number of buffer gets performed by the SQL statement (default 10,000)
        - size of sharable memory used by the SQL statement    (default 1m)
        - version count for the SQL statement                  (default 20)

       The values of each of these threshold parameters are used when
       deciding which SQL statements to collect - if a SQL statement's
       resource usage exceeds any one of the above threshold values, it
       is captured during the snapshot.
       The SQL threshold levels used are either those stored in the table
       stats$statspack_parameter, or by the thresholds specified when
       the snapshot is taken.

 Levels  >= 10 Additional statistics:  Parent and Child latches
    This level includes all statistics gathered in the lower levels, and
    additionally gathers Parent and Child Latch information.  Data gathered
    at this level can sometimes cause the snapshot to take longer to complete
    i.e. this level can be resource intensive, and should only be used
    when advised by Oracle personnel.

Purging/removing unnecessary data
It is possible to purge unnecessary data from the PERFSTAT schema using sppurge.sql.   This script deletes snapshots which fall between the begin and end range of Snapshot Id's specified.
Purging may require the use of a large rollback segment, as all data relating each Snapshot Id to be purged will be deleted. To avoid rollback segment extension errors, explicitly use a large rollback segment.  This can be done by executing the 'set transaction use rollback segment..' command before running the sppurge.sql script

Truncating all data
If you wish to truncate all performance data indiscriminantly, it is possible to do this using sptrunc.sql  This script truncates all statistics data gathered.

Removing the package
To deinstall the package, connect as a user with SYSDBA privilege and run the following script from SQL*Plus:  spdrop
      SQL>  connect / as sysdba
      SQL>  @spdrop

Check each of two output files produced (spdtab.lis, spdusr.lis) to ensure the package was completely deinstalled.

Automatic Purge of Old Statspack Reports - Method 1
To delete data automatically, you can add this:
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-14;

right before:
--  Post warning
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.
prompt You may wish to export this data before continuing.

and save that script as something ELSE (not sppurge.sql), then running that script will automatically delete all snapshots older then two weeks old. 
You can add an EXIT at the end of this new script and then use OEM or your OS job scheduler to schedule this script to run every friday.

Automatic Purge of Old Statspack Reports - Method2
Below is a script that may be run via cron which will purge all old snapshots that exceed the specified count.  This script requires no knowledge of the password for the PERFSTAT user.  The script has been tested with Oracle 8.1.7 and 9.2.0.

To implement this script, do the following:

1)      Save the script (below) as sp_purge.ksh to each Unix machine on which the Oracle instance may be located.
2)      If the Unix tmp directory is anything other than /tmp then you will need to modify the script accordingly.
3)      If your oratab file is located in any directory other than /var/opt/oracle then you will need to update the script accordingly.
4)      Set execute privilege on the script:  chmod u+x sp_purge.ksh
5)      Establish a cron job to call the script.  The script requires three parameters:

· The name of the database in which to purge snapshots.
· The maximum number of snapshots to retain.
· The email recipient for success messages.

Here is an example cron entry:
00 19 * * 1-5 /scripts/sp_purge.ksh  prod  60  >>/tmp/sp_purge_portal.log 2>&1 &

This entry causes the script to run at 19:00 each weekday, to retain no more than 60 snapshots for the ‘prod’ database, and send success messages to:

6)      Note that this script may be invoked on any machine on which the instance may run.  If the instance is not on the current machine, then a simple message to that effect will be sent to a file in the tmp directory.
7)      Note also that all log files are written to the tmp directory.

Automatic StatsPack snapshot purge script:
#   Script Name:  sp_purge.ksh
#   This script is designed to purge StatsPack snapshots.
#      Parameter $1 is the name of the database.
#      Parameter $2 is the maximum number of snapshots to retain.
#      Parameter $3 is the mail recipient for success messages.
#   To succeed, this script must be run on the machine on which the
#   instance is running.
#   Example for calling this script:
#      sp_purge.ksh prod 30
#   Script History:
#   Who             Date         Action
#   --------------- ------------ --------------------------------------------
#   Mark J. Rogers  22-Sep-2003  Script creation.
# Validate the parameters.
if [[ $# -ne 3 ]]; then
   echo ""
   echo "*** ERROR: You must specify these parameters: "
   echo ""
   echo "             1: the name of the database"
   echo "             2: the maximum # of snapshots to retain"
   echo "             3: the mail recipient for success messages"
   echo ""
   exit 1

grep "^${1}:" /var/opt/oracle/oratab >> /dev/null

if [[ $? -ne 0 ]]; then
   echo ""
   echo "*** ERROR: The ORACLE_SID specified in parameter 1 is not a valid SID."
   echo "    (Note that the SID is case sensitive.)"
   echo ""
   exit 1

if [[ ! (${2} -ge 0) ]]; then
   echo ""
   echo "*** ERROR: Parameter 2 must specify the # of snapshots to retain."
   echo ""
   exit 1

# Ensure that the instance is running on the current machine.
ps -ef | grep pmon | grep $1 >> /dev/null

if [[ $? -ne 0 ]]; then
   echo ""
   echo "*** ERROR: Instance $1 is not running on machine `uname -n` "
   echo "    on `date`."
   echo "    The instance must be running on the current machine for this"
   echo "    script to function properly."
   echo ""
   echo "    Exiting..."
   echo ""
   exit 1

# Establish error handling for this UNIX script.
function errtrap {
   echo ""
   echo "   *** ERROR: Error message $the_status occured on line number $1."
   echo ""
   echo "   *** The script is aborting."
   echo ""
   exit $the_status
trap  \
'  \
errtrap $LINENO  \
'  \

# Set up the Oracle environment.
export ORACLE_SID=${1}
. oraenv

echo ""
echo "Script: $script_name"
echo "   started on: `date`"
echo "   by user: `id`"
echo "   on machine: `uname -n`"
echo ""
echo "This script is designed to purge StatsPack snapshots for the "
echo "   $ORACLE_SID database."
echo ""
echo "You have requested to retain no more than $2 StatsPack snapshots."
echo ""

tmp_script=${tmp_dir}/sp_purge_tmp_$ORACLE_SID.ksh  # script to actually purge
tmp_output=${tmp_dir}/sp_purge_tmp_$ORACLE_SID.out  # output to be mailed

rm -f $tmp_script
rm -f $tmp_output

sqlplus -s <<EOF_SP
/ as sysdba

whenever sqlerror exit failure rollback
whenever oserror exit failure rollback



   /* Assign values to these variables. */
   :P_SNAPS_TO_RETAIN := ${2};
   :P_LOSNAPID := -1;
   :P_HISNAPID := -1;

-- Identify the snapshot ids to purge, if any.
   V_LOSNAPID             NUMBER := NULL;  -- Low snapshot ID to purge.
   V_HISNAPID             NUMBER := NULL;  -- High snapshot ID to purge.
   V_COUNT                NUMBER := NULL;  -- Number of snapshots current saved.
   V_COUNTER              NUMBER := 0;     -- Temporary counter variable.
   V_DBID                 NUMBER := NULL;  -- Current database ID.
   V_INSTANCE_NUMBER      NUMBER := NULL;  -- Current instance number.
   V_SNAPS_TO_RETAIN    NUMBER := :P_SNAPS_TO_RETAIN; -- Max snaps to retain.
   select d.dbid, i.instance_number
   from v\$database d, v\$instance i;

   select count(snap_id)
   into v_count
   from perfstat.stats\$snapshot
   where  dbid = V_DBID AND
          instance_number = V_INSTANCE_NUMBER;

      -- We do NOT need to perform a purge.
      DBMS_OUTPUT.PUT_LINE ('NOTE: There are only ' ||
       to_char(v_count) || ' snapshots currently saved.');
      -- We DO need to perform a purge.
      DBMS_OUTPUT.PUT_LINE ('There are currently ' ||
       to_char(v_count) || ' snapshots saved.');
      -- Obtain the low snapshot id to be purged.
      select min(snap_id)
      into V_LOSNAPID
      from perfstat.stats\$snapshot
      where dbid = V_DBID AND
            instance_number = V_INSTANCE_NUMBER;

      -- Obtain the high snapshot id to be purged.
         (SELECT SNAP_ID
          FROM perfstat.stats\$snapshot
          WHERE dbid = V_DBID AND
                instance_number = V_INSTANCE_NUMBER
         V_COUNTER := V_COUNTER + 1;
            EXIT;   -- Exit this LOOP and proceed to the next statement.
         END IF;
      END LOOP;

      :P_LOSNAPID := V_LOSNAPID;     
      :P_HISNAPID := V_HISNAPID;     
   END IF;

-- Generate the specific purge script.
set linesize 60
spool $tmp_script
   IF (:P_LOSNAPID <> -1) THEN
      /* Build the script to purge the StatsPack snapshots. */
      dbms_output.put_line('#THIS IS THE SCRIPT TO ACTUALLY PERFORM THE PURGE');
      dbms_output.put_line('trap '' exit \$? '' ERR');
      dbms_output.put_line('sqlplus -s << SP_EOF2');
      dbms_output.put_line('/ as sysdba');
      dbms_output.put_line('whenever sqlerror exit failure rollback');
      dbms_output.put_line('whenever oserror exit failure rollback');
      dbms_output.put_line('@ \$ORACLE_HOME/rdbms/admin/sppurge.sql');
      dbms_output.put_line('-- the following are needed again');
      dbms_output.put_line('whenever sqlerror exit failure rollback');
      dbms_output.put_line('whenever oserror exit failure rollback');
      dbms_output.put_line('exit \$?');
   END IF;
spool off

if [[ ! (-f ${tmp_script}) ]]; then
   echo ""
   echo "*** ERROR: Temporary script: ${tmp_script} does not exist."
   echo ""
   exit 1

if [[ `cat ${tmp_script} | wc -l` -ne 0 ]]; then
   #   Execute the newly generated StatsPack snapshot purge script.
   chmod u+x $tmp_script
   echo ""
   echo "Performing the purge..."
   echo ""
   $tmp_script > $tmp_output
   cat $tmp_output   # display the output
   #  Check the output file for a success message:
   trap ' ' ERR  # temporarily reset error handling for the grep command
   grep "^Purge of specified Snapshot range complete." $tmp_output >> /dev/null
   if [[ $? -ne 0 ]]; then
      echo ""
      echo "*** ERROR: The purge did not complete successfully."
      echo "           Check the log file $tmp_output."
      echo ""
      exit 1
   trap ' errtrap $LINENO ' ERR  # re-establish desired error handler
   #   No purge script was created.
   echo "No snapshot purge was necessary." > $tmp_output

echo ""
echo "The ${script_name} script appears to have completed "
echo " successfully on `date`."
echo ""

mailx \
   -s "sp_purge.ksh in $ORACLE_SID on `uname -n` completed successfully"  \
   ${3} \
   < $tmp_output
# End of script sp_purge.ksh.