Dell’s Spotlight on RAC provides fairly extensive real time services monitoring capabilities: it can show how services are utilizing various resources across instances and how the load on each instance is driven by the various services. It also allows you to view wait time and session information for specific services.
Figure 6 Spotlight on RAC services monitoring
Getting acceptable cluster balance and avoiding overloaded instances when services are deployed requires fairly careful monitoring and adjustment of service configuration. But in general, you achieve better balance by making instances that are under-utilized available to more services and moving services off over-utilized instances.
When using cluster services to distribute workload, ensure that no instances are overloaded, while also avoiding under-utilization of instances.
RAC Load Balancing facilities
By default, Oracle uses client-side round-robin load-balancing: each request from a client connects to a different instance in the cluster and the overall result is that each instance receives approximately the same number of connections.
The client side load balancing is enabled within the TNSNAMES definition for the cluster database service. Unless the LOAD_BALANCE clause is set to OFF, round robin load balancing will be enabled across all the hosts listed in the TNS entry. So for instance the following TNSNAMES definition will result in connections requests being alternated between the hosts host1-vip, host2-vip and host3-vip:
racdb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = host1-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = host2-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = host3-vip)(PORT = 1521))
(LOAD_BALANCE = YES)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb)
)
)
For client-side load balancing to work properly all TNSNAMES definitions should be kept up to date. For instance, if you add a new instance to the cluster but fail to update TNSNAMES.ORA files on the clients, then the new instance might not pick up its share of the load (although server side load balancing will probably save the day).
To keep client-side load balancing current, it is advisable to keep all TNSNAMES entries up to date when instances are added or removed from a cluster.
If you fail to update the TNSNAMES files on all the clients, load balancing should still occur providing that server-side load balancing is enabled. Server side load balancing works at the listener level. Listeners on each node will direct connection requests to the instance or node which is currently least heavily loaded.
Server side load balancing requires that the REMOTE_LISTENER parameter in each instance be set to the name of a TNSNAMES entry that defines the listeners on all of the nodes in the cluster. When adding or removing an instance, you should update the TNSNAMES entries on all hosts in the cluster to ensure that the remote listener definitions are up to date.
Server side load balancing requires that the LOCAL_LISTENER parameter point to a TNSNAMES entry that defines all of the listeners in the database cluster.
Advanced load balancing capabilities are available within the Oracle client libraries – such as JDBC and ODP.NET. Applications that take advantage of these interfaces can obtain better load balancing – for instance load balancing at the transaction level rather than at the connection level. Oracle’s Fast Application Notification (FAN) and Load