Tuesday, October 25, 2016

"GC BUFFER BUSY" wait event explained

When a session wants to modify a buffer, which is cached in the remote instances, it waits on the buffer busy global cache event. This is similar to buffer busy waits in single instance architecture. This wait happens at cache layer. In other words, the session is waiting for a buffer in local cache, which is busy because it is waiting for a global cache request to complete. The waits at the RAC layer are accounted in the global cache busy wait event.

The interconnect statistics look good so this looks like a hot block issue.

You may want to investigate a hot block issue.

To resolve contention for this latch, you must first identify the segments that the blocks are associated with by doing the following 

Find the "hottest" child latches (usually top 5): 
SELECT addr, latch#, sleeps 
FROM v$latch_children 
WHERE name = 'cache buffers chains' 
AND sleeps > 0 
ORDER BY sleeps DESC 

Find the buffers associated with the hot latches: 
SELECT hladdr, dbarfil, dbablk, tch FROM x$bh 
WHERE hladdr = '&child_latch_addr' -- <== run this for each of the hot latches 
ORDER BY tch DESC 

Find the segments associated with the hot buffers: 
SELECT owner, segment_name, partition_name, segment_type, tablespace_name, 
relative_fno, block_id, blocks 
FROM dba_extents e 
WHERE &Block_No BETWEEN e.block_id AND (e.block_id + blocks) 
AND e.relative_fno = &File_no 

To relieve contention on the segments listed above: 

1) On rare occasions, a larger buffer cache may help relieve contention; if there is sufficient memory available, increase the buffer cache by 20% and check if the contention on this latch is reduced. (This almost never helps, but its simple to try). 

2) Examine SQL statements with a high number of buffer gets and tune them to reduce the number of buffer gets per execution or change the application to execute the statements less frequently. 

3) Alter (or even rebuild) tables listed above to use a higher PCTFREE setting. This will reduce the number of rows per block and hopefully, spread out contention for the blocks (at the expense of wasting space). 

4) If the hot blocks are index leaf blocks, there may be contention due to key values that are increasing steadly (using a sequence). Look at using reverse key indexes (if range scans aren't commonly used against the segment). Or, consider changing the application to use natural keys, partitioning or otherwise reduce the dependence on the sequence-generated keys.

No comments:

Post a Comment