gc buffer busy acquire and gc buffer busy release

These two wait events are similar to ”buffer busy wait” that you might have observed which is recorded when a session tries to access a data block/buffer which is already being accessed by another session that is connected to the same instance and buffer is also in the current instance’s buffer cache. In RAC environment, if a session tries to read a buffer from a remote instance’s buffer cache but the buffer is already being read by a different session, a “gc buffer busy acquire” wait event is recorded. If a session tries to read a buffer from the buffer cache of local instance, but buffer is already being read into remote instance’s buffer cache from local cache by a session connected to that remote instance, a “gc buffer busy release” wait event is recorded.  

Buffer busy wait, gc buffer busy acquire and gc buffer busy release waits keep happening all the time, but if they are listed among the top wait events in AWR report, tuning would be needed. Being at the top means that there are “hot blocks” in the database that are being accessed by several sessions simultaneously.  

 I the following I will discuss a real time scenario that I faced and the root cause of the issue.

See following snapshot of an AWR that shows “gc buffer busy acquire” at the top. Same wait event was also at the top in the AWR report of the other instance.

In my experience, most of the time *buffer busy* wait events are caused because of excessive FULL TABLE SCANS that cause entire table blocks to become “hot”, and also cause huge physical reads most of the time. Full table scans are also slower as compared to index based data access. In current case I found the same root cause. The section “SQL ordered by reads” of AWR listed the top SQLs doing physical reads, and the top SQLs were those that were doing full table scans to the huge tables. I found same SQLs on instance 2 as well under same section. This proved that these SQLs were the cause of “hot blocks” because they were accessing entire tables’ blocks again and again and therefore causing *buffer busy” wait events to be at the top on both instances’ AWR reports

Following output from DBA_HIST_SQL_PLAN shows that fir SQL form the list was doing full table scan of a very huge table. I am not showing similar output for other SQLs, but other SQLs were also doing full table scans of other huge tables.

SQL> select distinct operation,options,object_owner,object_name from dba_hist_sql_plan where sql_id='3kffqv73dgf4s' and operation='TABLE ACCESS' and options='FULL'; OPERATION         OPTIONS        OBJECT_OWNER       OBJECT_NAME ---------------------------------------------- ---------------------------------------------- TABLE ACCESS      FULL           TABLE_OWNER        HUGE_TABLE SQL> select count(*) from TABLE_OWNER.HUGE_TABLE;   COUNT(*) ----------  100459632

To reduce these *buffer busy* waits, creating proper indexes on the tables could help vanish these waits from the list of top wait events. As a result, significant performance improvement should be observed.