Database Reference
In-Depth Information
All the statistics calculated and their sources are summarized in Table 27-1.
Table 27-1.
Statistics
Statistic
Source
cr
,
cu
, and
p
in
PARSE
,
EXEC
, and
FETCH
entries
Buffer cache hit ratio
cr
in
PARSE
,
EXEC
, and
FETCH
entries
Consistent gets
parameter
mis
in
PARSE
entries
Cursor hits and misses
cu
in
PARSE
,
EXEC
, and
FETCH
entries
Db block gets
WAIT
entries with
nam='enqueue'
or
'enq:
enqueue_details
'
, where
enqueue_details
contains the name of an enqueue and a short
description (e.g.,
nam='enq: TX - contention'
)
Enqueue waits
WAIT
entries with
nam='latch free'
or
'latch
:
latch_details
'
, where
latch_details
contains the name of a latch and a short description
(e.g.,
nam='latch: cache buffers chains'
)
Latch waits
p
in
PARSE
,
EXEC
, and
FETCH
entries
Physical reads
r
in
PARSE
,
EXEC
, and
FETCH
entries
Rows processed
p3
(Oracle9
i
and prior releases) or blocks (Oracle10
g
) of the wait
events
db file sequential read
and
db file scattered read
Single block and
multi block reads
R and
XCTEND rlbk=0, rd_only=0
Transactions per second
The small trace file used in the previous case study did not contain any waits for enqueues
or latches. Hence the latch wait and enqueue wait sections in the preceding code example are
empty. Following is an excerpt of an ESQLTRCPROF report, which resulted from tracing data-
base sessions that were concurrently enqueuing messages into an Advanced Queuing queue
table. The excerpt illustrates that the sessions were contending for both latches and enqueues.
Latch wait breakdown
------------------------
row cache objects waits: 1 sleeps: 0
library cache pin waits: 2 sleeps: 0
commit callback allocation waits: 1 sleeps: 0
cache buffers chains waits: 2 sleeps: 1
dml lock allocation waits: 1 sleeps: 0
library cache waits: 9 sleeps: 4
enqueue hash chains waits: 1 sleeps: 0
Enqueue wait breakdown (enqueue name, lock mode)
------------------------------------------------
HW,X waits: 4
TX,S waits: 7
The enqueue HW was requested in exclusive mode (X), whereas the enqueue TX was
requested in shared mode (S). The enqueue HW (high water mark) is requested when a segment
