Tuning the Cluster Interconnect - Expert Oracle RAC Performance Diagnostics and Tuning - page 468

Step 9
The overall performance of the interconnect significantly improved. The latency numbers were reduced considerably.
Script: MVRACPDnTap_blksndtime.sql
SELECT
B1.INST_ID INT,
B2.VALUE "gc cr BR",
B1.VALUE "gc cr BRT",
B4.VALUE "gc cur BR",
b3.value "gc cur BRT",
((B1.VALUE/B2.VALUE ) *10) "Avg CR BRT(ms)",
((B3.VALUE/B4.VALUE ) *10) "Avg CUR BRT(ms)"
FROM GV$SYSSTAT B1,
GV$SYSSTAT B2,
GV$SYSSTAT B3,
GV$SYSSTAT B4
WHERE B1.NAME = 'gc cr block receive time'
AND B2.NAME = 'gc cr blocks received'
AND B3.NAME = 'gc current block receive time'
AND B4.NAME = 'gc current blocks received'
AND B1.INST_ID = B2.INST_ID
AND B1.INST_ID = B3.INST_ID
AND B1.INST_ID = B4.INST_ID
ORDER BY 1
/
Avg CR Avg CUR
INT gc cr BR gc cr BRT gc cur BR gc cur BRT BRT (ms) BRT (ms)
---- -------- --------- --------- ---------- --------- ---------
1 44380 3039 70899 4969 .68 .70
2 35247 11330 62471 10061 3.21 1.61
3 42207 7406 61169 10415 1.75 1.70
4 756530 88951 1050510 95022 1.18 .90
5 35808 10791 58142 9869 3.01 1.70
6 6558172 942731 2860932 227932 1.44 .80
If any of these changes did not help in the overall latency number of the interconnect, tuning the
DB_FILE_MULTIBLOCK_READ_COUNT parameter should be considered. Latency can also be influenced by a high value
of the DB_FILE_MULTIBLOCK_READ_COUNT (MBRC) parameter. This is because this parameter determines the size of the
block that each instance would request from the other during read transfers; and a requesting process can issue more
than one request for a block, depending on the setting of this parameter, and may have to wait longer.
The number of blocks or messages transferred depends on the DB_FILE_MULTI_BLOCK_READ_COUNT *
DB_BLOCK_SIZE . Oracle transfers blocks as contiguous, and the blocks are optimized to have the same master. For a
MBRC of 16 and the database block size of 8, the global cache would still send 16 * 8 K requests logically; but the
requests can be physically batched into a few physical messages.
Although all of these are hardware level and configuration changes to the servers, the root cause of high
interconnect traffic was due to badly written, inefficient SQL statements. As new code comes into the environment, it
could worsen the network traffic, increasing latency, and this entire workshop will have to be repeated.