Database Reference
In-Depth Information
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
CPU time 671 90.2
db file sequential read 1,525,262 36 0 4.9
db file scattered read 138,657 16 0 2.1
latch: library cache 7 15 2086 2.0
log file parallel write 5,187 3 1 .4
The lowest contribution to elapsed time comes from log file parallel write and is less than
1 percent, so in this case there is no need to change
top_n_events
. Table 25-2 lists the undocu-
mented Statspack report parameters of Oracle10
g
Release 2.
Table 25-2.
Undocumented Oracle10g Statspack Report Parameters
Statspack Report Parameter
Purpose
Default
Value
avwt_fmt
Displayed precision of average wait time
1 ms
cache_xfer_per_instance
Report per instance cache transfer statistics
Y
display_file_io
Report file-level I/O statistics
Y
display_undostat
Report undo segment statistics
Y
linesize_fmt
Controls the line size of SQL*Plus output and
should be increased if any columns are made wider
80
streams_top_n
Number of lines in Oracle Streams related statistics
(e.g., capture, propagation, and apply statistics)
25
top_n_events
Number of lines in the “Top Timed Events”
report section
9
5
top_n_undostat
Number of lines in undo statistics
35
Statspack Tables
In Oracle10
g
, the Statspack schema PERFSTAT contains 67 tables. Among these, only
STATS$ENQUEUE_STAT
and
STATS$STATSPACK_PARAMETER
are documented in the file
spdoc.txt
.
A Statspack schema contains a wealth of information on a database and the instance that
opens the database (or multiple instances in case of RAC). When troubleshooting performance
problems or malfunctions, it may be useful to query these tables directly to detect snapshots
with high resource utilization or to figure out when a problem occurred for the first time.
Table 25-3 contains a list of all tables, the V$ views used to populate each table, if any, and a
short explanation on the purpose of the table.
910
The variable
top_n_events
is defined in
sprepins.sql
not in
sprepcon.sql
.
9.
