Table of Contents
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