Examining the interconnect
When Global Cache waits are high, we should first determine if the latency is primarily the result of interconnect network waits.
The best way to determine the interconnect contribution to overall performance is to use the ping utility to measure latency independently of the Oracle stack. Ping packet handling is not identical to RAC packet handling, but if ping latency is high then you can confidently assume that network responsiveness is an issue.
In Oracle 10g the view X$KSXPIA shows the private and public IP addresses being used by the current instance. In Oracle 11g this information is available in the view Gv$cluster_interconnects. The following query shows us the private interconnect IP address plus other identifying information for the current instance (this query must be run as SYS):
SQL> SELECT instance_number, host_name, instance_name,
2 name_ksxpia network_interface, ip_ksxpia private_ip
3 FROM x$ksxpia
4 CROSS JOIN
5 v$instance
6 WHERE pub_ksxpia = 'N';
Inst Host Net Private
# Name INSTANCE_NAME IFace IP
---- ------------------------- ---------------- ----- ------------
3 melclul32.melquest.dev.me MELRAC3 eth1 192.168.0.12
l.au.qsft
We can then ping the IP address from another node in the cluster to determine average latency. On a Linux system, we can use the “–s 8192” flag to set an 8K packet size so as to align with the block size of this Oracle database. On Windows the appropriate flag is “-l”:
$ ping -c 5 -s 8192 192.168.0.12
PING 192.168.0.12 (192.168.0.12) 8192(8220) bytes of data.
8200 bytes from 192.168.0.12: icmp_seq=0 ttl=64 time=0.251 ms
8200 bytes from 192.168.0.12: icmp_seq=1 ttl=64 time=0.263 ms
8200 bytes from 192.168.0.12: icmp_seq=2 ttl=64 time=0.260 ms
8200 bytes from 192.168.0.12: icmp_seq=3 ttl=64 time=0.265 ms
8200 bytes from 192.168.0.12: icmp_seq=4 ttl=64 time=0.260 ms
--- 192.168.0.12 ping statistics ---
5 packets transmitted, 5 received, 0% packet loss, time 3999ms
rtt min/avg/max/mdev = 0.251/0.259/0.265/0.020 ms, pipe 2
The ping output above indicates low latency – about .25 ms - across the interconnect.
Use the ping utility to measure the interconnect latency independently of the Oracle software stack.
Dell Software’s Spotlight on RAC presents both ping latencies and Global Cache latencies for each instance side by side, as shown in Figure 3[3].
Figure 3 Global Cache and ping latencies in Quest's Spotlight on RAC
Very high network latencies may indicate the need to tune the interconnect as outlined below. However, probably the number one “newbie” error is to inadvertently configure RAC to use the public LAN network rather than the private interconnect network. Before tuning the private interconnect, make absolutely sure that you didn’t accidentally configure RAC to use the public LAN as the interconnect network. Use the X$KSXPIA or V$CLUSTER_INTERCONNECTS view to double check the IP addresses are those associated with the interface that is connected to the switch that implements the private network.
It’s a common mistake to configure RAC to use the public network – rather than the private network – for the interconnect. Before tuning the interconnect make absolutely sure that the interconnect is private and that RAC is configured to use the correct network.
Signs of interconnect problems
Asides from high latencies – as exposed by the ping command - interconnect issues can show up as “lost” or congested blocks.
Lost blocks occur when a block is transmitted, but never received. The f