GC CR Block 3-Way/Block Busy Wait Event

Initial Setup

Setup GC CR Block 3-Way Wait Event 
grac41:     Running select 
grac42:     Master  Mode 
grac43:     Owner Node
Directory:  /home/oracle/RAC/PERF/GC_CR

Cleanup buffer cache
grac41: Flush buffer cache 
  SQL>  alter system flush BUFFER_CACHE;
grac42: Flush buffer cache
  SQL>  alter system flush BUFFER_CACHE;
grac43: Flush buffer cache and read the block the first time
  SQL>  alter system flush BUFFER_CACHE;

Read the data first time on grac43:
  SQL> select n1,dbms_rowid.rowid_to_absolute_fno(rowid, user, 'T' ) fno, dbms_rowid.rowid_block_number(rowid) block,
      dbms_rowid.rowid_object(rowid) obj_id,  length(v1) v1_len from t where n1=100;

Verify setup:
  SQL> select resource_name, ON_CONVERT_Q, ON_GRANT_Q,  Master_node from gv$ges_resource 
         where resource_name like '[0x89d0e][0x4],[BL]%';
    RESOURCE_NAME               ON_CONVERT_Q ON_GRANT_Q MASTER_NODE
    ------------------------------ ------------ ---------- -----------
    [0x89d0e][0x4],[BL][ext 0x0,0x          0         1         1
  SQL> select inst_id, resource_name1,  grant_level, state,owner_node from gv$ges_enqueue 
          where resource_name1 like '[0x89d0e][0x4],[BL]%';
       INST_ID RESOURCE_NAME1                 GRANT_LEV STATE         OWNER_NODE
    ---------- ------------------------------ --------- ------------- ----------
             2 [0x89d0e][0x4],[BL][ext 0x0,0x KJUSERPR  GRANTED              2
             3 [0x89d0e][0x4],[BL][ext 0x0,0x KJUSERPR  GRANTED              2
--> MASTER_NODE : grac42, OWNER_NODE: grac43, GRANT_LEVEL: Protected READ

grac41: Read the data on grac41
  SQL> select n1,dbms_rowid.rowid_to_absolute_fno(rowid, user, 'T' ) fno, dbms_rowid.rowid_block_number(rowid) block,
      dbms_rowid.rowid_object(rowid) obj_id,  length(v1) v1_len from t where n1=100;
Wait Event
  WAIT #140373949760312: nam='gc current block 3-way' ela= 540019 p1=4 p2=564494 p3=1 obj#=90353 tim=1392123301744637
GES locks
  SQL> select inst_id, resource_name1,  grant_level, state,owner_node    from gv$ges_enqueue 
         where resource_name1 like '[0x89d0e][0x4],[BL]%';
   INST_ID RESOURCE_NAME1          GRANT_LEV STATE         OWNER_NODE
  -------- ------------------------------ --------- -------------------- ----------
     2 [0x89d0e][0x4],[BL][ext 0x0,0x KJUSERPR  GRANTED              2
     2 [0x89d0e][0x4],[BL][ext 0x0,0x KJUSERPR  GRANTED              0
     1 [0x89d0e][0x4],[BL][ext 0x0,0x KJUSERPR  GRANTED              0
     3 [0x89d0e][0x4],[BL][ext 0x0,0x KJUSERPR  GRANTED              2 
X$bh block status
  SQL> select decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi',
           9,'memory',10,'mwrite',11,'donated', 12,'protected', 13, 'securefile', 14, 'siop',
           15, 'recckpt', 16, 'flashfree', 17, 'flashcur', 18, 'flashna') as state,
         mode_held, le_addr, dbarfil, dbablk, cr_scn_bas, cr_scn_wrp, class
     from sys.x$bh b
         where dbarfil = &file_no and dbablk = &block_no and state!=0 order by cr_scn_wrp,cr_scn_bas;
  STATE        MODE_HELD LE_ADDR          DBARFIL     DBABLK CR_SCN_BAS CR_SCN_WRP    CLASS
  ---------- ---------- ---------------- ---------- ---------- ---------- ---------- ----------
  scur            0 000000006BFCC478        4     564494          0      0        1

--> Summary:
    The initial Read sees a gc current block 3-way wait event  as we need to transfer the block from 
      grac43 to grac41 buffer cache
    Subsequent reads don't show any add. GCS wait events as the local buffer cache still hold the CURRENT block

Issue a long running DML on grac43 and select the same block on grac41 multiple times

grac43: 
    SQL> update t set v1 = 'Newly updated' where n1=100;
grac41: Run 3x 
   SQL> select n1,dbms_rowid.rowid_to_absolute_fno(rowid, user, 'T' ) fno, dbms_rowid.rowid_block_number(rowid) block,
      dbms_rowid.rowid_object(rowid) obj_id,  length(v1) v1_len from t where n1=100;

   SQL> select inst_id, resource_name1,  grant_level, state,owner_node from gv$ges_enqueue where resource_name1 
        like '[0x89d0e][0x4],[BL]%';
     INST_ID RESOURCE_NAME1                 GRANT_LEV STATE         OWNER_NODE
   --------- ------------------------------ --------- ------------- ----------
           3 [0x89d0e][0x4],[BL][ext 0x0,0x KJUSEREX  GRANTED              2
           2 [0x89d0e][0x4],[BL][ext 0x0,0x KJUSEREX  GRANTED              2

  SQL> select decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi',
           9,'memory',10,'mwrite',11,'donated', 12,'protected', 13, 'securefile', 14, 'siop',
           15, 'recckpt', 16, 'flashfree', 17, 'flashcur', 18, 'flashna') as state,
         mode_held, le_addr, dbarfil, dbablk, cr_scn_bas, cr_scn_wrp, class
     from sys.x$bh b
         where dbarfil = &file_no and dbablk = &block_no and state!=0 order by cr_scn_wrp,cr_scn_bas;
   STATE              MODE_HELD LE_ADDR         DBARFIL    DBABLK CR_SCN_BAS CR_SCN_WRP      CLASS
   -------------------- ---------- ---------------- ---------- ---------- ---------- ---------- ----------
   cr                  0 00              4    564494     26034467       0          1
   cr                  0 00              4    564494     26034471       0          1
   cr                  0 00              4    564494     26034474       0          1

Wait Events
  WAIT #139916002047512: nam='gc cr block busy' ela= 3742 p1=4 p2=564494 p3=1 obj#=90353 tim=1392124434363139
  WAIT #139916002047512: nam='gc cr block busy' ela= 4331 p1=4 p2=564494 p3=1 obj#=90353 tim=1392124437806999
  WAIT #139916002047512: nam='gc cr block busy' ela= 3362 p1=4 p2=564494 p3=1 obj#=90353 tim=1392124438532356

--> For CR blocks no resource locks are used - the KJUSEREX lock is from our long running Update statment
    Received CR copy is only usable for the current session and for the current SQL statment as Query SCN moves forward
    For every select the block needs to be re-constructed on grac43 even nothing has changed on grac43 
    Gc cr block busy is seen because  we can't send the block immediate ( compare to our initial settings ) as we need 
      reconstruct the block with the DML info. 
    Local buffer caches can hold multiple block version even the block itself doesn't change
    Long running DMLs or not committing data a long time can have severe performance impact with any RAC database !

After committing data on grac43

grac43:
  SQL> COMMIT;
grac41:
  SQL> select inst_id, resource_name1,  grant_level, state,owner_node from gv$ges_enqueue 
         where resource_name1 like '[0x89d0e][0x4],[BL]%';
   INST_ID RESOURCE_NAME1          GRANT_LEV STATE         OWNER_NODE
  -------- ------------------------------ --------- -------------------- ----------
     1 [0x89d0e][0x4],[BL][ext 0x0,0x KJUSERPR  GRANTED              0
     3 [0x89d0e][0x4],[BL][ext 0x0,0x KJUSERPR  GRANTED              2
     2 [0x89d0e][0x4],[BL][ext 0x0,0x KJUSERPR  GRANTED              2
     2 [0x89d0e][0x4],[BL][ext 0x0,0x KJUSERPR  GRANTED              0
   SQL> select decode(state,0,'free',1,'xcur',2,'scur',3,'cr', 4,'read',5,'mrec',6,'irec',7,'write',8,'pi',
           9,'memory',10,'mwrite',11,'donated', 12,'protected', 13, 'securefile', 14, 'siop',
           15, 'recckpt', 16, 'flashfree', 17, 'flashcur', 18, 'flashna') as state,
         mode_held, le_addr, dbarfil, dbablk, cr_scn_bas, cr_scn_wrp, class
     from sys.x$bh b
         where dbarfil = &file_no and dbablk = &block_no and state!=0 order by cr_scn_wrp,cr_scn_bas;
  STATE              MODE_HELD  LE_ADDR           DBARFIL    DBABLK CR_SCN_BAS CR_SCN_WRP      CLASS
  ------------------  --------- --------------- - --------- ---------- ---------- ---------- ----------
  scur                0          000000006BFCC478      4    564494            0       0          1
  cr                  0          00                    4    564494     26034467       0          1
  cr                  0          00                    4    564494     26034471       0          1
  cr                  0          00                    4    564494     26034474       0          1
--> After commit the KJUSEREX lock is downgraded to KJUSERPR 
    As we work with the current block now no add. GCS wait events are seen when running the 
      select multiple times

Leave a Reply

Your email address will not be published. Required fields are marked *