Server Side Load Balancing for Oracle RAC

Server side load balancing is achieved via REMOTE_LISTENER on each node, so make sure both LOCAL_LISTENER and REMOTE_LISTENER are configured correctly on every RAC node to guarantee server side load balancing functions properly.

From 10gR2 the service can be set up to use load balancing advisory. This means connections can be routed using SERVICE TIME or THROUGHPUT.

The tests are held under the following environment. In order to show the concept of server side load balancing more clearly, I deliberately use 11gR2 databases for testing the behaviors of server side load balancing by using VIP hosts instead of SCAN. In this case, make sure all nodes VIPs are included in REMOTE_LISTENER because SCAN is only used as default in REMOTE_LISTENER from 11gR2 onwards.

                     GI: 12.1.0.2.0
                  RAC: 11.2.0.4
Oracle Client: 11.2.0.4

This shell script is used to create multiple concurrent sessions to simulate connections from clients.

$ cat create_multi_sessions.sh
#!/bin/bash
for ((i=1; i <= 40 ; i++))
do
nohup sqlplus -S testuser/password@RACTEST_NODE1_VIP<< EOT &
begin
dbms_lock.sleep(20);
end;
/
EOT
done

The service name is RAC_TEST running on node1 and node4, while available nodes are node2 and node3.

$ srvctl status service -s RAC_TEST -d TESTDB
Service RAC_TEST is running on instance(s) TESTDB1,TESTDB4

$srvctl modify service -s RAC_TEST -d TESTDB -h

Usage: srvctl modify service -d <db_unique_name> -s <service_name> [-g <pool_name>] [-c {UNIFORM | SINGLETON}] [-P {BASIC|NONE}] [-l [PRIMARY][,PHYSICAL_STANDBY][,LOGICAL_STANDBY][,SNAPSHOT_STANDBY]] [-y {AUTOMATIC | MANUAL}][-q {true|false}] [-x {true|false}] [-j {SHORT|LONG}] [-B {NONE|SERVICE_TIME|THROUGHPUT}] [-e {NONE|SESSION|SELECT}] [-m {NONE|BASIC}] [-z <integer>] [-w <integer>] [-t <edition>]
 -d <db_unique_name> Unique name for the database
 -s <service> Service name
 -g <pool_name> Server pool name
 -c {UNIFORM | SINGLETON} Service runs on every active server in the server pool hosting this service (UNIFORM) or just one server (SINGLETON)
 -P {NONE | BASIC} TAF policy specification
 -l <role> Role of the service (primary, physical_standby, logical_standby, snapshot_standby)
 -y <policy> Management policy for the service (AUTOMATIC or MANUAL)
 -e <Failover type> Failover type (NONE, SESSION, or SELECT)
 -m <Failover method> Failover method (NONE or BASIC)
 -w <integer> Failover delay
 -z <integer> Failover retries
 -t <edition> Edition (or "" for empty edition value)
 -j <clb_goal> Connection Load Balancing Goal (SHORT or LONG). Default is LONG.
 -B <Runtime Load Balancing Goal> Runtime Load Balancing Goal (SERVICE_TIME, THROUGHPUT, or NONE)
 -x <Distributed Transaction Processing> Distributed Transaction Processing (TRUE or FALSE)
 -q <AQ HA notifications> AQ HA notifications (TRUE or FALSE)
 -h Print usage

Configure Connection Load Balancing ( CLB_GOAL )

LONG: Use the LONG connection load balancing method for applications that have long-lived connections. This is typical for connection pools and SQL*Forms sessions. It does not matter if GOAL is set or not for this condition as the point behind this setting is to balance based on number of sessions. LONG is the default connection load balancing goal.

Example to modify service RAC_TEST and set CLB_GOAL LONG.

$srvctl modify service -s RAC_TEST -d TESTDB -j LONG

or by using old fashion DBMS_SERVICE which is deprecated:

SQL>EXECUTE DBMS_SERVICE.MODIFY_SERVICE (service_name => 'RAC_TEST', clb_goal => DBMS_SERVICE.CLB_GOAL_LONG);

By using following TNS entry, we can see the sessions are balanced between nodes where services are running on preferred node 1 and node 4.

RACTEST_NODE1_VIP =
 (DESCRIPTION = 
 (ADDRESS = (PROTOCOL = TCP)(HOST =node1-vip)(PORT = 1521))
(CONNECT_DATA =
 (SERVICE_NAME = RAC_TEST)
 )
 )

SQL>select inst_id, username, count(*) 
      from gv$session 
     where username='TESTUSER' 
     group by inst_id, username;

