Table of contents
Preamble
I was looking for RAC cluster interconnect throughput and so accessed to Oracle® Database Reference 11g Release 2 (11.2) documentation and surprisingly, at least for me, I was not able to find a V$ view simply displaying it.
I was able to find it in AWR reports but as AWR package is wrapped it’s not possible to extract the formula Oracle uses:
The funny thing is that if you generate AWR report for the exact same period on another instance of your cluster you may have different result, it happens to me. They are not completely divergent but not strictly equal.
There is also no MOS (My Oracle Support) note explaining how to compute it…
Below views provide the current hardware configuration:
SQL> SET lines 200 pages 100 SQL> SELECT * FROM gv$cluster_interconnects ORDER BY inst_id,name; INST_ID NAME IP_ADDRESS IS_ SOURCE ---------- --------------- ---------------- --- ------------------------------- 1 eth0:1 169.254.5.247 NO 1 eth1:1 169.254.192.128 NO 2 eth0:1 169.254.90.252 NO 2 eth1:1 169.254.158.153 NO |
SQL> SET lines 200 pages 100 SQL> SELECT * FROM gv$configured_interconnects ORDER BY inst_id,name; INST_ID NAME IP_ADDRESS IS_ SOURCE ---------- --------------- ---------------- --- ------------------------------- 1 bond0 10.75.36.4 YES 1 bond0:1 10.75.36.48 YES 1 bond0:2 10.75.36.33 YES 1 eth0:1 169.254.5.247 NO 1 eth1:1 169.254.192.128 NO 2 bond0 10.75.36.7 YES 2 bond0:1 10.75.36.47 YES 2 bond0:2 10.75.36.49 YES 2 bond0:5 10.75.36.42 YES 2 eth0:1 169.254.90.252 NO 2 eth1:1 169.254.158.153 NO |
Then there are multiple views providing the amount of blocks (data, undo, …) exchanged between cluster instances:
SQL> SET lines 200 pages 100 SQL> SELECT inst_id,class,cr_block,current_block FROM gv$instance_cache_transfer WHERE instance IN (1,2) ORDER BY inst_id,class; INST_ID CLASS CR_BLOCK CURRENT_BLOCK ---------- ------------------ ---------- ------------- 1 1st LEVEL bmb 1566 3016 1 1st LEVEL bmb 0 0 1 2nd LEVEL bmb 0 0 1 2nd LEVEL bmb 2241 1652 1 3rd LEVEL bmb 0 0 1 3rd LEVEL bmb 15 18 1 bitmap block 0 0 1 bitmap block 0 0 1 bitmap INDEX block 0 347 1 bitmap INDEX block 0 0 1 data block 105067 392338 1 data block 0 0 1 extent map 0 0 1 extent map 0 0 1 FILE header block 347 771 1 FILE header block 0 0 1 free list 0 0 1 free list 8 8 1 save undo block 0 0 1 save undo block 0 0 1 save undo header 0 0 1 save undo header 0 0 1 SEGMENT header 0 0 1 SEGMENT header 358 1687 1 sort block 0 0 1 sort block 0 0 1 undo block 0 0 1 undo block 67 1 1 undo header 0 0 1 undo header 89024 5956 1 unused 0 0 1 unused 0 0 2 1st LEVEL bmb 9368 25428 2 1st LEVEL bmb 0 0 2 2nd LEVEL bmb 12819 10844 2 2nd LEVEL bmb 0 0 2 3rd LEVEL bmb 0 0 2 3rd LEVEL bmb 657 651 2 bitmap block 0 0 2 bitmap block 0 0 2 bitmap INDEX block 0 0 2 bitmap INDEX block 0 1821 2 data block 0 0 2 data block 612191 1999885 2 extent map 0 0 2 extent map 0 0 2 FILE header block 2114 3657 2 FILE header block 0 0 2 free list 0 0 2 free list 1459 1541 2 save undo block 0 0 2 save undo block 0 0 2 save undo header 0 0 2 save undo header 0 0 2 SEGMENT header 0 0 2 SEGMENT header 1654 6654 2 sort block 0 0 2 sort block 0 0 2 undo block 0 0 2 undo block 5897 0 2 undo header 272117 12986 2 undo header 0 0 2 unused 0 0 2 unused 0 0 |
SQL> SET lines 200 pages 100 SQL> SELECT inst_id,cr_requests,current_requests FROM gv$cr_block_server; INST_ID CR_REQUESTS CURRENT_REQUESTS ---------- ----------- ---------------- 1 105862 93810 2 870616 8415406 |
If you are wondering the difference between CR and current blocks it is clearly explained in Oracle documentation:
The Global Cache Block Access Latency chart shows data for two different types of data block requests: current and consistent-read (CR) blocks. When you update data in the database, Oracle Database must locate the most recent version of the data block that contains the data, which is called the current block. If you perform a query, then only data committed before the query began is visible to the query. Data blocks that were changed after the start of the query are reconstructed from data in the undo segments, and the reconstructed data is made available to the query in the form of a consistent-read block.
As those table contains figures since the instance started it is not easily usable to get the interconnect traffic which is an amount of information over a period of time. I was also tempted to sum GC CR Block Received Per Second and GC Current Block Received Per Second from V$SYSMETRIC_HISTORY table.
But as explained around on Internet this would not take into account the size of messages exchanged by cluster instances. The formula can be finally find in sprepins.sql file located in $ORACLE_HOME/rdbms/admin directory. This file is script to generate Statspack reports.
Estd Interconnect traffic = ((Global Cache blocks received + Global Cache blocks served)*db_block_size +(GCS/GES messages received + GCS/GES messages sent)*200)/elapsed time
This can be verified in an AWR report…
The bad news is that figures are available in GV$SYSSTAT and GV$GES_STATISTICS (or GV$DLM_MISC if you have not executed $ORACLE_HOME/rdbms/admin/catclust.sql script). Means that we will have to subtract figures to compute the interconnect throughput.
Remark:
Even if Oracle takes 200 bytes as GCS/GES message size there could be a formula to compute it but nothing confirmed officially:
SQL> SELECT SUM(kjxmsize * (kjxmrcv + kjxmsnt + kjxmqsnt)) / SUM((kjxmrcv + kjxmsnt + kjxmqsnt)) "avg Message size" FROM x$kjxm WHERE kjxmrcv > 0 OR kjxmsnt > 0 OR kjxmqsnt > 0; AVG Message SIZE ---------------- 312.751415 |
Last but not least the above formula does not take into account the MTU of network interface:
[root@server1 ~]# ifconfig eth0 eth0 Link encap:Ethernet HWaddr B4:99:BA:A7:07:2A inet addr:10.10.10.11 Bcast:10.10.10.255 Mask:255.255.255.0 inet6 addr: fe80::b699:baff:fea7:72a/64 Scope:Link UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1 RX packets:475791490 errors:0 dropped:0 overruns:0 frame:0 TX packets:172347326 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 txqueuelen:1000 RX bytes:147344045136 (137.2 GiB) TX bytes:86219877687 (80.2 GiB) |
Conclusion could be that crosschecking figures at OS level (RX / TX) versus the ones of Oracle is anyway interesting…
Script
So all is turning around this query:
SQL> SELECT DECODE(name,'gc cr blocks received','global cache blocks received','gc cr blocks served','global cache blocks served','gc current blocks received','global cache blocks received','gc current blocks served','global cache blocks served',name) AS name, SUM(VALUE) AS VALUE, TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss') AS date_taken FROM gv$sysstat WHERE inst_id=1 AND name IN ('gc cr blocks received','gc cr blocks served','gc current blocks received','gc current blocks served','gcs messages sent','ges messages sent') GROUP BY DECODE(name,'gc cr blocks received','global cache blocks received','gc cr blocks served','global cache blocks served','gc current blocks received','global cache blocks received','gc current blocks served','global cache blocks served',name) UNION SELECT name,VALUE,TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss') AS date_taken FROM gv$dlm_misc WHERE name IN ('gcs msgs received','ges msgs received') AND inst_id=1; NAME VALUE DATE_TAKEN ---------------------------------------------------------------- ---------- ------------------- gcs messages sent 5357944 2012-05-16 17:12:34 gcs msgs received 1807244 2012-05-16 17:12:34 ges messages sent 1428546 2012-05-16 17:12:34 ges msgs received 1453824 2012-05-16 17:12:34 global cache blocks received 626606 2012-05-16 17:12:34 global cache blocks served 508232 2012-05-16 17:12:34 6 ROWS selected. |
To compute interconnect transfer rate you have to execute the above query 2 times and divide the result by the elapsed time. In a more advanced script it gives:
If you want to recompute what you find in an AWR report you can use DBA_HIST_DLM_MISC and DBA_HIST_SYSSTAT hsitory tables.
Performance queries
Global Cache Service processes (LMS) statistics:
SQL> SET lines 200 pages 100 SQL> SELECT * FROM gv$current_block_server; INST_ID PIN1 PIN10 PIN100 PIN1000 PIN10000 FLUSH1 ---------- ---------- ---------- ---------- ---------- ---------- ---------- FLUSH10 FLUSH100 FLUSH1000 FLUSH10000 WRITE1 WRITE10 WRITE100 ---------- ---------- ---------- ---------- ---------- ---------- ---------- WRITE1000 WRITE10000 CLEANDC RCVDC QUEUEDC EVICTDC WRITEDC ---------- ---------- ---------- ---------- ---------- ---------- ---------- 1 25 255 355 0 0 4255 2163 60 4 0 36586 10143 478 42 39 0 0 0 23948 0 2 119 393 471 0 0 18724 6353 61 0 0 163483 58352 2277 284 623 0 0 0 65906 0 |
Latency of interconnect messages:
SQL> SET lines 200 pages 100 SQL> SELECT * FROM dba_hist_interconnect_pings WHERE snap_id=2846; SNAP_ID DBID INSTANCE_NUMBER TARGET_INSTANCE CNT_500B WAIT_500B WAITSQ_500B CNT_8K WAIT_8K WAITSQ_8K ---------- ---------- --------------- --------------- ---------- ---------- ----------- ---------- ---------- ---------- 2846 2446434119 1 1 49270 11135446 2673946 49270 10535445 2393620 2846 2446434119 1 2 49270 12011547 4889338 49270 15731115 7441966 2846 2446434119 2 1 49270 11611649 4012287 206735 62938564 29425924 2846 2446434119 2 2 206896 55943066 15317805 206896 52958767 13732654 |
Interesting one coming from racdiag.sql script:
SQL> SET lines 200 SQL> SELECT b1.inst_id, b2.VALUE "GCS CR BLOCKS RECEIVED", b1.VALUE "GCS CR BLOCK RECEIVE TIME", ((b1.VALUE / b2.VALUE) * 10) "AVG CR BLOCK RECEIVE TIME (ms)" FROM gv$sysstat b1, gv$sysstat b2 WHERE b1.name = 'global cache cr block receive time' AND b2.name = 'global cache cr blocks received' AND b1.inst_id = b2.inst_id OR b1.name = 'gc cr block receive time' AND b2.name = 'gc cr blocks received' AND b1.inst_id = b2.inst_id; INST_ID GCS CR BLOCKS RECEIVED GCS CR BLOCK RECEIVE TIME AVG CR BLOCK RECEIVE TIME (ms) ---------- ---------------------- ------------------------- ------------------------------ 1 207686 5048 .243059234 2 1089552 43020 .394841182 |
References
- Script to Collect RAC Diagnostic Information (racdiag.sql) [ID 135714.1]
- RAC Performance
- Oracle Cache fusion, private inter connects and practical performance management considerations in Oracle RAC
- Sqlplus Dashboard For RAC
- Estimated Interconnect traffic (KB) – Oracle RAC
Micka says:
Very nice article and useful to avoid AWR functionnality when we don’t have diag and tunning pack available !
Thank you !
Yannick Jaquier says:
Thanks for comment and for stopping by. Happy to have helped !! 🙂