Database Reference
In-Depth Information
The following query shows the actual values (in this case, the default values in place after the installation) of each
parameter:
SQL> SELECT parameter, value
2 FROM stats$statspack_parameter
3 UNPIVOT (
4 value FOR
5 parameter IN (snap_level, executions_th, parse_calls_th, disk_reads_th, buffer_gets_th,
6 sharable_mem_th, version_count_th, seg_phy_reads_th, seg_log_reads_th,
7 seg_buff_busy_th, seg_rowlock_w_th, seg_itl_waits_th, seg_cr_bks_rc_th,
8 seg_cu_bks_rc_th)
9 );
PARAMETER VALUE
---------------- -------
SNAP_LEVEL 5
EXECUTIONS_TH 100
PARSE_CALLS_TH 1000
DISK_READS_TH 1000
BUFFER_GETS_TH 10000
SHARABLE_MEM_TH 1048576
VERSION_COUNT_TH 20
SEG_PHY_READS_TH 1000
SEG_LOG_READS_TH 10000
SEG_BUFF_BUSY_TH 100
SEG_ROWLOCK_W_TH 100
SEG_ITL_WAITS_TH 100
SEG_CR_BKS_RC_TH 1000
SEG_CU_BKS_RC_TH 1000
To modify the value of one of the parameters, the
statspack
package provides the
modify_statspack_parameter
procedure. For each Statspack parameter, the procedure has an input parameter. For example, the following call
changes the snapshot level to 6:
statspack.modify_statspack_parameter(i_snap_level => 6)
Taking and Purging Snapshots
To take a snapshot, you have to call one of the
snap
subroutines of the
statspack
package. Two subroutines exist: a
function and a procedure. Both of them accept an input parameter for every configuration parameter described in the
previous section. All parameters are optional, so you can simply take a snapshot without specifying any parameter,
like this:
perfstat.statspack.snap();
To purge one or several snapshots, provided that they aren't defined as baselines, you have to call one of the
purge
subroutines of the
statspack
package. Eight subroutines exist. On the one hand, the same functionality is
implemented as a function and as a procedure. On the other hand, four methods are available to specify which
snapshots have to be deleted:
•
All snapshots between a begin and end snapshot ID (parameters
i_begin_snap
and
i_end_snap
)
