How to find current Statspack level?
1. Look at table PERFSTST.STATS$SNAPSHOT
2. Run spreport.sql and you will notice it along with the snapids listed
execute statspack.snap (i_snap_level=> 7, i_modify_parameter=>’true’);
Levels >= 0 General Performance Statistics
Any level greater than 0 collects general performance statistics, such as wait statistics, system events, system statistics, rollback segment data, row cache, SGA, background events, session events, lock statistics, buffer pool statistics, and parent latch statistics.
Levels >= 5 Additional Data: SQL Statements
This level includes all statistics gathered in the lower level(s), as well as performance data on SQL statements with high resource usage. In a level 5 snapshot (or higher), the time required for the snapshot to complete depends on the SHARED_POOL_SIZE and on the number of SQL statements in the shared pool at the time of the snapshot. The larger the shared pool, the longer it takes to complete the snapshot.
Levels >= 6 Additional Data: SQL Plans and SQL Plan Usage
This level includes all statistics gathered in the lower levels, as well a SQL plans and plan usage data for each of the high-resource SQL statements captured.
A level 6 snapshot gathers valuable information for determining whether the execution plan used for a SQL statement has changed. Therefore, level 6 snapshots should be used whenever a plan might have changed.
Levels >= 10 Additional Statistics: Parent and Child Latches
This level includes all statistics gathered in the lower levels, as well as parent and child latch information. Sometimes data gathered at this level can cause the snapshot to take longer to complete. This level can be resource-intensive, and it should only be used when advised by Oracle personnel.
Statspack threshold parameters:
* Number of executions of the SQL statement (default 100)
* Number of disk reads performed by the SQL statement (default 1,000)
* Number of parse calls performed by the SQL statement (default 1,000)
* Number of buffer gets performed by the SQL statement (default 10,000)
* Size of sharable memory used by the SQL statement (default 1 Mb)
* Version count for the SQL statement (default 20)
The SQL threshold levels used are either those stored in the table STATS$STATSPACK_PARAMETER or by the thresholds specified when the snapshot is taken.
How to define statspack level ?
SQL> EXECUTE STATSPACK.SNAP(i_snap_level=>6);
Gather session statistics and wait events for a particular session
SQL> EXECUTE STATSPACK.SNAP(i_session_id=>3);
Set new value as instance’s default
SQL> EXECUTE STATSPACK.SNAP(i_snap_level=>10, i_modify_parameter=>’true’);