Client-Side Load Balancing vs SCAN

Some Days Ago I was reviewing a problem in a 2-nodes Oracle RAC 12c, where one instance had more sessions than the sessions in instance  2. Clearly, there was a problem in the load balancing,

A bad load balancing can be caused mainly by:

• Not using SCAN
• Using Client-Side Load Balancing 
• Bad configuration of the local_listener, remote_listener or listener_networks in each instance
• Bad configuration of the SCAN and its listeners/VIPs
• Configuration of the "Connection Load Balancing Goal" and "Runtime Load Balancing Goal" parameters in the Service
• Configuration of the Service (Running in some nothers only)
• High Load in one (or some) node(s).

In other article I will discuss each by each, but in this blogpost I will limit the content to compare Client-Side load balancing and SCAN. The "Client-Side Load Balancing" is not actually a correct balancing method, it just a random way to create the session. Oracle documentation defines Client-Side Load Balancing as:

"Client-side load balancing is defined in your client connection definition (tnsnames.ora file, for example) by setting the parameter LOAD_BALANCE=ON. When you set this parameter to ON, Oracle Database randomly selects an address in the address list, and connects to that node's listener."
In this example I configured tnsnames.ora to use Client-Side Balancing:

orcl_cslb =
   (DESCRIPTION =
    (LOAD_BALANCE=ON)
     (ADDRESS = (PROTOCOL = TCP)(HOST =rac1-vip)(PORT = 1521))
     (ADDRESS = (PROTOCOL = TCP)(HOST =rac2-vip)(PORT = 1521))
    (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = orcl)
     ))

The session will be created randomly between rac1-vip and rac2-vip, the result is the following:

Test 1:
select inst_id, count(*) from gv$session where username='DGOMEZ' group by inst_id

   INST_ID   COUNT(*)
---------- ----------
         1        143
         2        157
Test 2:
select inst_id, count(*) from gv$session where username='DGOMEZ' group by inst_id

   INST_ID   COUNT(*)
---------- ----------
         1        162
         2        138
Test 3:
select inst_id, count(*) from gv$session where username='DGOMEZ' group by inst_id

   INST_ID   COUNT(*)
---------- ----------
         1        158
         2        142
Test 4:
SQL> select inst_id, count(*) from gv$session where username='DGOMEZ' group by inst_id

   INST_ID   COUNT(*)
---------- ----------
         1        155
         2        145

Result: I used only 300 Sessions for this test. It's clearly that using Client-Side Balancing (randomly) creates the session close to a correct load balancing (for sessions), but still there is some margin of error.


Now I will replicate a curious configuration I saw in a customer, where 2 entries were the 2 VIPs of the SCAN + each VIP of each node, this seems to be a combination of Client-Side Load Balancing + SCAN.  

Entry in tnsnames.ora:

orcl_cslb =
   (DESCRIPTION =
    (LOAD_BALANCE=ON)
     (ADDRESS = (PROTOCOL = TCP)(HOST =192.178.5.35)(PORT = 1522))
     (ADDRESS = (PROTOCOL = TCP)(HOST =192.178.5.36)(PORT = 1522))
     (ADDRESS = (PROTOCOL = TCP)(HOST =rac1-vip)(PORT = 1521))
     (ADDRESS = (PROTOCOL = TCP)(HOST =rac2-vip)(PORT = 1521))
    (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = orcl)
     ))

The result is this:
Test 1:  
SQL> select inst_id, count(*) from gv$session where username='DGOMEZ' group by inst_id

   INST_ID   COUNT(*)
---------- ----------
         1        186
         2        114
Test 2:
SQL> select inst_id, count(*) from gv$session where username='DGOMEZ' group by inst_id

   INST_ID   COUNT(*)
---------- ----------
         1        149
         2        151
Test 3:
SQL> select inst_id, count(*) from gv$session where username='DGOMEZ' group by inst_id

   INST_ID   COUNT(*)
---------- ----------
         1        193
         2        107
Test 4:
SQL> select inst_id, count(*) from gv$session where username='DGOMEZ' group by inst_id

   INST_ID   COUNT(*)
---------- ----------
         1        176
         2        124

Result: The result is even worse than using only Client-Side Load Balancing. Some times less is more, perhaps who configured this was trying to do a super load balancing algorithm :)


Finally let's test how SCAN works.

Entry in tnsnames.ora:

orcl_cslb =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST =rac-scan)(PORT = 1521))
    (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = orcl)
     ))

 Test 1:
SQL> select inst_id, count(*) from gv$session where username='DGOMEZ' group by inst_id

   INST_ID   COUNT(*)
---------- ----------
         1        148
         2        152
 Test 2:
SQL> select inst_id, count(*) from gv$session where username='DGOMEZ' group by inst_id

   INST_ID   COUNT(*)
---------- ----------
         1        150
         2        150
 Test 3:  
SQL> select inst_id, count(*) from gv$session where username='DGOMEZ' group by inst_id

   INST_ID   COUNT(*)
---------- ----------
         1        148
         2        152

Result: The result was almost perfect, very few margin of error, and even in a case it was perfect (150-150).