Distributing Work Among Oracle RAC Nodes for Performance

One of the fundamental features of Oracle RAC, in addition to providing redundancy, is the ability to spread workload efficiently across several machines. To achieve minimal delays for the user requests, you have to ensure that the user is connected to a node for which resources are available. Like the approach to avoid single points of failure, the load balancing approach is ideally performed on every level of your environment. Let’s have a look at how load balancing is implemented across your Oracle RAC nodes.

Client-side load balancing

Oracle has attempted to move more and more of the load-balancing configuration onto the server side, lessening the need to configure the client. With Oracle Database 11g Release 2 and the introduction of the Single Client Access Name (SCAN), this has been consequently improved. Prior to 11g Release 2 (you can still set up your clients that way), the client-side load balancing was implemented by providing a list of node addresses and setting the parameter LOAD_BALANCE=yes in the clients tnsnames.ora file:

   grid_callcenter =
     (DESCRIPTION =
   ADDRESS_LIST(
       (ADDRESS = (PROTOCOL = TCP)(HOST=ratlnx01-vip)(PORT = 1521))
       (ADDRESS = (PROTOCOL = TCP)(HOST=ratlnx02-vip)(PORT = 1521)) 

   )
       (LOAD_BALANCE = yes)
       (CONNECT_DATA =
         (SERVER = DEDICATED)
         (SERVICE_NAME = grid_callcenter)
        )
    )

The client then randomly selects an address from the address list and connects to that node’s listener. This is not actually real load balancing because it does not take any existing load of the instances into consideration. With 11g Release 2, when clients make use of the SCAN (with more than one IP address assigned to it), the client-side load balancing is moved to the Domain Name System (DNS) server. The client configuration contains only a single SCAN name, and this is resolved by the DNS to one of the assigned IP addresses and provided to the client. The client now connects to the SCAN listener running on that SCAN VIP.

Server-side load balancing

When a SCAN listener receives a connection request, it will check which is the least loaded instance for the service provided in the connect request. The local listener on this node will then get the redirected connection request, provide its VIP address to the client, and create the connection to the database instance.

For each service, you can define the connection load-balancing goal that you want the listener to use. You can use a goal of either long or short for connection load balancing. These goals have the following characteristics:

• Short Connections are distributed across instances based on the amount of time that the service is used. Use the short connection load-balancing goal for applications that have connections of brief duration.
• Long Connections are distributed across instances based on the number of sessions in each instance, for each instance that supports the service. Use the long connection load-balancing goal for applications that have connections of long duration.

Load-balancing configuration

The server parameter files (SPFILE) parameter REMOTE_LISTENER ensures cross registration of the instances with listeners on other nodes. Oracle Database 11g Release 2 RAC instances register with SCAN listeners only as remote listeners. The REMOTE_LISTENER parameter must be set to SCAN:PORT. In 11g Release 2, the listener no longer requires tnsnames.ora file entries. Because the REMOTE_LISTENER is configured to reference the SCAN:PORT, there is no need for a tnsnames.ora entry.