STATSPACK Pocket Reference

Database Statistics
Statspack is different from BSTAT/ESTAT
Statspack collects more data, including high-resource SQL
It precalculates many ratios such as cache hit ratios, rates and transaction statistics
It uses permanent tables owned by PERFSTAT user to store performance stats
It separates data collection from report generation
It makes data collection easy to automate using either DBMS_JOB or an OS utility to schedule collection tasks



Different levels of Statspack for monitoring:

level 0 - General Performance Stats : Wait stats, system events, system stats, rollback segment data, row cache, SGA, bg events, lock stats, buffer pool stats and parent latch stats

level 5 - SQL statements with high level resource usage. Larger the shared pool more time it takes to take a snapshot.
No of executions of the SQL statement. Default: 100
No of disk reads performed by the SQL statement. Default: 1,000
No of parse calls performed by the SQL statement. Default: 1,000
No of buffer gets performed by the SQL statement. Default: 10,000
Size of the sharable memory used by the SQL statement. Default: 1 MB
Version count for the SQL statement. Default: 20
level 6 - All the statistics + SQL Plans & SQL Plan usage. Pre-requisite is, the plan of the statement which is to be gathered has to be in the shared pool at the time that snapshot is taken and it must exceed one of the SQL thresholds & specify the executions thresholds to 0 for those snapshots.

level 7 - Lists all the segments that are heavily accessed and contended, thus, modifying the physical layout of some segments or of the tablespaces they reside in.
Logical Reads
DB block changes
Physical Reads. Default: 10,000
Physical Writes. Default: 1,000
Physical Reads direct
Physical Writes direct
Global cache consistent read blocks served (RAC specific). Default: 1,000
Global cache current blocks served (RAC specific). Default: 1,000
Buffer busy waits. Default: 100
ITL waits. Default: 100
Row lock waits. Default: 100
level 10 - Parent & child latch information, session specific stats can be calculated by specifying the session id in the call to the Statspack.

To install Statspack run the following script: SPCREATE.SQL
To remove Statspack run the following script: SPDROP.SQL
Statspack reporting script: SPREPORT.SQL
Statspack reporting script for the specific SQL hash value specified: SPREPSQL.SQL
Statspack reporting script for the database and instance specified: SPREPINS.SQL
Statspack script for automating Statspack stats collection: SPAUTO.SQL
Statspack script for converting data from 9.0 to 9.2. Before running this backup the schema: SPUP90.SQL
Statspack script for upgrading data from 8.1.7: SPUP817.SQL
Statspack script for upgrading data from 8.1.6: SPUP816.SQL
Statspack script for purging a limited range of snapshot IDs for a given database instance: SPPURGE.SQL
Statspack script for truncating all performance data in Statspack tables: SPTRUNC.SQL
Statspack documentation: SPDOC.TXT

How to install and take a snapshot:
Set timed_statistics to TRUE to get the time at which the data was collected.

SQL> CONNECT / AS SYSDBA
SQL> CREATE TABLESPACE STATSPACK DATAFILE ‘C:\ORACLE9I\ORADATA\ALEX\STATS01.DBF’
SIZE 500M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
SQL> define default_tablespace=’STATSPACK’
SQL> define temporary_tablespace=’TEMP’
SQL> define perfstat_password=’my_perfstat_password’
SQL> @?/rdbms/admin/spcreate
SQL> DECLARE
jobno number;
BEGIN
DBMS_JOB.SUBMIT(
job => jobno,
what => ‘BEGIN statspack.snap; END;’,
next_date => sysdate + 1/24/60,
interval => ‘/*15:Min*/ sysdate + 15/24/60′);
COMMIT;
END;
SQL> CONNECT perfstat/my_perfstat_password
SQL> EXECUTE statspack.snap;

How to create a Statspack Report Without Prompts

SQL> connect perfstat/my_perfstat_password
SQL> define begin_snap=1
SQL> define end_snap=2
SQL> define report_name=batch_run
SQL> @?/rdbms/admin/spreport

How to gather optimizer stats on the PERFSTAT Schema:

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS (OWNAME=>?PERFSTAT?, CASCADE=.TRUE):
or
EXECUTE DBMS_UTILITY.ANALYZE_SHCEMA (?PERFSTAT?,?COMPUTE?);

Where to find the snapshot level and threshold information used by the package:STATS$STATSPACK_PARAMETER

Temporarily using new values at snapshot level and take a snapshot:
EXECUTE STATSPACK.SNAP (i_snap_level=>6);

Saving the values of the snapshot level permanently and take a snapshot:
EXECUTE STATSPACK.SNAP (i_snap_level=>6, i_modify_parameter=>?true?);

Saving the values of the snapshot level permanently without taking a snapshot:
EXECUTE STATSPACK.MODIFY_STATSPACK_PARAMETER
(i_snap_level=>6, i_buffer_gets_th=>10000, i_disk_reads_th=>1000);