gc buffer busy acquire/release wait event in Oracle RAC Database

In an Oracle Database, the GC buffer busy acquire wait event occurs when a session tries to acquire a buffer from the buffer cache and the buffer is currently being used by another session.
The GC buffer busy release wait event occurs when a session tries to release a buffer back to the buffer cache and the buffer is currently being used by another session.

So, basically these events occur when there is contention for buffers in the buffer cache. This can happen when there are not enough buffers available to accommodate all the sessions that need them, or when a session is holding on to a buffer for an extended period of time.

In general to fix these events:

Increase the buffer cache size to accommodate more buffers.
Tune the SQL statements that are causing contention for the buffers.
Tune the indexes to reduce the I/O and reduce the number of blocks that need to be in the buffer cache.
Monitor and Kill the sessions that hold the buffer lock for too long.
Increase the degree of parallelism.

Also, as such, it is important to know that GC buffer busy acquires and releases are normal and expected events in a busy Oracle database, but when they occur frequently and stay for a longer time, it can indicate a performance problem.

Here are some SQL scripts you can use to identify the sessions that are causing the GC buffer busy acquire and release wait events:

To find the sessions that are currently waiting on the GC buffer busy acquire event:

SELECT s.sid, s.username, s.status, s.program, s.machine, s.module, s.event, s.p1, s.p2, s.p3
FROM v$session s, v$session_wait w
WHERE s.sid = w.sid
AND w.event = 'gc buffer busy acquire';

To find the sessions that are currently waiting on the GC buffer busy release event:

SELECT s.sid, s.username, s.status, s.program, s.machine, s.module, s.event, s.p1, s.p2, s.p3
FROM v$session s, v$session_wait w
WHERE s.sid = w.sid
AND w.event = 'gc buffer busy release';

To find the sessions that are currently holding locks on the GC buffer busy:

SELECT s.sid, s.username, s.status, s.program, s.machine, s.module, s.event, s.p1, s.p2, s.p3
FROM v$session s, v$lock l
WHERE s.sid = l.sid
AND l.type = 'buffer busy waits';

To find the object that is causing the GC buffer busy waits:

SELECT s.sid, s.username, s.status, s.program, s.machine, s.module, s.event, s.p1, s.p2, s.p3, 
l.id1, l.id2, o.object_name, o.object_type
FROM v$session s, v$lock l, dba_objects o
WHERE s.sid = l.sid
AND l.type = 'buffer busy waits'
AND o.object_id = l.id1
AND o.data_object_id = l.id2;

To find the total number of GC buffer busy acquire and release waits, grouped by the object they are waiting on:

WITH waits AS
(SELECT p1 "file#", p2 "block#", SUM(time_waited) "total_time_waited"
FROM v$session_wait
WHERE event = 'gc buffer busy acquire' OR event = 'gc buffer busy release'
GROUP BY p1, p2)
SELECT o.object_name, o.object_type, w.file#, w.block#, w.total_time_waited
FROM waits w, dba_objects o
WHERE o.data_object_id = w.block# AND o.data_object_id = w.file#;

To find the average wait time for GC buffer busy acquire and release waits, grouped by the object they are waiting on:

WITH waits AS
(SELECT p1 "file#", p2 "block#", SUM(time_waited) "total_time_waited",
COUNT(*) "total_waits"
FROM v$session_wait
WHERE event = 'gc buffer busy acquire' OR event = 'gc buffer busy release'
GROUP BY p1, p2)
SELECT o.object_name, o.object_type, w.file#, w.block#,
w.total_time_waited / w.total_waits "avg_wait_time"
FROM waits w, dba_objects o
WHERE o.data_object_id = w.block# AND o.data_object_id = w.file#
ORDER BY w.total_time_waited / w.total_waits;

To find the sessions that are currently waiting on GC buffer busy acquire or release events, along with the SQL statement they are executing:

SELECT s.sid, s.username, s.status, s.program, s.machine, s.module, s.event,
s.p1, s.p2, s.p3, sq.sql_text
FROM v$session s, v$session_wait w, v$sql sq
WHERE s.sid = w.sid
AND s.sql_id = sq.sql_id
AND (w.event = 'gc buffer busy acquire' OR w.event = 'gc buffer busy release');

To find the top 10 SQL statements that have the most GC buffer busy acquire and release waits:

WITH waits AS
(SELECT s.sql_id, SUM(time_waited) "total_time_waited"
FROM v$session s, v$session_wait w
WHERE s.sid = w.sid
AND (w.event = 'gc buffer busy acquire' OR w.event = 'gc buffer busy release')
GROUP BY s.sql_id)
SELECT sq.sql_text, w.total_time_waited
FROM waits w, v$sql sq
WHERE w.sql_id = sq.sql_id
ORDER BY w.total_time_waited DESC
FETCH FIRST 10 ROWS ONLY;

To identify the top 10 sessions that are currently waiting on GC buffer busy acquire and release waits:

SELECT s.sid, s.serial#, s.username, s.status, w.event, w.time_waited
FROM v$session s, v$session_wait w
WHERE s.sid = w.sid
AND (w.event = 'gc buffer busy acquire' OR w.event = 'gc buffer busy release')
ORDER BY w.time_waited DESC
FETCH FIRST 10 ROWS ONLY;

To identify the top 10 segments that are currently causing GC buffer busy acquire and release waits:

WITH waits AS
(SELECT s.sid, s.serial#, s.username, s.status, w.event, w.time_waited
FROM v$session s, v$session_wait w
WHERE s.sid = w.sid
AND (w.event = 'gc buffer busy acquire' OR w.event = 'gc buffer busy release')
ORDER BY w.time_waited DESC
FETCH FIRST 10 ROWS ONLY)
SELECT w.event, w.time_waited, seg.owner, seg.segment_name
FROM waits w, dba_extents seg
WHERE seg.file_id = w.p1
AND seg.block_id = w.p2;

Author
Recent Posts

Brijesh Gogia

I’m an experienced Cloud/Oracle Applications/DBA Architect with more than 15 years of full-time DBA/Architect experience. I have gained wide knowledge on Oracle and Non-Oracle software stack running on-prem and on Cloud and have worked on several big projects for multi-national companies. I enjoy working with leading-edge technology and have a passion for Cloud architecture, automation, database performance, and stability. Thankfully my work allows me time for researching new technologies (and to write about them).

gc buffer busy acquire/release wait event in Oracle RAC Database

Related posts:

One Comment