Database Reference
In-Depth Information
SQL> SELECT * FROM v$waitstat;
CLASS COUNT TIME
------------------ ---------- ----------
data block 102011 5162
sort block 0 0
save undo block 0 0
segment header 76053 719
save undo header 0 0
free list 3265 12
extent map 0 0
1st level bmb 6318 352
2nd level bmb 185 3
3rd level bmb 0 0
bitmap block 0 0
bitmap index block 0 0
file header block 389 2069
unused 0 0
system undo header 1 1
system undo block 0 0
undo header 3244 70
undo block 38 2
■
the essential thing to understand about the
v$waitstat
view is that the
class
column is referring to the block
type, not to the type of data or structure on which the wait occurred. For example, if there's contention due to freelists
contained in a segment header block, the waits are reported under the
segment header
class and not under the
free
list
class. in fact, the
free list
class is used for blocks storing only freelist information (such blocks are created when
FREELIST GROUPS
is set to a value greater than 1). another example is given by indexes. if there's contention for a block
that stores an index, the waits are reported under the
data block
class.
Note
For the reasons just explained, the following two sections provide examples of identification based on SQL Trace
and the
v$session
view (based on Snapper; in this case, Active Session History isn't very suitable because the
test I'm using only runs for a few seconds). The block contention used in these examples were generated with the
buffer_busy_waits.sql
script.
Using SQL Trace
I advise using TVD$XTAT to process the trace file generated by the
buffer_busy_waits.sql
script. I recommend this
even though you can use either TKPROF or TVD$XTAT. This is because TKPROF doesn't provide all information that
you require to troubleshoot a block contention issue. Specifically, it doesn't provide information about the blocks
experiencing
buffer busy waits
.
The output file generated by TVD$XTAT for the current example, which is available, with the trace file it's based
on, in the
buffer_busy_waits.zip
file, shows that one SQL statement, an
UPDATE
statement, is responsible for
almost the whole response time. The following excerpt shows the
UPDATE
statement's execution statistics. For 10,000
executions, an elapsed time of 6.187 seconds and a CPU time of 2.411 seconds were measured.
