Databases Reference
In-Depth Information
Collecting different amounts of data
We can configure Statspack to collect more or less data. The
LEVEL
parameter can be
used to instruct the tool about the kind of information we want to store in the snapshot.
The following table summarizes the available levels (the default level is 5):
Level
Description
0
General performance statistics
5
Additional data: High resource usage SQL statements
6
Additional data: SQL Plans and SQL Plan usage
information for high resource usage SQL statements
7
Additional data: Segment level statistics including logical
and physical reads, row locks, and so on
10
Additional statistics: Parent and Child latches
We can use a different level parameter for a single snapshot, passing the corresponding
level to the
STATSPACK.snap
procedure:
EXEC STATSPACK.snap(i_snap_level=>10);
If we want our selection made permanent for subsequent snapshots, we add another
parameter to the procedure:
EXEC STATSPACK.snap(i_snap_level=>6, i_modify_parameter=>'true');
If we want to change the level of the snapshots without taking one, we will use the following
statement:
EXECUTE STATSPACK.modify_statspack_parameter(i_snap_level=>6);
Producing a report on a specific SQL
Statspack provides another script,
sprepsql.sql
, which allows us to elaborate a more
detailed report on a specific SQL statement.
If we find a statement in the Statspack report that we want to investigate deeper, we can
launch this script, indicating the beginning and ending snapshots, and the "Old Hash Value"
(a pre-10
g
memory) of the SQL statement on which we want to elaborate the report.
