Oracle RAC cache fusion principle test

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.