Until Oracle 8i released, Oracle Introduced Parallel Server on Oracle 6i version. Oracle Introduced Real Application Cluster concept on Oracle 8i version. Oracle Cache Fusion is one of the important component in Oracle RAC environment.
- What is Cache Fusion
- How Cache Fusion works?
- How Oracle RAC read block from disk or buffer of another Instance using Cache Fusion?
- How Oracle RAC maintain blocks in buffer across the cluster for DML?
What is Cache Fusion:
Oracle RAC has two or more instances, and it uses Cache Fusion to smoothly move data blocks between these instances’ buffers across the cluster connection. If one instance reads a data block from the disk and another instance needs the same block, it’s faster to grab it from the instance that already has it in its memory (SGA) than reading from the disk again. This quick transfer across the network is way faster than dealing with the slower disk operations, making Oracle RAC work better.
Cache Fusion Ships Blocks from Cache to Cache Across the Interconnect:
Buffer Cache, Shared Pool and the Undo Tablespace like single-instance database are required to facilitate the Cache Fusion. But Extra coordination is needed in the cluster to make a collection of instances work together.
Before explained how Cache Fusion works, We will know –
Global Resource Directory (GRD): On RAC, GRD process keeps track of the resources in the cluster. There is no true concept of a master node in Oracle RAC but each instance of cluster can becomes the resources mater.
- Track master instances of all buffers.
- GRD is present on all the instances of the cluster.
How to find the master node for a resource in RAC:
SQL> SELECT DATA_OBJECT_ID, OBJECT_ID FROM DBA_OBJECTS
WHERE OBJECT_NAME = 'TBL_CFUSION';
DATA_OBJECT_ID OBJECT_ID
-------------- ----------
93836 93836
SQL> SELECT b.dbablk, r.kjblmaster master_node
FROM x$le l, x$kjbl r, x$bh b
WHERE b.le_addr = l.le_addr and l.le_kjbl = r.kjbllockp and b.obj = &Data_Object_ID;
Enter value for data_object_id: 93836
old 3: WHERE b.obj = &Data_Object_ID and b.le_addr = l.le_addr and l.le_kjbl = r.kjbllockp
new 3: WHERE b.obj = 93836 and b.le_addr = l.le_addr and l.le_kjbl = r.kjbllockp
DBABLK MASTER_NODE
---------- -----------
225 1
230 1
227 1
224 1
229 1
226 1
231 1
228 1
8 rows selected.
Global Cache Services (GCS): Global Cache Services are responsible to transfer blocks from one instance to another. A single-instance database relies on enqueues (locks) to protect for modifying the same records by two processes simultaneously. Buffer Cache on all instances appear to be global and enqueues on the resources is also global across the cluster.
LMS – LMS is a GCS process. This process used to called the Lock Manager Server.
#Use the following syntax to query V$SYSSTAT: SQL> SELECT NAME,VALUE FROM V$SYSSTAT WHERE NAME LIKE '%global cache%'; SQL>
Global Enqueue Services (GES): Global Enqueue Services is responsible for managing locks across the cluster. GES was previously called the Distributed/Dynamic Lock Manager (DLM).
- Holds the information on the locks on the buffers.
- Each lock has a name shown in V$LOCK_ELEMENT (or X$LE).
- If a buffer is locked, the lock element name is shown in V$BH.LOCK_ELEMENT.
SQL> UPDATE SAMAD.TBL_CFUSION SET NAME='Y' WHERE ID=4;
1 row updated.
SQL> INSERT INTO SAMAD.TBL_CFUSION VALUES (3,'X');
1 row created.
SQL> DELETE FROM SAMAD.TBL_CFUSION WHERE ID =2;
1 row deleted.
SQL> Select LOCK_ELEMENT_NAME,MODE_HELD,CLASS,LOCK_ELEMENT_ADDR
From V$LOCK_ELEMENT
where LOCK_ELEMENT_ADDR in (SELECT LOCK_ELEMENT_ADDR FROM V$BH
WHERE OBJD IN (SELECT DATA_OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME = 'TBL_CFUSION'));
LOCK_ELEMENT_NAME MODE_HELD CLASS LOCK_ELEMENT_ADD
----------------- ---------- ---------- ----------------
225 2 6 0000000077FDF6E8
230 2 6 0000000087BDD960
227 1 6 00000000877DA828
224 2 6 0000000077FD99F0
229 1 6 0000000087BE7168
226 1 6 0000000087BECA00
231 2 6 0000000087BE5098
228 1 6 00000000877DB660
8 rows selected.
SQL> SELECT ADDR,LE_ADDR,LE_ID1,LE_RLS,LE_MODE,LE_LOCAL
FROM X$LE
WHERE LE_ADDR IN (SELECT LOCK_ELEMENT_ADDR FROM V$BH
WHERE OBJD IN (SELECT DATA_OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME = 'TBL_CFUSION'));
ADDR LE_ADDR LE_ID1 LE_RLS LE_MODE LE_LOCAL
---------------- ---------------- ---------- ---------- ---------- ----------
00007F87B4376810 0000000077FDF6E8 225 0 2 1
00007F87B4376810 0000000087BDD960 230 0 2 1
00007F87B4376810 00000000877DA828 227 0 1 1
00007F87B4376810 0000000077FD99F0 224 0 2 1
00007F87B4376810 0000000087BE7168 229 0 1 1
00007F87B4376810 0000000087BECA00 226 0 1 1
00007F87B4376810 0000000087BE5098 231 0 2 1
00007F87B4376810 00000000877DB660 228 0 1 1
8 rows selected.
Note::: MODE_HELD : Platform dependent value for lock mode held; often: 3 = share; 5 = exclusive
How GES Workloads Affect Performance:
Calculate the ratio of local-to-remote global enqueue resource operations using this query:
SELECT
r.CONVERT_TYPE,
SUM(r.AVERAGE_CONVERT_TIME),
SUM(l.AVERAGE_CONVERT_TIME),
SUM(r.CONVERT_COUNT),
SUM(l.CONVERT_COUNT)
FROM V$GES_CONVERT_LOCAL l, V$GES_CONVERT_REMOTE r
WHERE r.convert_count <> 0 OR l.convert_count <> 0
GROUP BY r.CONVERT_TYPE;LMON: LMON process is the GES master process.
LMD – Lock Manager Daemon. This process manages incoming lock requests.
LCK0 – The instance enqueue process. This process manages lock requests for library Cache objects.
How Cache Fusion Works:
How Oracle RAC read block from disk or buffer of other Instance using Cache Fusion?
- Object is not available on Buffer. See the output from V$BH.
- 1st time run select query – SELECT ID FROM SAMAD.TBL_CFUSION
- Collect sql_trace of the query and generate TKPROF report.
Node 1:
When we run any query in database first time or blocks are not available in cache then all blocks read from database file for rac and stand alone database.
SQL> SELECT DATA_OBJECT_ID, OBJECT_ID FROM DBA_OBJECTS
WHERE OBJECT_NAME = 'TBL_CFUSION';
DATA_OBJECT_ID OBJECT_ID
-------------- ----------
93836 93836
SQL> @obj_buffer.sql
Enter value for objid: 93836
old 21: WHERE OBJD= &OBJID
new 21: WHERE OBJD= 93836
no rows selected
SQL> ALTER SESSION SET SQL_TRACE = TRUE;
Session altered.
SQL> SELECT ID FROM SAMAD.TBL_CFUSION;
ID
----------
1
1
4
3
SQL> ALTER SESSION SET SQL_TRACE = FALSE;
Session altered.
TKPROF output. There are 6 IOs from disk of this query.
SELECT ID FROM SAMAD.TBL_CFUSION;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 6 8 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 6 8 0 4
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 111
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
4 4 4 TABLE ACCESS FULL TBL_CFUSION (cr=8 pr=6 pw=0 time=2709 us cost=3 size=12 card=4)
SQL> select s.name, st.value
from v$statname s, v$mystat st
where st.STATISTIC# = s.STATISTIC#
and s.name in ('session logical reads','physical reads');
NAME VALUE
---------------------------------------------------------------- ----------
session logical reads 3677
physical reads 99
Node 2:
- Collect total physical and logical IO information.
- Object is not available in Buffer Cache on 2nd Instance.
- Select query – SELECT ID FROM SAMAD.TBL_CFUSION with enabling SQL Trace.
- Before and After execution of the query, Physical read is 1 on this session.
SQL> select s.name, st.value
from v$statname s, v$mystat st
where st.STATISTIC# = s.STATISTIC#
and s.name in ('session logical reads','physical reads');
NAME VALUE
---------------------------------------------------------------- ----------
session logical reads 1555
physical reads 1
SQL> @obj_buffer.sql
Enter value for objid: 93836
old 21: WHERE OBJD= &OBJID
new 21: WHERE OBJD= 93836
no rows selected
SQL> ALTER SESSION SET SQL_TRACE = TRUE;
Session altered.
SQL> SELECT ID FROM SAMAD.TBL_CFUSION;
ID
----------
1
1
4
3
SQL> ALTER SESSION SET SQL_TRACE = FALSE;
Session altered.
SQL> select s.name, st.value
from v$statname s, v$mystat st
where st.STATISTIC# = s.STATISTIC#
and s.name in ('session logical reads','physical reads');
NAME VALUE
---------------------------------------------------------------- ----------
session logical reads 3026
physical reads 1
There is no physical IO for the query execution even object was not in Buffer cache. Means all required blocks for this object retrieved from 1st Instance buffer cache instead of Disk.
SQL ID: 0yx6m42qawyb5 Plan Hash: 4124388744
SELECT ID FROM SAMAD.TBL_CFUSION
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 42 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 8 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 50 0 4
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 111
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
4 4 4 TABLE ACCESS FULL TBL_CFUSION (cr=8 pr=0 pw=0 time=1263 us cost=3 size=12 card=4)
How Oracle RAC maintain blocks in buffer across the cluster for DML?
When a block is requested by user:
- The buffer cache is searched on local buffer cache
- If not found, there are two options
- Get from the other cache across the cluster
- Get from disk
- If found on buffer on other instance, there are three options:
- Send the buffer to the user
- Examine other caches for the presence of this buffer
- Get from the disk
- If not found, there are two options
- How does it decide which option to take?
- The buffer can be retrieved in two modes
- Consistent Read (CR) -> Block contains uncommitted changes. Session will get a version of the block prior to the changes. Intention to read block
- Current -> If intention to modify
- The buffer can be retrieved in two modes
- There can be several CR copies of a buffer
- There can be only one current mode
- For an instance
- Each current buffer is Shared Current
- Only one buffer in the entire cluster can be Exclusive Current
SQL> show user
USER is "SAMAD"
SQL> Create Table SAMAD.TBL_CFTEST(id number, name char(2000));
insert into SAMAD.TBL_CFTEST Values(1, 'Samad');
insert into SAMAD.TBL_CFTEST Values(2, 'Samad');
insert into SAMAD.TBL_CFTEST Values(3, 'Samad');
insert into SAMAD.TBL_CFTEST Values(4, 'Samad');
Table created.
SQL> SQL>
1 row created.
SQL> SQL>
1 row created.
SQL> SQL>
1 row created.
SQL> SQL>
1 row created.
SQL> commit;
Commit complete.
SQL> SELECT EXTENT_ID, BLOCK_ID, BLOCKS FROM DBA_EXTENTS
WHERE SEGMENT_NAME = 'TBL_CFTEST';
EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ----------
0 232 8
SQL> SELECT ID,
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE#,
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK#
FROM SAMAD.TBL_CFTEST;
ID FILE# BLOCK#
---------- ---------- ----------
1 6 238
2 6 238
3 6 238
4 6 239
SQL> SELECT DATA_OBJECT_ID, OBJECT_ID FROM DBA_OBJECTS
WHERE OBJECT_NAME = 'TBL_CFTEST';
DATA_OBJECT_ID OBJECT_ID
-------------- ----------
93861 93861For DML, rows on block status is xcur (exclusive current) and these are most recent current copy of block. xcur status for a rows is possible a copy on an instance across the cluster.
SQL> @obj_buffer.sql
Enter value for data_obj_id: 93861
old 9: where objd = &data_obj_id
new 9: where objd = 93861
FILE# BLOCK# CLASS_TYPE STATUS LOCK_ELEMENT_ADD
---------- ---------- ------------------ ---------- ----------------
6 232 1st level bmb xcur 00000000877DADA0
6 233 2nd level bmb xcur 00000000877E5958
6 234 segment header xcur 0000000087BF7C48
6 235 data block xcur 0000000087BEED00
6 236 data block xcur 00000000877D96A8
6 237 data block xcur 00000000877DBF20
6 238 data block xcur 00000000877E7C58
6 239 data block xcur 00000000877E4490
8 rows selected.Node 2:
SQL> SELECT DATA_OBJECT_ID, OBJECT_ID FROM DBA_OBJECTS
WHERE OBJECT_NAME = 'TBL_CFTEST';
DATA_OBJECT_ID OBJECT_ID
-------------- ----------
93861 93861TBL_CFTEST object is not available in buffer cache on local instance. When run the select command of this table,
SQL> @obj_buffer.sql
Enter value for objid: 93861
old 21: WHERE OBJD= &OBJID
new 21: WHERE OBJD= 93861
no rows selected
SQL> SELECT INSTANCE_NUMBER FROM V$INSTANCE;
INSTANCE_NUMBER
---------------
2
SQL> SELECT ID FROM SAMAD.TBL_CFTEST WHERE ID = &ID;
Enter value for id: 1
old 1: SELECT ID FROM SAMAD.TBL_CFTEST WHERE ID = &ID
new 1: SELECT ID FROM SAMAD.TBL_CFTEST WHERE ID = 1
ID
----------
1These blocks are available in Node 1 which is resource master.
SQL> select b.dbablk, r.kjblmaster master_node
from x$le l, x$kjbl r, x$bh b
where b.le_addr = l.le_addr
and l.le_kjbl = r.kjbllockp and b.obj = &DataObjectId; 2 3 4
Enter value for dataobjectid: 93861
old 4: and l.le_kjbl = r.kjbllockp and b.obj = &DataObjectId
new 4: and l.le_kjbl = r.kjbllockp and b.obj = 93861
DBABLK MASTER_NODE
---------- -----------
238 1
235 1
237 1
234 1
239 1
236 1
6 rows selected.Selected only one record and it should return the relevant block (block # 238) and it copied all blocks as there was no index and it reads all block for full table scan.
When oracle cache fusion request a block for reading from another instance, block copies as Consistence Read (CR) mode from other instance then cope the same block as scur mode. Oracle remain CR mode block because that point user request to read these block. SCUR (Shared Current) means these rows are up to date for across the cluster and Oracle can rely this row for any further changes in cluster.
In this point on node 2, have two copies of the blocks.
SQL> @obj_buffer.sql
Enter value for objid: 93861
old 21: WHERE OBJD= &OBJID
new 21: WHERE OBJD= 93861
FILE# BLOCK# CLASS TYPE STATUS LOCK_ELEMENT_ADD
---------- ---------- ------------------ ---------- ----------------
6 234 SEGMENT HEADER cr 00
6 234 SEGMENT HEADER scur 00000000803FA178
6 235 DATA BLOCK scur 00000000803F1230
6 235 DATA BLOCK cr 00
6 236 DATA BLOCK scur 0000000087BE01D8
6 236 DATA BLOCK cr 00
6 237 DATA BLOCK scur 0000000087BE4D50
6 237 DATA BLOCK cr 00
6 238 DATA BLOCK cr 00
6 238 DATA BLOCK scur 0000000087BE6448
6 239 DATA BLOCK cr 00
FILE# BLOCK# CLASS TYPE STATUS LOCK_ELEMENT_ADD
---------- ---------- ------------------ ---------- ----------------
6 239 DATA BLOCK scur 0000000087BE00C0
12 rows selected.Now on Node 1, rows status changed from xcur to scur means these block’s most recent copies are available in another nodes. in this case, we saw that these blocks copies to node 2 as scur. If we have any rows status xcur means these are exclusively current across the cluster.
XCUR and SCUR both mode for a row in buffer is not possible at the same time.
SCUR on both nodes means Oracle will consider any one instance for these rows as these are up to date on both nodes.
SQL> @obj_buffer.sql
Enter value for data_obj_id: 93861
old 9: where objd = &data_obj_id
new 9: where objd = 93861
FILE# BLOCK# CLASS_TYPE STATUS LOCK_ELEMENT_ADD
---------- ---------- ------------------ ---------- ----------------
6 234 segment header scur 0000000087BF7C48
6 235 data block scur 0000000087BEED00
6 236 data block scur 00000000877D96A8
6 237 data block scur 00000000877DBF20
6 238 data block scur 00000000877E7C58
6 239 data block scur 00000000877E4490
8 rows selected.Now try to update a row on Instance 1, and we see that row status has been changed to xcur and also remain with CR copy for read consistence.
SQL> UPDATE SAMAD.TBL_CFTEST SET NAME='Y' WHERE ID=2;
1 row updated.
SQL> @obj_buffer.sql
Enter value for data_obj_id: 93861
old 9: where objd = &data_obj_id
new 9: where objd = 93861
FILE# BLOCK# CLASS_TYPE STATUS LOCK_ELEMENT_ADD
---------- ---------- ------------------ ---------- ----------------
6 238 data block xcur 00000000877E7C58
6 238 data block cr 00
6 239 data block scur 00000000877E4490
3 rows selected.Now on Node 2, Now we got two cr copies for block# 238 and no scur copy as we had xcur on Node 1.
SQL> @obj_buffer.sql
Enter value for objid: 93861
old 21: WHERE OBJD= &OBJID
new 21: WHERE OBJD= 93861
FILE# BLOCK# CLASS TYPE STATUS LOCK_ELEMENT_ADD
---------- ---------- ------------------ ---------- ----------------
6 238 DATA BLOCK cr 00
6 238 DATA BLOCK cr 00
6 239 DATA BLOCK cr 00
6 239 DATA BLOCK scur 0000000087BE00C0
4 rows selected.
SQL> SELECT ID,
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) FILE#,
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) BLOCK#
FROM SAMAD.TBL_CFTEST;
ID FILE# BLOCK#
---------- ---------- ----------
1 6 238
2 6 238
3 6 238
4 6 239Another row on same block update on Node 2, and we see 4 CR copies and 1 XCUR copy.
SQL> UPDATE SAMAD.TBL_CFTEST SET NAME='Y' WHERE ID=3;
1 row updated.
SQL> @obj_buffer.sql
Enter value for objid: 93861
old 21: WHERE OBJD= &OBJID
new 21: WHERE OBJD= 93861
FILE# BLOCK# CLASS TYPE STATUS LOCK_ELEMENT_ADD
---------- ---------- ------------------ ---------- ----------------
6 238 DATA BLOCK cr 00
6 238 DATA BLOCK cr 00
6 238 DATA BLOCK cr 00
6 238 DATA BLOCK xcur 0000000087BE6448
6 238 DATA BLOCK cr 00
6 239 DATA BLOCK scur 0000000087BE00C0
6 239 DATA BLOCK cr 00
7 rows selected.On node 1, previously updated record (LOCK_ELEMENT_ADD-> 00000000877E7C58) status changed to Past Image (PI) mode.
SQL> @obj_buffer.sql
Enter value for data_obj_id: 93861
old 9: where objd = &data_obj_id
new 9: where objd = 93861
FILE# BLOCK# CLASS_TYPE STATUS LOCK_ELEMENT_ADD
---------- ---------- ------------------ ---------- ----------------
6 238 data block pi 00000000877E7C58
6 238 data block cr 00
6 238 data block cr 00
6 238 data block cr 00
6 239 data block scur 00000000877E4490
5 rows selected.Once made checkpoint, PI changed to CR.
SQL> alter system checkpoint;
System altered.
SQL> @obj_buffer.sql
Enter value for data_obj_id: 93861
old 9: where objd = &data_obj_id
new 9: where objd = 93861
FILE# BLOCK# CLASS_TYPE STATUS LOCK_ELEMENT_ADD
---------- ---------- ------------------ ---------- ----------------
6 238 data block cr 00
6 238 data block cr 00
6 238 data block cr 00
6 238 data block cr 00
6 239 data block scur 00000000877E4490
5 rows selected.If we compare IO statistic, we see that more physical IO on Node 1 than Node 2. And “gc cr blocks received” value is 8.
Node 1: IO details
SQL> SELECT statistic_name, value
FROM v$segstat
WHERE dataobj# = &data_object_id and value > 0;
Enter value for data_object_id: 93861
old 3: WHERE dataobj# = &data_object_id and value > 0
new 3: WHERE dataobj# = 93861 and value > 0
STATISTIC_NAME VALUE
---------------------------------------------------------------- ----------
logical reads 80
db block changes 32
physical writes 8
physical write requests 4
space used 9257
space allocated 65536
6 rows selected.
Node 2: IO details
SQL> SELECT statistic_name, value
FROM v$segstat
WHERE dataobj# = &data_object_id and value > 0;
Enter value for data_object_id: 93861
old 3: WHERE dataobj# = &data_object_id and value > 0
new 3: WHERE dataobj# = 93861 and value > 0
STATISTIC_NAME VALUE
---------------------------------------------------------------- ----------
logical reads 48
db block changes 16
physical reads 6
physical writes 1
physical read requests 2
physical write requests 1
gc cr blocks received 8
gc current blocks received 1
8 rows selected.
Used Scripts:
[oracle@ocmnode2 ~]$ cat obj_buffer.sql
SELECT FILE#, BLOCK#, DECODE(CLASS#,
1, 'DATA BLOCK',
2, 'SORT BLOCK',
3, 'SAVE UNDO BLOCK',
4, 'SEGMENT HEADER',
5, 'SAVE UNDO HEADER',
6, 'FREE LIST',
7, 'EXTENT MAP',
8, '1ST LEVEL BMB',
9, '2ND LEVEL BMB',
10, '3RD LEVEL BMB',
11, 'BITMAP BLOCK',
12, 'BITMAP INDEX BLOCK',
13, 'FILE HEADER BLOCK',
14, 'UNUSED',
15, 'SYSTEM UNDO HEADER',
16, 'SYSTEM UNDO BLOCK',
17, 'UNDO HEADER',
18, 'UNDO BLOCK') "CLASS TYPE", STATUS, LOCK_ELEMENT_ADDR
FROM V$BH
WHERE OBJD= &OBJID
ORDER BY 1,2,3
/[oracle@ocmnode2 ~]$ cat obj_stat.sql
SELECT statistic_name, value
FROM v$segstat
WHERE dataobj# = &data_object_id and value > 0
/
select s.name, st.value
from v$statname s, v$mystat st
where st.STATISTIC# = s.STATISTIC#
and s.name in ('session logical reads','physical reads');
