Oracle RAC cache fusion is the core working mechanism of RAC. It virtualizes the SGA of all instances into a large SGA zone. Each time different instances request the same data block, this data block needs to be transmitted between instances. So when will it be passed? In addition, RAC has four nodes, one of which executes an SQL statement to scan a table from the entire table. At this time, the node loads the data in this table into the cache; if the same data block is required for other nodes, the data of the first node will be retrieved. Is that required or pushed by the first node?
Lab settings:
1. Clear the share pool and databuffer of the four nodes. In fact, when the first node is cleared, all the other nodes have been cleared. You can see from the execution time of the clear statement. After clearing the table, check whether the SQL table to be executed is cached in the data buffer of each node.
2. Execute an SQL statement on the first node. Check whether the SQL table is cached in data buffer on other nodes. If yes, data blocks are actively pushed.
Experiment conclusion:
Data blocks are pushed from the executed node to other nodes. Although RAC doubles the resources used, due to the cache fusion feature, whether the system after RAC is upgraded is unknown.
--- Clear four nodes (54,55, 56,57) shared_pool and buffer_cache
Now the share pool and data buffer are cleared on node 54.
SQL> alter system flush shared_pool;
The system has been changed.
SQL> alter system flush buffer_cache;
The system has been changed.
--- Execute the same command on other nodes 55, 56, and 57
--- Test the statement on 54th nodes
SQL> SELECT COUNT (1)
2 FROM MM_DISTRIBUTION W
3 where w. DATA_AREA LIKE '03'
4 | '%'
5 and w. CREATE_DATE> TO_DATE ('2017-01-01 ', 'yyyy-mm-dd ');
Used time: 00: 00: 02.40
Execution Plan
----------------------------------------------------------
Plan hash value: 3507380501
Bytes -------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop |
Bytes -------------------------------------------------------------------------------------------------------
| 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 |
Bytes -------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
3-filter ("W". "CREATE_DATE"> TO_DATE ('2017-01-01 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss') AND
"W". "DATA_AREA" LIKE '000000 ')
Statistics
----------------------------------------------------------
2997 recursive cballs
0 db block gets
24196 consistent gets
23581 physical reads
0 redo size
334 bytes sent via SQL * Net to client
338 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
39 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>/
Used time: 00: 00: 00.28
Execution Plan
----------------------------------------------------------
Plan hash value: 3507380501
Bytes -------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop |
Bytes -------------------------------------------------------------------------------------------------------
| 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 |
Bytes -------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
3-filter ("W". "CREATE_DATE"> TO_DATE ('2017-01-01 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss') AND
"W". "DATA_AREA" LIKE '000000 ')
Statistics
----------------------------------------------------------
0 recursive cballs
0 db block gets
23554 consistent gets
0 physical reads
0 redo size
334 bytes sent via SQL * Net to client
338 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--- Check whether the SQL statements in the share_pool are synchronized on four nodes.
Select SQL _text from v $ SQL s where SQL _text like '% MM_DISTRIBUTION % ';
--- Check and test buffer_cache on four nodes. You can see that the other three nodes have synchronized cached 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 indicates free, others indicate occupied
COUNT (B. OBJECT_NAME)
--------------------
23543
