Statspack

Database Statistics

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

Different levels of Statspack for monitoring:

level 0 General Performance Stats : Wait stats, system events, system stats, rollback seg
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.
1. No of executions of the SQL statement. Default: 100
2. No of disk reads performed by the SQL statement. Default: 1,000
3. No of parse calls performed by the SQL statement. Default: 1,000
4. No of buffer gets performed by the SQL statement. Default: 10,000
5. Size of the sharable memory used by the SQL statement. Default: 1 MB
6. 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 segs that are heavily accessed and contended, thus, modifying the physical layout
of some seg or of the tablespaces they reside in.
1. Logical Reads
2. DB block changes
3. Physical Reads. Default: 10,000
4. Physical Writes. Default: 1,000
5. Physical Reads direct
6. Physical Writes direct
7. Global cache consistent read blocks served (RAC specific). Default: 1,000
8. Global cache current blocks served (RAC specific). Default: 1,000
9. Buffer busy waits. Default: 100
10. ITL waits. Default: 100
11. 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> define default_tablespace='TOOLS'
SQL> define temporary_tablespace='TEMP'
SQL> define perfstat_password='my_perfstat_password'
SQL> @?/rdbms/admin/spcreate
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);