How to eliminate almost “gc buffer busy” wait event.

Using LIST partition, userenv(‘INSTANCE’) and add column, we can eliminate almost “gc buffer busy” wait event, in case of right hand growth index.

This is well documented at “gc buffer busy” wait event http://orainternals.wordpress.com/2010/09/27/gc-buffer-busy-waits/.
So, if you wonder about “gc buffer busy” wait event, this article is helpful to you. And now, I will explain another method to eliminate that wait event. The prerequisite of this method is column add to use LIST partition. Eventually, you must change sql statement. So, this method can’t apply to many cases. But, if you change the table layout and sql statement, this method is very effective.

1. Old Method.

(1) Create table, index, sequence, procedure.

 create table t1 
 (c1 number,
  c2 number,
  c3 number,
  dummy char(100));
 
 alter table t1 add constraint t1_pk primary key (c1);

 create sequence t1_seq cache 10000;

 create or replace procedure insert_t1 (LoopCnt number)
 is
 begin
     for i in 1..LoopCnt loop
      insert into t1 values(t1_seq.nextval, i, i,'dummy');
     end loop;
 end;
 /

(2) Perform the follow commands on each node (rac1, rac2) 10 sessions almost at the same time.

 alter session set tracefile_identifier='TEST1';
 alter session set events '10046 trace name context forever, level 8';
 exec dbms_application_info.set_module('TEST1','');
 exec insert_t1(10000);

(3) Merge 20 trace file using cat command (because, trcsess doesn’t well work), and analyze that using tkprof.

INSERT INTO T1
 VALUES
 (T1_SEQ.NEXTVAL, :B1 , :B1 ,'dummy')

 call     count       cpu    elapsed       disk      query    current        rows
 ------- ------  -------- ---------- ---------- ---------- ----------  ----------
 Parse       20      0.00       0.52          0          0          0           0
 Execute 200000     63.19    2495.15        253     132941     694543      200000
 Fetch        0      0.00       0.00          0          0          0           0
 ------- ------  -------- ---------- ---------- ---------- ----------  ----------
 total   200020     63.19    2495.67        253     132941     694543      200000
  
 Top-10 events (edited for readability)
   Event waited on                             Times   Max. Wait  Total Waited
   ----------------------------------------   Waited  ----------  ------------
   gc buffer busy                              15104        0.97       1009.75
   buffer busy waits                           26311        0.92        550.07
   buffer deadlock                              5423        0.47        247.61
   enq: HW - contention                         4637        0.48        180.93
   gc current block busy                        1727        1.00        131.24
   enq: TX - index contention                   3545        0.48         98.06
   gc current block 2-way                       2027        0.56         48.87
   free buffer waits                            2783        0.55         30.73
   gc cr block busy                              942        0.84         38.07
   gc cr block 2-way                           10300        0.03         20.29 
 ********************************************************************************

2. My Method.

(1) Create table, index, sequence, procedure.

create table t2 
 (
  inst_id number,
  c1 number,
  c2 number,
  c3 number,
  dummy char(100))
 partition by list(inst_id)
 (
  partition t1_inst1 values(1),
  partition t1_inst2 values(2)
 );
 
 create unique index t2_pk on t2(inst_id, c1) local;
 alter table t2 add constraint t2_pk primary key (inst_id, c1)

 create sequence t2_seq cache 10000;

 create or replace procedure insert_t2 (LoopCnt number)
 is
 v_inst_id number;
 begin
     select userenv('INSTANCE') into v_inst_id from dual;
     for i in 1..LoopCnt loop
       insert into t2 values(v_inst_id, t1_seq.nextval, i, i,'dummy') ;
     end loop;
 end;
 /

(2) Perform the follow commands on each node (rac1, rac2) 10 sessions almost at the same time.

 alter session set tracefile_identifier='TEST2';
 alter session set events '10046 trace name context forever, level 8';
 exec dbms_application_info.set_module('TEST2','');
 exec insert_t2(10000);

(3) Merge 20 trace file using cat command (because, trcsess doesn’t well work), and analyze that using tkprof. As you can see, In New method, “gc buffer busy” wait event almost disappear.

 INSERT INTO T2
 VALUES
 (:B2 , T1_SEQ.NEXTVAL, :B1 , :B1 ,'dummy')    

 call     count       cpu    elapsed       disk      query    current        rows
 ------- ------  -------- ---------- ---------- ---------- ----------  ----------
 Parse       20      0.01      12.70          0          0          0           0
 Execute 200000     56.05    1256.32        150     234086     654386      200000
 Fetch        0      0.00       0.00          0          0          0           0
 ------- ------  -------- ---------- ---------- ---------- ----------  ----------
 total   200020     56.07    1269.03        150     234086     654386      200000
  
 Top-10 events (edited for readability)
   Event waited on                             Times   Max. Wait  Total Waited
   ----------------------------------------   Waited  ----------  ------------
   buffer deadlock                              3093        1.70        379.96
   buffer busy waits                           15773        1.00        301.33
   enq: TX - index contention                   2753        0.48        134.41
   enq: HW - contention                         3809        0.49        125.47
   enq: TX - contention                          800        0.48         83.04
   free buffer waits                            3910        0.97         53.97
   log buffer space                               65        0.97         18.01
   enq: SQ - contention                          155        1.52         16.64
   library cache pin                              22        1.34         12.54
   gc current block busy                          34        1.08         10.10
   gc buffer busy                                 26        0.48          3.15
 ********************************************************************************