Statspack in Brief

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);