INST_ID    USERNAME   COUNT(*)
---------- ---------- ----------
1          TESTUSER   20
4          TESTUSER   20

Run query against sys$service_metrics_tab :

20141101-10:59:59 SYS$RLBTYP('RAC_TEST', 'VERSION=1.0 database=TESTDB service=RAC_TEST { {instance=TESTDB1 percent=50 flag=UNKNOWN aff=TRUE}{instance=TESTDB2 percent=50 flag=UNKNOWN aff=TRUE} } timestamp=2014-11-01 21:59:59') SYS$SERVICE_METRICS

SHORT: Use the SHORT connection load balancing method for applications that have short-lived connections. The database uses first the GOAL setting to have PMON tell the Listener which node to prefer.

Example to modify service RAC_TEST and set CLB_GOAL short:

$srvctl modify service -s RAC_TEST -d TESTDB -j SHORT

or by using old fashion DBMS_SERVICE which is deprecated already:

SQL>EXECUTE DBMS_SERVICE.MODIFY_SERVICE (service_name => 'RAC_TEST', CLB_GOAL => DBMS_SERVICE.CLB_GOAL_SHORT);

Please note for 11.2 onwards, Oracle recommends using srvctl to manage services. This is because the service attributes are stored in CRS by srvctl, and overwrite those specified by DBMS_SERVICE. The DBMS_SERVICE procedures do not update the CRS attributes.

Configure the Load Balancing Advisory ( GOAL )

None(0):When set to 0(NONE), this disables the ONS notifications to the Client as to the load of the various nodes.

SERVICE TIME:
Attempts to direct work requests to instances according to response time. Load balancing advisory data is based on elapsed time for work done in the service plus available bandwidth to the service. Good example is for application  of internet shopping system where work requests are various lengths.

To modify  service RAC_TEST and set up the service to use SERVICE_TIME.

$srvctl modify service -s RAC_TEST -d TESTDB -j SHORT -B SERVICE_TIME

SQL>EXECUTE DBMS_SERVICE.MODIFY_SERVICE (service_name => 'RAC_TEST'
, goal => DBMS_SERVICE.GOAL_SERVICE_TIME -
, clb_goal => DBMS_SERVICE.CLB_GOAL_SHORT);

SQL>select inst_id, username, count(*) 
      from gv$session 
     where username='TETSUSER' 
     group by inst_id, username;

INST_ID    USERNAME  COUNT(*)
---------- --------- ----------
1          TESTUSER   28
4          TESTUSER   12

Run query against sys$service_metrics_tab :

20141101-10:55:29
SYS$RLBTYP('RAC_TEST', 'VERSION=1.0 database=TESTDB service=RAC_TEST { {instance=TESTDB1 percent=74 flag=GOOD aff=TRUE}{instance=TESTDB2 percent=26 flag=GOOD aff=TRUE} } timestamp=2014-11-01 21:55:29')
SYS$SERVICE_METRICS

THROUGHPUT:
Attempts to direct work requests according to throughput. The load balancing advisory is based on the rate that work is completed in the service plus available bandwidth to the service. Instead of figuring out how long something takes, it is the frequency this work occurs that is used. So if node one is able to handle 10 transactions, while node two can handle 12, in the same amount of time, then the client will be told to go to node two. So even if node two will take longer to handle a specific job, it can handle more jobs at one time then node. Good example is for application of trading system where work requests are similar lengths.

To modify  service RAC_TEST and set up the service to use throughput.

$srvctl modify service -s RAC_TEST -d TESTDB -j SHORT -B THROUGHPUT

SQL> EXECUTE DBMS_SERVICE.MODIFY_SERVICE (service_name => 'RAC_TEST' -
, goal => DBMS_SERVICE.GOAL_THROUGHPUT -
, clb_goal => DBMS_SERVICE.CLB_GOAL_SHORT);

SQL>select inst_id, username, count(*) 
      from gv$session 
     where username='TESTUSER' 
     group by inst_id, username;

INST_ID  USERNAME   COUNT(*)
-------- ---------- ----------
1        TESTUSER    8
4        TESTUSER   32

20141101-10:35:28
SYS$RLBTYP('RAC_TEST', 'VERSION=1.0 database=TESTDB service=RAC_TEST { {instance=TESTDB1 percent=14 flag=GOOD aff=TRUE}{instance=TESTDB2 percent=86 flag=GOOD aff=TRUE} } timestamp=2014-11-01 21:35:28')
SYS$SERVICE_METRICS

You can see the goal settings for a service in the data dictionary and in the DBA_SERVICES, V$SERVICES, and V$ACTIVE_SERVICES views

Also see the service performance real time data in  gv$servicemetric view.