Database Reference
In-Depth Information
Top 5 Timed Foreground Events
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Avg
wait % DB
Event Waits Time(s) (ms) time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
log file sync 2,054 23,720 11548 45.8 Commit
gc buffer busy acquire
19,505 10,382 532 20.0 Cluster
gc cr block busy 5,407 4,655 861 9.0 Cluster
enq: SQ - contention 140 3,432 24514 6.6 Configurat
db file sequential read 38,062 1,305 34 2.5 User I/O
To troubleshoot gc buffer busy wait events, initially review events suffered by the background processes. In many
production problems, slowness in the database background processes is the root cause of gc buffer busy event waits. It
is also important to understand the difference between
gc buffer busy acquire
and
gc buffer busy release
wait
events. The following output from v$session_wait view differentiates these two events nicely:
1.
Notice that session 53 is waiting to access the block with
file_id=10
and
block_id=560651
and that session is currently waiting for
gc current request
event (a placeholder wait
event as discussed).
2.
Session 47 connected to instance 2 is waiting for
log file sync
event. LMS process is also
waiting for
log file sync
wait event (not shown in the output, though).
3.
In instance 1, all other sessions trying to access that block with
file_id=10
and
block_id=560651
are waiting for
gc buffer busy acquire
wait event.
4.
Meanwhile, sessions connected to instance 2, which are trying to access that block, are
waiting for
gc buffer busy release
wait event.
INST_ID SID EVENT STATE WIS P1_P2_P3_TEXT
------- ------ ---------------------------- ------- ----- -------------------------------
1 53 gc current request WAITING 26 file# 10-block# 560651-...
1 40 gc buffer busy acquire WAITING 26 file# 10-block# 560651-class# 1
1 60 gc buffer busy acquire WAITING 26 file# 10-block# 560651-class# 1
1 59 gc buffer busy acquire WAITING 26 file# 10-block# 560651-class# 1
1 58 gc buffer busy acquire WAITING 26 file# 10-block# 560651-class# 1
...
2 1 gc buffer busy release WAITING 1 file# 10-block# 560651-class# 1
2 68 gc buffer busy release WAITING 1 file# 10-block# 560651-class# 1
2 65 gc buffer busy release WAITING 0 file# 10-block# 560651-class# 1
...
2 47 log file sync WAITING 22 buffer# 4450-sync scn 30839721-
2 43 gc buffer busy release WAITING 1 file# 10-block# 560651-class# 1
2 36 gc buffer busy release WAITING 0 file# 10-block# 560651-class# 1
...
Essentially, sessions connected to instance 1 are waiting for the global cache locks to be acquired by instance 1,
and so they account wait time to event
gc buffer busy acquire
. Sessions connected to instance 2 are waiting for
instance 1 to release the gc lock, and so time is accounted to
gc buffer busy release
event. As global cache BL
locks are considered to be owned by an instance, not by an individual session, in this example, sessions connected
to instance 1 are waiting for instance 1 to acquire the gc lock, and sessions connected to instance 2 are waiting for
instance 1 to release the gc lock.
