Cache Fushion Scenarios

Let’s consider read and write scenarios in 2-nodes RAC. I want to answer the following questions:

1 What’s the buffer state in node A (SCUR or CR) after reading a block from disk initially?
2 What are the buffer states in node A and B after block transfer from A to B?
3 Does node A need to contact master node if it found a CR block in its local cache?
4 Can SCUR buffers be returned during buffer access?

Here are the steps to get the answers.

My Environments:

OS : RHEL 6.3 x86_64
DB : 11.2.0.3.0 RAC

— Prepare
— Create test table

conn caoff/caoff
drop table t1 purge;
create table t1(id number, name varchar2(64));

-- insert data into table
begin
  for i in 1..1000 loop
     insert into t1 values(
	 i, 'INITIAL VALUE OF COLUMN');
     end loop;
end;
/

commit;

-- on both nodes
alter system flush buffer_cache;

In order to rule out the implication of checkpoint, we suspend DBWx on both nodes temporarily.

select prc.pid
from 	v$bgprocess bgp,
	v$process prc
where bgp.name like 'DBW%'
and prc.addr = bgp.paddr;

oradebug setorapid 18
oradebug suspend

-- After all the DBWx were suspended, the command would hang:
alter system flush buffer_cache;

— Execute Scripts
— read one row and identify block no of a specific row

select 	DBMS_ROWID.ROWID_OBJECT(rowid) "DATA_OBJECT_ID",
	DBMS_ROWID.ROWID_RELATIVE_FNO(rowid) "FILE",
	DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) "BLOCK",
	DBMS_ROWID.ROWID_ROW_NUMBER(rowid) "ROW"
from T1
where id=1;

DATA_OBJECT_ID       FILE      BLOCK        ROW
-------------- ---------- ---------- ----------
         66854          5        142          0

— Identify master node BL locks and state of the buffer

select 	b.file# FILE#,
	b.dbablk BLOCK#,
	decode(b.state, 0, 'free',
			1, 'xcur',
			2, 'scur',
			3, 'cr',
			4, 'read',
			5, 'mrec',
			6, 'irec',
			7, 'iwrite',
			8, 'pi',
			9, 'memory',
			10, 'mwrite',
			11, 'donated') as state, 
	b.tch,
	decode(bitand(b.flag,1), 0, 'N', 'Y') as DIRTY,
	r.kjblmaster master_node,
	r.KJBLGRANT
from x$bh b,
		 x$le l,
		 x$kjbl r
where b.obj= 66854 --DataObjectId
and b.FILE# = 5 --FILENo
and b.DBABLK = 139 --BLKNo
and b.le_addr=l.le_addr
and l.le_kjbl =r.kjbllockp ;

     FILE#     BLOCK# STATE                        TCH DIR MASTER_NODE KJBLGRANT
---------- ---------- --------------------- ---------- --- ----------- ---------------------------
         5        139 scur                           1 N             1 KJUSERPR
select 	b.file# FILE#,
	b.dbablk BLOCK#,
	decode(b.state, 0, 'free',
			1, 'xcur',
			2, 'scur',
			3, 'cr',
			4, 'read',
			5, 'mrec',
			6, 'irec',
			7, 'iwrite',
			8, 'pi',
			9, 'memory',
			10, 'mwrite',
			11, 'donated') as state, 
	b.tch,
	decode(bitand(b.flag,1), 0, 'N', 'Y') as DIRTY
from x$bh b
where b.obj= 66854 --DataObjectId
and b.FILE# = 5 --FILENo
and b.DBABLK = 139 --BLKNo;

— update one specific row

select min(id), max(id), count(id)
from t1
where dbms_rowid.rowid_block_number(rowid) = 139;


   MIN(ID)    MAX(ID)  COUNT(ID)
---------- ---------- ----------
         1        223        223

— update 1 row, keep the length of name the same so the rowid will not change

update t1 set name='UPDATED VALUE OF COLUMN' where id=1;

— update 1 row, keep the length of name the same so the rowid will not change

update t1 set name='UPDATED VALUE OF COLUMN' where id=2;

Summary:

Scenarios State in Inst 1 Buffer State in Inst 2
1: inst1 read from disk SCUR N/A
2: inst2 read the same block SCUR SCUR
3: inst1 read the same block (repeated read) SCUR, TCH incre SCUR
4: inst1 modify the 1st row without commit XCUR, CR CR
5: inst2 read the same block XCUR 1 additional CR
6: inst2 modify the 2nd row without commit PI XCUR
7: inst1 read the same block 1 additional CR SCUR
8: inst1 commit PI, CR XCUR
9: inst2 commit PI, CR XCUR

Conclusions
1 What’s the buffer state in node A (SCUR or CR) after reading a block from disk initially?
A: SCUR

2 What are the buffer states in node A and B after block transfer from A to B?
A: Both are SCUR

3 Does node A need to contact master node if it found a CR block in its local cache?
A: Yes. Because a new CR block would be created.

4 Can SCUR buffers be returned during buffer access?
A: Yes

5 Will “alter system flush buffer_cache” write dirty buffers?
A: Yes.

6 Commit will not affect BL lock status while checkpoint will.

Reference:

https://mgrvinod.wordpress.com/2011/03/22/cache-fusion/
http://www.datadisk.co.uk/html_docs/rac/cache_fusion.htm

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s