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