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).
