Optimizing Load Balancing in Oracle RAC

Load balancing in Oracle Real Application Clusters (RAC) is a mechanism to evenly distribute workloads across all nodes in the RAC cluster. It helps ensure optimal resource utilization, improves performance, and supports high availability. By distributing user connections and application workloads evenly, RAC prevents any one node from becoming a bottleneck while maintaining overall system responsiveness.

In this section, we’ll explore the key topics related to load balancing in Oracle RAC, including different load balancing strategies, configuration methods, and best practices for monitoring and optimization.

1. Overview of Load Balancing in RAC Environments

In a typical Oracle RAC environment, client connections and database workloads are distributed across multiple RAC nodes. There are two primary forms of load balancing in RAC:

• Client-Side Load Balancing (CLB): The client chooses which RAC instance to connect to based on available RAC nodes. This distributes new connections across multiple instances.
• Server-Side Load Balancing (SSLB): The Oracle Net Listener balances connections by redirecting clients to the least-loaded RAC node.

Both client-side and server-side load balancing work together to ensure that no single RAC instance becomes overloaded, providing a scalable and highly available environment.

---

2. Connection Load Balancing (CLB) vs. Server-Side Load Balancing (SSLB)

• Client-Side Load Balancing (CLB): In this approach, the client connection is distributed randomly among RAC instances defined in the tnsnames.ora configuration. When a client initiates a new connection, it can be routed to any available RAC instance, thereby reducing the load on any particular node.
  • Example of CLB configuration in tnsnames.ora:

RACDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (LOAD_BALANCE = ON)
      (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = racnode2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = racdb)
    )
  )

• The LOAD_BALANCE = ON parameter ensures that new connections are distributed across RAC nodes.

Server-Side Load Balancing (SSLB): With server-side load balancing, Oracle Net Listener manages incoming connections and directs them to the least-loaded instance based on the current load metrics. SSLB is preferred for workloads requiring a more dynamic and optimized connection distribution based on real-time load.

• Configuring Server-Side Load Balancing: The listener handles connection load distribution automatically if configured with the LOAD_BALANCE = ON option in the listener.ora file.Example:

LISTENER =
  (DESCRIPTION_LIST =
    (LOAD_BALANCE = ON)
    (ADDRESS = (PROTOCOL = TCP)(HOST = racnode1)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = racnode2)(PORT = 1521))
  )

3. Configuring Connection Load Balancing with SCAN (Single Client Access Name)

Single Client Access Name (SCAN) is a key feature of Oracle RAC that simplifies client connection load balancing and failover. SCAN provides a single hostname for client connections to the RAC database, regardless of how many nodes are in the cluster. SCAN resolves to multiple IP addresses, and the listener distributes incoming connections across all available nodes in the cluster.

Steps to Configure SCAN-Based Load Balancing:

1. Configure SCAN in DNS:
   • SCAN should resolve to three virtual IP addresses in DNS.

SCAN_NAME.example.com.  IN  A  192.168.10.11
SCAN_NAME.example.com.  IN  A  192.168.10.12
SCAN_NAME.example.com.  IN  A  192.168.10.13

2. Verify SCAN Listener Status:

srvctl status scan_listener

3. Configure SCAN in the Client-Side tnsnames.ora File:

RACDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = scan-name)(PORT = 1521))
    (CONNECT_DATA =
      (SERVICE_NAME = racdb)
    )
  )

In this example, the scan-name will resolve to one of the available SCAN VIPs, and the SCAN listener will distribute the connections to the least-loaded RAC node.

4. Testing SCAN-Based Load Balancing:

• Connect multiple clients to test load distribution:

sqlplus user/password@RACDB

• Check the session distribution

SELECT inst_id, COUNT(*) FROM gv$session
WHERE username = 'USER' GROUP BY inst_id;

4. Tuning Workload Distribution Across RAC Nodes

Effective tuning of workload distribution in Oracle RAC requires monitoring key performance metrics and adjusting load balancing settings to optimize resource utilization across RAC nodes.

4.1 Adjusting Workload Distribution Using Services

Services provide finer control over workload distribution by routing specific workloads to designated RAC nodes.

• Create a Service for Workload Balancing:

srvctl add service -d racdb -s oltp_service -r racnode1,racnode2 -a racnode3

-r racnode1,racnode2: Preferred nodes for running the service.

-a racnode3: Available node for failover.

Start the Service:

srvctl start service -d racdb -s oltp_service

Monitor Service-Based Workload Distribution:

• Check service-specific sessions on each instance:

SELECT inst_id, COUNT(*) FROM gv$session
WHERE service_name = 'OLTP_SERVICE' GROUP BY inst_id;

4.2 Dynamic Performance Views for Tuning

• Monitor Cache Fusion Activity:

SELECT inst_id, block_transfers, waits FROM gv$cache_transfer;

Check Instance Resource Usage:

SELECT inst_id, cpu_usage, memory_usage FROM gv$instance_resource_usage;

Identify Load Balancing Inefficiencies: Use AWR (Automatic Workload Repository) reports to identify hotspots or imbalances in load distribution across nodes.

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

5. Monitoring and Optimizing RAC Load Balancing

Monitoring RAC load balancing helps ensure the optimal distribution of workloads and the identification of bottlenecks. Various tools and dynamic views are available for this purpose:

5.1 Monitoring Using Oracle Enterprise Manager (OEM)

• OEM provides graphical insights into RAC node load, session distribution, and resource consumption. Key metrics include:
  • Active Sessions per Instance
  • CPU and Memory Usage
  • Cache Fusion Activity

5.2 Monitoring Using Dynamic Views

• Monitor Sessions Across Nodes:

SELECT inst_id, COUNT(*) FROM gv$session
WHERE username IS NOT NULL GROUP BY inst_id;

Check Load Balancing Effectiveness: Use gv$load_balance_advice to see how effective load balancing is in your RAC environment:

SELECT inst_id, advice_status, cpu_effective FROM gv$load_balance_advice;

Monitor Load on Each RAC Node:

SELECT inst_id, load_percentage FROM gv$instance_load;

5.3 Optimizing Load Balancing

• Tune Interconnect: Ensure the interconnect network is optimized for low latency and high throughput to support efficient cache fusion and data sharing.
• Balance Workloads with Services: Use different services for different workloads (e.g., OLTP vs. reporting) to distribute load effectively across RAC nodes.
• Monitor Wait Events: Identify wait events in AWR reports to detect and address performance bottlenecks related to load balancing.

---

By configuring and fine-tuning both client-side and server-side load balancing, leveraging SCAN, and using services, Oracle RAC can efficiently manage workloads across multiple nodes to ensure high availability, fault tolerance, and optimal performance. Regular monitoring using tools like OEM, AWR, and dynamic performance views will help maintain an optimized load-balanced environment.