STATSPACK Collection Thresholds
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.
AWR uses a "Top N" method which defaults to collect the Top-30 SQL
statements for each SQL category, If you set statistics_level
= "all", AWR will collect the top 100 SQL statements.
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 Ion tool has a GUI interface that can be used to configure
STATSPACK settings.
Figure 5.3:
Setting up STATSPACK
parameters in The Ion tool.
In Oracle10g, the use of STATSPACK requires more configuration
settings than with the AWR. By default, the AWR requires no DBA
intervention. STATSPACK has to be configured to work properly.
While the AWR is fully automated with regard to statistic history
storage, it also allows the DBA to take new snapshots manually using
the
dbms_workload_repository.create_snapshot
procedure:
SQL> desc dbms_workload_repository
…
PROCEDURE CREATE_SNAPSHOT
Argument Name Type In/Out Default?
------------------------------ ------------ ------ --------
FLUSH_LEVEL VARCHAR2 IN DEFAULT
FUNCTION CREATE_SNAPSHOT RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ------------ ------ --------
FLUSH_LEVEL VARCHAR2 IN DEFAULT
The single parameter,
flush_level, establishes the amount of information the AWR gathers. This
parameter can have either the default value of TYPICAL or a value of
ALL. When the statistics level is set to ALL, the AWR gathers the
maximum amount of performance data. Usually, the TYPICAL level is
quite enough for performance analysis and tuning purposes. The AWR
uses the SPFILE initialization parameter,
statistics_level , which
specifies the level for snapshots gathered by the MMON process. The
possible values for this parameter are the same, TYPICAL and ALL.
The package,
dbms_workload_repository
, also has the overloaded function,
create_snapshot, which has the
same parameter level and performs the same job as
create_snapshot procedure except that it only returns
the number of the newly created snapshot.
In the STATSPACK utility, the
statspack.snap procedure must be executed manually as an Oracle job in order to
gather history for
v$ statistics on a
regular basis. The
statspack.snap
procedure must be called to take a new STATSPACK snapshot:
SQL> desc statspack
PROCEDURE SNAP
Argument Name Type In/Out 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 parameters for the
statspack.snap procedure allow the DBA to specify the level of statistics gathered
and the specific thresholds for the new snapshot. The settings
specified in the call of statspack.snap procedure will be valid only
for this single new snapshot.
Another essential difference between AWR and STATSPACK is the way
that old statistical history is removed from the database. The AWR
removes old snapshots from its repository based on the
retention parameter, which specifies
the length of time that any snapshot is stored in the database. DBAs
do not need to manually clear the database of old information.
SEE CODE DEPOT FOR FULL SCRIPTS
