,One of the best document on RAC Concepts that I came across is DemystifiyingCacheFusion by Arup nanda .
As this document makes the concept more clear and provides script to see actually what happens in the Database.
Script Location: racsig_scripts.zip
The document didn't provide the order of execution of the scripts,I figured out the order after few executions and here it is for all,
From Here on , Instance1 =I1 & Instance2 =I2
1) Created a User sri/sri from I1 . Check the same from I2 and Confirm.
2) Create a Table and populate it using "setup.sql" from I1 .
3) Get the object id of the table created using "dobjid.sql" .
4) run "sel.sql" in I1 .
5) Check the statistics using "segstat.sql" in I1. (Use Data object get from step3),
STATISTIC_NAME VALUE
------------------------- --------
logical reads 80
physical writes 8
physical write requests 8
space allocated 65536
6)Now run "sel.sql" in I2"
7) Check the statistics using "segstat.sql" in I2. (Use Data object get from step3),
STATISTIC_NAME VALUE
----------------------------- --------
logical reads 32
gc cr blocks received 12
gc current blocks received 6
Note the difference between the two statistics, this shows that the I2 got the block from I1 through Cache fusion and no Physical reads.
8) Run "flush.sql" in I1. and run "sel.sql" in I2.
9) Check the statistics using "segstat.sql" in I2. (Use Data object get from step3),
STATISTIC_NAME VALUE
------------------------------ -------
logical reads 64
physical reads 6
physical read requests 2
gc cr blocks received 12
gc current blocks received 6
Now we can see , since the cache was cleared the I2 read the blocks from the Disk.
10) Run the "block.sql" to find the block associated with the table :
COL1 FILE# BLOCK#
---------- ---------- ----------
1 4 179
2 4 179
3 4 179
4 4 179
5 4 179
6 4 179
11) To see the CR and SCUR :
Flush the cache and run a checkpoint in both the nodes .
alter system flush buffer_cache;
alter system checkpoint;
update the table from I1 using "upd.sql".
Check the lock status of the block using "vbh.sql" (edit the object Id if needed).
FILE# BLOCK# CLASS_TYPE STATUS LOCK_ELE
---------- ---------- ------------------ ---------- --------
4 179 data block free 00
4 179 data block xcur 3D7E3FE4
4 179 data block cr 00
4 179 data block free 00
4 179 data block free 00
4 179 data block free 00
4 179 data block free 00
4 179 data block free 00
This shows that the cr and Xcur. cr -> Consistent read , xcur --> exclusive Current.
And check the lock status from I2 -> "vbh.sql"
FILE# BLOCK# CLASS_TYPE STATUS LOCK_ELE
---------- ---------- ------------------ ---------- --------
4 179 data block free 00
4 179 data block free 00
4 179 data block free 00
4 179 data block free 00
4 179 data block free 00
12) Run select * from racsig : from I2, and check the block status "vbh.sql"
FILE# BLOCK# CLASS_TYPE STATUS LOCK_ELE
---------- ---------- ------------------ ---------- --------
4 179 data block cr 00
4 179 data block free 00
4 179 data block free 00
4 179 data block free 00
4 179 data block free 00
13) The master node of a block can be obtained using "check_master.sql "
DBABLK MASTER_NODE
---------- -----------
178 1
183 1
180 1
182 1
179 1 ================(racsig table)
181 1
14) Do an update of a column in I2 and select the same row in I1. Now check the block status (vbh.sql)in I2,
FILE# BLOCK# CLASS_TYPE STATUS LOCK_ELE
---------- ---------- ------------------ ---------- --------
4 179 data block scur 433EA63C
4 179 data block cr 00
4 179 data block cr 00
4 179 data block cr 00
4 179 data block cr 00
15) do a commit on both Instances. and in I1 run
update racsig set col2='C';
on I2 check the block status,
FILE# BLOCK# CLASS_TYPE STATUS LOCK_ELE
---------- ---------- ------------------ ---------- --------
4 179 data block pi 433EA63C
4 179 data block cr 00
4 179 data block cr 00
4 179 data block cr 00
4 179 data block cr 00
Since we issued a commit (not a checkpoint) ,and I1 requested the same so I2 created a PI of the block and sent it to I1.
I1 --> vbh.sql
FILE# BLOCK# CLASS_TYPE STATUS LOCK_ELE
---------- ---------- ------------------ ---------- --------
4 179 data block cr 00
4 179 data block xcur 3D7E3FE4
4 179 data block cr 00
4 179 data block cr 00
4 179 data block cr 00
4 179 data block cr 00
16) Run : alter system checkpoint and see block status in I2 (after a checkpoint is issued the PI will be destroyed).
As this document makes the concept more clear and provides script to see actually what happens in the Database.
Script Location: racsig_scripts.zip
The document didn't provide the order of execution of the scripts,I figured out the order after few executions and here it is for all,
From Here on , Instance1 =I1 & Instance2 =I2
1) Created a User sri/sri from I1 . Check the same from I2 and Confirm.
2) Create a Table and populate it using "setup.sql" from I1 .
3) Get the object id of the table created using "dobjid.sql" .
4) run "sel.sql" in I1 .
5) Check the statistics using "segstat.sql" in I1. (Use Data object get from step3),
STATISTIC_NAME VALUE
------------------------- --------
logical reads 80
physical writes 8
physical write requests 8
space allocated 65536
6)Now run "sel.sql" in I2"
7) Check the statistics using "segstat.sql" in I2. (Use Data object get from step3),
STATISTIC_NAME VALUE
----------------------------- --------
logical reads 32
gc cr blocks received 12
gc current blocks received 6
Note the difference between the two statistics, this shows that the I2 got the block from I1 through Cache fusion and no Physical reads.
8) Run "flush.sql" in I1. and run "sel.sql" in I2.
9) Check the statistics using "segstat.sql" in I2. (Use Data object get from step3),
STATISTIC_NAME VALUE
------------------------------ -------
logical reads 64
physical reads 6
physical read requests 2
gc cr blocks received 12
gc current blocks received 6
Now we can see , since the cache was cleared the I2 read the blocks from the Disk.
10) Run the "block.sql" to find the block associated with the table :
COL1 FILE# BLOCK#
---------- ---------- ----------
1 4 179
2 4 179
3 4 179
4 4 179
5 4 179
6 4 179
11) To see the CR and SCUR :
Flush the cache and run a checkpoint in both the nodes .
alter system flush buffer_cache;
alter system checkpoint;
update the table from I1 using "upd.sql".
Check the lock status of the block using "vbh.sql" (edit the object Id if needed).
FILE# BLOCK# CLASS_TYPE STATUS LOCK_ELE
---------- ---------- ------------------ ---------- --------
4 179 data block free 00
4 179 data block xcur 3D7E3FE4
4 179 data block cr 00
4 179 data block free 00
4 179 data block free 00
4 179 data block free 00
4 179 data block free 00
4 179 data block free 00
This shows that the cr and Xcur. cr -> Consistent read , xcur --> exclusive Current.
And check the lock status from I2 -> "vbh.sql"
FILE# BLOCK# CLASS_TYPE STATUS LOCK_ELE
---------- ---------- ------------------ ---------- --------
4 179 data block free 00
4 179 data block free 00
4 179 data block free 00
4 179 data block free 00
4 179 data block free 00
12) Run select * from racsig : from I2, and check the block status "vbh.sql"
FILE# BLOCK# CLASS_TYPE STATUS LOCK_ELE
---------- ---------- ------------------ ---------- --------
4 179 data block cr 00
4 179 data block free 00
4 179 data block free 00
4 179 data block free 00
4 179 data block free 00
13) The master node of a block can be obtained using "check_master.sql "
DBABLK MASTER_NODE
---------- -----------
178 1
183 1
180 1
182 1
179 1 ================(racsig table)
181 1
14) Do an update of a column in I2 and select the same row in I1. Now check the block status (vbh.sql)in I2,
FILE# BLOCK# CLASS_TYPE STATUS LOCK_ELE
---------- ---------- ------------------ ---------- --------
4 179 data block scur 433EA63C
4 179 data block cr 00
4 179 data block cr 00
4 179 data block cr 00
4 179 data block cr 00
15) do a commit on both Instances. and in I1 run
update racsig set col2='C';
on I2 check the block status,
FILE# BLOCK# CLASS_TYPE STATUS LOCK_ELE
---------- ---------- ------------------ ---------- --------
4 179 data block pi 433EA63C
4 179 data block cr 00
4 179 data block cr 00
4 179 data block cr 00
4 179 data block cr 00
Since we issued a commit (not a checkpoint) ,and I1 requested the same so I2 created a PI of the block and sent it to I1.
I1 --> vbh.sql
FILE# BLOCK# CLASS_TYPE STATUS LOCK_ELE
---------- ---------- ------------------ ---------- --------
4 179 data block cr 00
4 179 data block xcur 3D7E3FE4
4 179 data block cr 00
4 179 data block cr 00
4 179 data block cr 00
4 179 data block cr 00
16) Run : alter system checkpoint and see block status in I2 (after a checkpoint is issued the PI will be destroyed).
Note: All the .sql are in the racsig_scripts.zip .
Special thanks to Arup for this Document.
