Statspack

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
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.
on Unix:
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus / as sysdba
SQL>  @spcreate

on Windows:
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
To correctly install Statspack after an error, 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:
sqlplus perfstat
SQL>  exec statspack.snap;

Note:  In an RAC 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.

We recommend to change some of the Defaults Parameters for Statspack with the following sentence:
sqlplus perfstat
SQL>  EXEC STATSPACK.MODIFY_STATSPACK_PARAMETER(i_snap_level=>5, i_buffer_gets_th=>100000, i_modify_parameter=>'true');
SQL>  exec statspack.snap;

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
    job_queue_processes=1

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(job_number,'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 dbms_job.run(<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 report will then calculate and print ratios, increases etc. for all statistics between the two snapshot periods.

Note:  It is not correct to get stats bwhen the DB has been restarted between 2 snapshots.
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   (also sprepsql.sql)

e.g. on Windows
SQL>  connect perfstat/perfstat
SQL>  @%ORACLE_HOME%\rdbms\admin\spreport  (also sprepsql.sql)

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');
or
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.
 

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
e.g. on Unix
$ cd $ORACLE_HOME/rdbms/admin
SQL>  connect perfstat/perfstat
SQL>  @spdrop.sql

e.g. on Windows
SQL>  connect perfstat/perfstat
SQL>  @%ORACLE_HOME%\rdbms\admin\spdrop.sql

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

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
 

Because STATSPACK uses FK referential integrity constraints with the ON CASCADE DELETE option, then I can delete from the stats$snapshot table and all associated data is removed.
So I create a little procedure:

create PROCEDURE "PERFSTAT"."PURGE_SNAPS" as begin
   --Delete Yesterdays data
   delete from perfstat.stats$snapshot
      where snap_time < trunc(sysdate-1);
   commit;
end;
/

Then I create a little DB Job, to call that procedure everyday at 4 AM:
VARIABLE v_jobnum NUMBER;
BEGIN
  DBMS_JOB.SUBMIT(:v_jobnum,'PERFSTAT.PURGE_SNAPS;', trunc(sysdate+1)+4/24, 'trunc(sysdate)+28/24' );
END;
/
commit;

I know that I could execute the delete statement directly from the DB job, but with that procedure in place, I can use it to execute it manually.

Automatic Purge of Old Statspack Reports - Method 2
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
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.
prompt
prompt You may wish to export this data before continuing.
prompt

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 - Method 3
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.

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:

Here is an example cron entry:
00 19 * * 1-5 /scripts/sp_purge.ksh  prod  60  username@company.com  >>/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: username@company.com

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:
-----------------------CUT----------CUT----------CUT-------------------------
#!/bin/ksh
#   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 username@mycompany.com
#   Script History:
#
#   Who             Date         Action
#   --------------- ------------ --------------------------------------------
#   Mark J. Rogers  22-Sep-2003  Script creation.
#
#
#
tmp_dir=/tmp
# 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
fi

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
fi

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

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

# Establish error handling for this UNIX script.
function errtrap {
   the_status=$?
   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  \
'  \
ERR

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

script_name=${0##*/}
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

SET SERVEROUTPUT ON
SET FEEDBACK OFF

VARIABLE P_SNAPS_TO_RETAIN   NUMBER
VARIABLE P_LOSNAPID          NUMBER
VARIABLE P_HISNAPID          NUMBER

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

-- Identify the snapshot ids to purge, if any.
DECLARE
   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.
 
BEGIN
   select d.dbid, i.instance_number
   INTO v_DBID, V_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;

   IF V_COUNT <= V_SNAPS_TO_RETAIN THEN
      -- We do NOT need to perform a purge.
      DBMS_OUTPUT.PUT_LINE ('NOTE: There are only ' ||
       to_char(v_count) || ' snapshots currently saved.');
   ELSE
      -- 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.
      FOR V_HISNAPID_REC IN
         (SELECT SNAP_ID
          FROM perfstat.stats\$snapshot
          WHERE dbid = V_DBID AND
                instance_number = V_INSTANCE_NUMBER
          ORDER BY SNAP_ID DESC)
      LOOP
         V_COUNTER := V_COUNTER + 1;
         IF V_COUNTER > V_SNAPS_TO_RETAIN THEN
            V_HISNAPID := V_HISNAPID_REC.SNAP_ID;
            EXIT;   -- Exit this LOOP and proceed to the next statement.
         END IF;
      END LOOP;

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

prompt
-- Generate the specific purge script.
set linesize 60
spool $tmp_script
begin
   IF (:P_LOSNAPID <> -1) THEN
      /* Build the script to purge the StatsPack snapshots. */
      dbms_output.put_line('#!/bin/ksh');
      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(:P_LOSNAPID);
      dbms_output.put_line(:P_HISNAPID);
      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('commit;');
      dbms_output.put_line('exit');
      dbms_output.put_line('SP_EOF2');
      dbms_output.put_line('exit \$?');
   END IF;
end;
/
spool off
exit
EOF_SP

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

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
   fi
   trap ' errtrap $LINENO ' ERR  # re-establish desired error handler
else
   #   No purge script was created.
   echo "No snapshot purge was necessary." > $tmp_output
fi

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

Tools to Analyze Statspack Reports
http://www.oraperf.com 
http://www.txmemsys.com/statspack-reg.htm (Statspack Analyzer)
http://www.dbapool.com/dbanalyzer.php (Analyze your AWR or Statspack)
http://www.softpedia.com/get/Internet/Servers/Database-Utils/spReporter.shtml  (Download Tool to Analyze AWR or Statspack Reports)
http://www.ondatafine.com/  (web based application. It processes plain-text statspack or AWR)
http://www.spviewer.com/index.html (STATSPACK and AWR Viewer software)

Scripts: http://www.evdbt.com/tools.htm