Statspack Pocket Reference

STATSPACK Pocket Reference

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

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 segments that are heavily accessed and contended, thus, modifying the physical layout of some segments 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> 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 X NUMBER; BEGIN SYS.DBMS_JOB.SUBMIT ( job => X ,what => 'statspack.snap;' ,next_date => to_date('07/04/2011 00:00:00','dd/mm/yyyy hh24:mi:ss') ,interval => 'trunc(SYSDATE+1/24,''HH'')' ,no_parse => TRUE ,instance => 1 ,force => TRUE ); SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x)); 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);