Oracle RAC Cache Fusion principle Test

Oracle RAC Cache Fusion is the most central working mechanism of RAC. He took all the instances of the SGA virtual into a large SGA area, each time the different instances request the same data block, the data block will need to be passed between the instances.

When did it pass?

Assuming that the RAC has 4 nodes, one of the nodes running a SQL is a full table scan of a table, this time the node to load the table data into the cache;
Mode 1: Other nodes assume that the same data block takes the data from the first node, which is required.
Mode 2: Or the first node push.

Experiment Settings:

1. Clear the share pool and databuffer of the 4 nodes. In fact, when the first node is cleared. The other nodes have been cleared, as can be seen from the time the purge statement was run. Clear the table below to see if there are any tables in data buffer to cache the SQL that will run.

2. Run a SQL on the first node, and on the other nodes, see if the SQL table is cached in data buffer, and the dummy is provided that the block is actively pushed.

Experimental conclusion:

The data block is pushed from the node running to the other node, although the RAC makes more than a few times the resources used. But because of the cache fusion feature, does the system have an elevation or an unknown number after RAC.

---Cleanup of 4 nodes (54,55,56,57) Shared_pool and Buffer_cache

Share pool and data buffer are now cleared on node 54
Sql> alter system flush Shared_pool;
The system has changed.
Sql> alter system flush Buffer_cache;
The system has changed.

---Run identically on other node 55,56,57

---in the 54th node test statement
Sql> SELECT COUNT (1)
2 from Mm_distribution W
3 WHERE w.data_area like ' 03 '
4 | | ‘%‘
5 and W.create_date > to_date (' 2013-01-01 ', ' yyyy-mm-dd ');
Time used: 00:00:02.40
Run a plan
----------------------------------------------------------
Plan Hash value:3507380501
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
| 0 |                 SELECT STATEMENT |     |    1 |  13 | 5209 (2) |       00:01:03 |       | |
|  1 |                 SORT AGGREGATE |     |    1 |            13 |          |       |       | |
|   2 |                 PARTITION LIST all| |   43668 |  554k| 5209 (2) |     00:01:03 |     1 | 2 |
|* 3 | TABLE ACCESS full| mm_distribution |   43668 |  554k| 5209 (2) |     00:01:03 |     1 | 2 |
-------------------------------------------------------------------------------------------------------
predicate information (identified by Operation ID):
---------------------------------------------------
3-filter ("W". " Create_date ">to_date (' 2013-01-01 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss ') and
"W". " Data_area "like ' 3% ')
Statistical information
----------------------------------------------------------
2997 Recursive calls
0 db Block gets
24196 consistent gets
23581 Physical Reads
0 Redo Size
334 Bytes sent via sql*net to client
338 Bytes received via sql*net from client
2 sql*net roundtrips To/from Client
Sorts (memory)
0 Sorts (disk)
1 rows processed

Sql>/

Time used: 00:00:00.28

Run a plan
----------------------------------------------------------
Plan Hash value:3507380501
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
| 0 |                 SELECT STATEMENT |     |    1 |  13 | 5209 (2) |       00:01:03 |       | |
|  1 |                 SORT AGGREGATE |     |    1 |            13 |          |       |       | |
|   2 |                 PARTITION LIST all| |   43668 |  554k| 5209 (2) |     00:01:03 |     1 | 2 |
|* 3 | TABLE ACCESS full| mm_distribution |   43668 |  554k| 5209 (2) |     00:01:03 |     1 | 2 |
-------------------------------------------------------------------------------------------------------
predicate information (identified by Operation ID):
---------------------------------------------------
3-filter ("W". " Create_date ">to_date (' 2013-01-01 00:00:00 ', ' yyyy-mm-dd hh24:mi:ss ') and
"W". " Data_area "like ' 3% ')
Statistical information
----------------------------------------------------------
0 Recursive calls
0 db Block gets
23554 consistent gets
0 physical Reads
0 Redo Size
334 Bytes sent via sql*net to client
338 Bytes received via sql*net from client
2 sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
1 rows processed

---See if the SQL in Share_pool is synchronized in 4 nodes
Select Sql_text from V$sql s where sql_text like '%mm_distribution% ';

---View test buffer_cache on 4 nodes. can see that the other 3 nodes have synchronized cache data
Select COUNT (b.object_name)
From Sys.v_x$bh A, user_objects b
where a.obj = b.object_id
and b.object_name = ' mm_distribution '
and a.state <> 0;---state=0 represents free. Other means occupied
COUNT (B.object_name)
--------------------
23543

Oracle RAC Cache Fusion principle Test