Database Reference
In-Depth Information
Send request for a block to LMS
process
Set a timer for 0.5 seconds
(In Windows timer is 6s)
Timer
expired
Account time to
gc <cr| current> lost block;
Resend request.
Block / Grant
received ? or timer expired?
Block / Grant
received
Continue normal
processing
Figure 9-7.
gc lost block logic
The SQL Trace file of a process stuck waiting for a block is as follows. The foreground process is waiting for a
block with file#=33, block#=1701. You can see the approximate wait time of 0.5 seconds in the
ela=
field for these
waits. After 0.5 seconds of sleep, the process re-requests for the block, but the block is not received in that interval,
going into a continuous sleep-resend cycle. (In this problem scenario, due to a switch issue, few blocks were blocked
by the switch never arriving to the requestor.)
*** 2012-04-05 10:51:47.280
WAIT #47390186351424: nam='gc cr block lost' ela= 520692 p1=33 p2=1701 p3=1 obj#=409860
*** 2012-04-05 10:51:48.318
WAIT #47390186351424: nam='gc cr block lost' ela= 516782 p1=33 p2=1701 p3=1 obj#=409860
WAIT #47390186351424: nam='gc cr block lost' ela= 523749 p1=33 p2=1701 p3=1 obj#=409860
...
Parameter
_side_channel_batch_timeout_ms
controls the duration of sleep in a database instance and defaults
to 0.5 seconds (prior to 10gR2, this parameter default value was 6 seconds) in the Unix platform. On Windows,
another parameter,
_side_channel_batch_timeout
, is used with a default value of 6 seconds. There is no reason to
modify any of these parameters.
It is always important to quantify the effect of a wait event; in a perfectly healthy database, you can expect a few
waits for lost block-related wait events, and you should ignore those events if the DB time spent waiting for lost blocks
is negligible. Also, it is possible for another event such as gc current block receive time or gc cr block receive time to be
elevated due to the loss of blocks.
Figure
9-8
shows an AWR report for the duration of a few minutes; 62% of DB time was spent in waiting for
gc cr block lost
event. As the percentage of DB time spent on this wait event is very high, this database must be
reviewed further to identify the root cause.
