Statspack levels and thresholds
You can adjust the amount of information collected by Statspack by specifying a level or adjusting the threshold for data collection. A SQL statement must surpass the specified threshold for a statistic before it is captured by Statspack.
Statspack supports the following levels:
0or greater collects general statistics such as wait and system statistics, rollback segment data, row cache and SGA information, system, background, and session events, as well as lock, buffer pool, and parent latch statistics
5 or greater also includes performance data on SQL statements that exceed the thresholds specified
6 or greater also includes information about whether the execution plan has changed.
10 or greater also includes information about child and parent latches
The thresholds that can be specified for a statement, with their defaults, are:
- I_EXECUTIONS_TH
Number of times executed (100)
- I_DISK_READS_TH
Number of disk reads (1000)
- I_PARSE_CALLS_TH
Number of parse calls (1000)
- I_BUFFER_GETS_TH
Number of buffer gets (10,000)
- I_SHARABLE_MEM_TH
Amount of sharable memory (1,048,576)
- I_VERSION_COUNT_TH
Number of versions (20)
To change a parameter for an individual session, include the threshold or level number identifier followed by => and the new value as a parameter to the SNAP procedure. To set the level, you use the parameter name I_SNAP_LEVEL. You can also include I_UCOMMENT to add a user comment and I_SESSION_ID for the ID of the session to use to gather session-specific ...