These tables
contain your SQL source code, and if you set your snapshot
thresholds too generously, you could consume a gigabyte of disk in
just a few months.
One of the most
common problems with STATSPACK is when the volume of data reaches
the file size limit for the STATSPACK tables.
The intent of
AWR and STATSPACK is that the DBA will intelligently set their
thresholds, depending on the "exception threshold" for the system.
The exception threshold is determined by finding the averages for
the whole application, adding a plus/minus tolerance factor.
The snapshot thresholds only apply to the SQL
statements that are captured in the stats$sql_summary table. The
stats$sql_summary table can easily become the largest tables in the
STATSPACK schema because each snapshot might collect several hundred
rows, one for each SQL statement that was in the library cache at the
time of the snapshot.
In the
Automatic Workload Repository (AWR), the executions_th ,
parse_calls_th , disk_read_th , buffer_gets_th , sharable_mem_th ,
and version_count_th SQL collection threshold settings allow the DBA
to set thresholds for SQL statements. If any of the thresholds are
exceeded, the information will be stored by STATSPACK in the
repository.
The thresholds are stored in the
stats$statspack_parameter table. Let's take a look at each threshold.
The names are different for different releases of STATSPACK and AWR:
Oracle 8 (backported) through
Oracle8i:
·
executions_th This
is the number of executions of the SQL statement (default is 100).
·
disk_reads_th This
is the number of disk reads performed by the SQL statement (default is
1,000).
·
parse_calls_th This
is the number of parse calls performed by the SQL statement (default
is 1,000).
·
buffer_gets_th This
is the number of buffer gets performed by the SQL statement (default
is 10,000).
Oracle9i and beyond:
In Oracle9i and
beyond STATSPACK (and the
AWR thresholds), there are new threshold parameters:
p_def_num_sql – This is a threshold for the number of SQL
statements.
p_def_executions_th – This is a threshold for the number of SQL
executions.
p_def_parse_calls_th – This is a threshold for the number of
SQL parse calls.
p_def_disk_reads_th – This is a threshold for the number of
disk reads.
p_def_buffer_gets_th – This is a threshold for the number of
SQL buffer gets.
p_def_sharable_mem_th – This is a threshold for the SQL
sharable memory.
p_def_version_count_th – This is a threshold for the SQL
child cursors.
The above STATSPACK parameters can be
set manually using the statspack.modify_statspack_parameter
procedure:
SQL> desc statspack
PROCEDURE MODIFY_STATSPACK_PARAMETER
Argument Name Type In/Out Default?
--------------------- ------------- ------ --------
I_DBID NUMBER IN DEFAULT
I_INSTANCE_NUMBER NUMBER IN DEFAULT
I_SNAP_LEVEL NUMBER IN DEFAULT
I_SESSION_ID NUMBER IN DEFAULT
I_UCOMMENT VARCHAR2 IN DEFAULT
I_NUM_SQL NUMBER IN DEFAULT
I_EXECUTIONS_TH NUMBER IN DEFAULT
I_PARSE_CALLS_TH NUMBER IN DEFAULT
I_DISK_READS_TH NUMBER IN DEFAULT
I_BUFFER_GETS_TH NUMBER IN DEFAULT
I_SHARABLE_MEM_TH NUMBER IN DEFAULT
I_VERSION_COUNT_TH NUMBER IN DEFAULT
I_ALL_INIT VARCHAR2 IN DEFAULT
I_PIN_STATSPACK VARCHAR2 IN DEFAULT
I_MODIFY_PARAMETER VARCHAR2 IN DEFAULT
The executions_th , parse_calls_th ,
disk_read_th , buffer_gets_th , sharable_mem_th , and
version_count_th settings allow the DBA to set thresholds for SQL
statements. If any of the thresholds are exceeded, the information
will be stored by STATSPACK in the repository.
The above STATSPACK parameters can be set manually using the
statspack.modify_statspack_parameter procedure:
