(1)Adjusting the STATSPACK
STATSPACK Collection Options
Oracle Tips by Burleson Consulting
As we have already mentioned, STATSPACK has two
types of collection options, level and threshold. The
level parameter controls the type of data collected from Oracle,
while the threshold parameter acts as a filter for the
collection of SQL statements into the stats$sql_summary table.
There are three snapshot levels used in
STATSPACK, and level 5 is the default:
Level 0: General Performance Statistics This
level 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.
Level 5: Add SQL Statements This
level includes all level 0 statistics plus SQL statements into the
Level 10: Add Child Latch Statistics The
level 10 snapshot includes everything in the level 5 statistics plus
the addition of child latches into the stats$latch_children table. You
rarely, if ever, need this level of detail, and you should only do a
level 10 snapshot when directed by Oracle technical support.
The default for a snapshot is always level 5,
but you can use the STATSPACK package to change the default or request
a special snapshot at a different level. You can change the default
level of a snapshot with the statspack.snap function. In the example
here, we remove all SQL collection from all future snapshots by
specifying a default level 1 snapshot:
execute statspack.snap -
Once set, all future snapshots will be level 0.
If you want a single snapshot at a different level without changing
the default, you just omit the i_modify_parameter argument. In the
next example, we take a single snapshot at level 10, while leaving the
default at level 5 for all subsequent snapshots:
execute statspack.snap -
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.
The thresholds are stored in the
stats$statspack_parameter table. Let's take
a look at each threshold:
is the number of executions of the SQL statement (default is 100).
is the number of disk reads performed by the SQL statement (default is
is the number of parse calls performed by the SQL statement (default
is the number of buffer gets performed by the SQL statement (default
(3)New Oracle9i SQL
In Oracle9i there are new threshold parameters,
but they have not yet been activated in STATSPACK.
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.
It is important to understand that each SQL
statement will be evaluated against all of these thresholds, and the
SQL statement will be included in the stats$sql_summary table if
any one of the thresholds is exceeded. In other words, these
thresholds are not AND'ed together as we might expect, but they are
OR'ed together such that any value exceeding any of the thresholds
will cause a row to be populated.
The main purpose of these thresholds is to
control the rapid growth of the stats$sql_summary table that will
occur when a highly active database has hundreds of SQL statements in
the library cache. In the next chapter, we will be discussing clean-up
strategies for removing unwanted snapshots from the database.
You can change the threshold defaults by
calling the statspack.modify_statspack_ parameter function. In the
example here, we change the default threshold for buffer_gets and
disk_reads to 100,000. In all subsequent snapshots, we will only see
SQL that exceeds 100,000 buffer gets or disk reads.
execute statspack.modify_statspack_parameter -
This is an excerpt from "Oracle9i
High Performance tuning with STATSPACK" by Oracle Press.
If you like Oracle tuning, you may enjoy the new book "Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today.