Oracle RAC Cache Fusion is the core work of RAC, which takes all instances of the SGA virtual into a large SGA area, which needs to be passed between instances whenever different instances request the same data block. So when is it going to pass? Plus RAC has 4 nodes, one of the nodes executed a SQL is a full table scan a table, this time the node to load the table data into the cache, the other node if the need for the same data block will take the first node of the data, it is necessary to go, or the first node push it?
Experiment Settings:
1. Empty 4 nodes of the share pool and databuffer, in fact, when the first node is cleared, the other nodes have been cleared, from the time to execute the purge statement can be seen. Clear the table below to see if there are any tables in data buffer that will cache the SQL to be executed.
2. Execute a SQL on the first node, and on the other nodes, see if the SQL table is cached in data buffer, and if so, the block is actively pushed.
Experimental conclusion:
The data block is pushed from the node executed to the other node, although the RAC makes use of the resources several times, but because of the cache fusion feature, the system system after the RAC has been improved or unknown number.
---Cleanup of 4 nodes (54,55,56,57) Shared_pool and Buffer_cache
Now cleanup share pool and data buffer on node 54
Sql> alter system flush Shared_pool;
The system has changed.
Sql> alter system flush Buffer_cache;
The system has changed.
The ---also executes on the other node 55,56,57
---Test the statement on the 54th node
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
Execution 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
Execution 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 the test buffer_cache on 4 nodes, you 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, others indicate that it is occupied
COUNT (B.object_name)
--------------------
23543
