Buffer busy waits Vs GC buffer busy waits

1. Buffer busy waits (NON RAC) – waiting for buffer because buffer already pinned by other session
2. GC buffer busy waits (RAC) – Divided into 2 types
   • Gc buffer busy acquire – waiting for buffer because buffer is being read by another session in local instance
   • Gc buffer busy release  – waiting for buffer because buffer is being read by another session in remote instance

Prepare the environment

• Create table test3, test4 and test5
• Test3

drop table test3;

create table test3
(emp_id number,
dept_id number);

insert into test3
SELECT LEVEL,
       ((MOD (ROWNUM, 2)+1)*10)
FROM DUAL
CONNECT BY LEVEL < 100;

select count(*) from test3;
  COUNT(*)
----------
        99

• Test4

drop table test4;
create table test4
(emp_id number,
dept_id number);
select count(*) from test4;
  COUNT(*)
----------
         0

• test5

drop table test5;
create table test5
(emp_id number,
dept_id number);
select count(*) from test5;
  COUNT(*)
----------
         0

Demo

Buffer busy wait

We will simulate a scenario where two sessions from local instance will try to access same set of blocks.

1.  Session 1
   • Run update statement which will keep updating same set of rows (99 rows) of test3 table 1000000 times

BEGIN
FOR i IN 1 .. 1000000
LOOP
update test3 set DEPT_ID =i ;
commit;
END LOOP;
END;
/

1. Session 2
   • Run some form of select statement on test3 which will try to use all rows of test3 table

BEGIN
FOR i IN 1 .. 1000000
LOOP
insert into test4 select * from test3;
commit;
END LOOP;
END;
/

• Session 3 monitor the wait events
  • you can see that session 1 (sid=43, update statement) and session 2 (sid=80, insert statement) both witnessing “buffer busy wait” event randonly

set lines 150
col username for a10
col event for a30 

select inst_id,sid,serial#,USERNAME ,STATUS,SQL_ID,EVENT,STATE,SECONDS_IN_WAIT,WAIT_TIME from gv$session where sid in (43,80); 

Gc buffer busy acquire & Gc buffer busy release

We will simulate a scenario where multiple sessions from local and remote instance of RAC will try to access same set of blocks.

1. session 1 (sid =43, instance 1, update on test3)

• session 2 (sid =80, instance 1, select from test3 using “insert into”)

• session 3 (sid =63, instance 1, update on test3)

• session 4 (sid =76, instance 2, update on test3)

• session 5 (sid =77, instance 2, select from test3 using “insert into”)

• Monitor the session – you can see “gc buffer busy acquire” and “gc buffer busy release” wait events randomly from both instance sessions.

select inst_id,sid,USERNAME ,STATUS,SQL_ID,EVENT from gv$session where sid in (43,77,63,76,80) order by sid;