Statspack - Oracle 9i

To install statspack

Make sure you have a tablespace available (tools?) and know the temporary tablespace name.
Run $ORACLE_HOME/rdbms/admin/spcreate as sysdba (spdrop reverses it).
It creates the user PERFSTAT/PERFSTAT.

To collect statistics with statspack:

Check that TIMED_STATISTICS is set to TRUE (if its not ALTER SYSTEM SET timed_statistics = true)

execute statspack.snap

DBMS_JOB can be used to schedule statspack, the script spauto.sql schedules statspack to run once an hour (JOB_QUEUE_PROCESSES must be set to greater than 0).

To run reports:

There are 2 reports that can be run. spreport - which is a general health check, and an SQL report that is run to check a specific SQL statement.

To run the health check:
On Unix
connect perfstat/perfstat
@?/rdbms/admin/spreport

On NT
connect perfstat/perfstat
@%ORACLE_HOME%\rdbms\admin\spreport

It will prompt you for beginning snapshot id and ending shanpshot id and a output file name.

To run the SQLreport
sprepsql Prompts for beginning and ending snapshot id, output file name and hash value of sql statement (as shown in above report)

Configuring STATSPACK

Levels can be set, the higher the level the more data is collected, default is level 5.
EXECUTE STATSPACK.SNAP - (i_snap_level=>10, i_modify_parameter=>'true');, if the i_modify_parameter had been omitted or set to false the values would have not been saved to STATS$STATPACK_PARAMETER table and so the level would have only been used for that particular STATSPACK.

Levels >= 0 - General Performance Statistics.
Levels >= 5 - Includes performance data on high usage SQL statements, the time to complete is dependant on the SHARED_POOL_SIZE and the number of SQL statements in there at the time of the snapshot.
Levels >= 6 - Includes SQL plans and plan usage for each high resource SQL statement captured.
Levels >= 10 - Includes parent and child latch info, it can take a long time and use a lot of resources.
Parameters able to be passed in to the STATSPACK.SNAP and STATSPACK.MODIFY_STATSPACK_PARAMETER procedures are as follows: Parameters that can be passed to the STATSPACK.SNAP and STATSPACK.MODIFY_STATSPACK_PARAMETER.

i_snap_level: values 0, 5, 6, 10 (Snapshot Level)
i_ucomment: input text, (Comment to be stored with Snapshot)
i_executions_th: values Integer >=0, default 100, (SQL Threshold: number of times the statement was executed).
i_disk_reads_th: values Integer >=0,default 1,000, (SQL Threshold: number of disk reads the statement made)
i_parse_calls_th: values Integer >=0, default 1,000, (SQL Threshold: number of parse calls the statement made).
i_buffer_gets_th: values Integer >=0, default 10,000, (SQL Threshold: number of buffer gets the statement made).
i_sharable_mem_th: values Integer >=0, default 1048576, (SQL Threshold: amount of sharable memory).
i_version_count_th: values Integer >=0, default 20, (SQL Threshold: number of versions of a SQL statement i_session_id: values - valid sid, default 0 (no Session Id of the Oracle Session from session) to capture session granular v$session statistics for i_modify_parameter: values - True,False, default False, (Save the parameters specified for future snapshots?).

Removing STATSPACK data

sppurge - will purge all data between supplied snapshot ids.
sptrunc - will get rid of all data.

Monitoring

STATS$STATSPACK_PARAMETER, shows parameters and levels that are set for STATSPACK.