When using SCAN, is LOAD_BALANCE still necessary?

Short answer, for balancing to the SCAN listeners from a single client, yes it does (a little).

When you look at a connection string with only a single SCAN ‘host’ there, it seems logical that the LOAD_BALANCE option is unnecessary, but  the Oracle client will replace (expand) this with an ADDRESS_LIST, containing the IP addresses it gets from the DNS server. It seems this order can not be trusted to be random. The DNS client can cache this until the TTL expires and/or the DNS server might give them in the order configured and not do this is a round-robin fashion (Round-robin DNS). Nothing will guarantee it will be returned randomly. It might look random when you do a nslookup of the SCAN address, but tracing the Oracle client it shows not to be.

“There is no standard procedure for deciding which address will be used by the requesting application, a few resolvers attempt to re-order the list to give priority to numerically “closer” networks. Some desktop clients do try alternate addresses after a connection timeout of 30–45 seconds.”

Furthermore (in 11.2), the LOAD_BALANCE option is only on by default in the DESCRIPTION_LIST, not the ADDRESS_LIST: Local Naming Parameters (tnsnames.ora).

Testing

(Test results below)

I have been reading (and translating) the following blogs as background info, but I wanted to test it myself; Client-side load balancing in Oracle RAC 11GR2 and Client Load Balancing in RAC 11.2 (Update 1 – LOAD_BALANCE=YES нужен!) (in Russian).

I tested stuff on my client by enabling tracing. The first test with LOAD_BALANCE = ON does show after the expansion of the ADDRESS_LIST that the load was balanced. It connected to the second address. When not using the option, it always showed an expansion of the addresses in the same order (probably caching) and that it only connected to first one (not having the  LOAD_BALANCE option).

Yes, when you have a 100 clients connecting to a 4 node RAC cluster using SCAN without the LOAD_BALANCE option set, you do see them distributed ~25 on each node. That’s good! This is because the SCAN listener does a random distribution (and is aware of the load if set up).

Now when I looked at the SCAN listener services establishment to nodes, I would have expected one listener to be flooded with requests, but it didn’t. The distribution was somewhat evenly between the SCAN listeners, so the requests must come in ‘randomly’.

My thought about this is, on different clients the order in which the addresses are returned are different. So the first address it encounters is different on each machine. So that is were the ‘randomly’ distribution between the SCAN listeners come from.

Conclusion

It looks like a single client will always (until TTL passes?) connect to one SCAN listener only, the first in the list the Oracle client resolves. But not all clients will have the same address returned first. This will make the distribution to the SCAN listeners load balanced. A single SCAN listener balances to different database nodes.

Now if you have a big application server using connection pooling, my guess it will flood one SCAN listener for connection requests when creating the pool and the SCAN listener will load balance it over the nodes. Also when you have ‘a lot’ of request (not using pooling), they also will all go to one SCAN listener. If this becomes a problem and/or your DNS server does not ‘rotor’ the records, do use LOAD_BALANCE = ON in the connection string when using SCAN. Otherwise, you have to assume/trust the randomness of the DNS return (nslookup) and multiple clients connecting.

Test results

… some text replaced / removed to keep output readable …

Test with LOAD_BALANCE = ON

Result: It uses the second address.

761556 : nnftrne:Using tnsnames.ora address
(DESCRIPTION=(LOAD_BALANCE=ON)
  (ADDRESS=(PROTOCOL=TCP)(HOST=scan)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL))) for name ORCL1
763299 : niotns:Calling address:
(DESCRIPTION=(LOAD_BALANCE=ON)
  (ADDRESS=(PROTOCOL=TCP)(HOST=scan)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)(CID=(...))))
765127 : nttbnd2addr:looking up IP addr for host: scan
791297 : nlad_expand_hst:Adding an ADDRESS_LIST binding
791321 : nlad_expand_hst:Expanding scan
791640 : nlad_expand_hst:Adding IP 192.168.3.66
791682 : nlad_expand_hst:Adding IP 192.168.3.60
791722 : nlad_expand_hst:Adding IP 192.168.3.65
791765 : nlad_expand_hst:Result:
(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=ORCL)(CID=(...)))
(ADDRESS_LIST=(LOAD_BALANCE=ON)
  (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.3.66)(PORT=1521))
  (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.3.60)(PORT=1521))
  (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.3.65)(PORT=1521))))
791929 : nsc2addr:
(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=ORCL)(CID=(...)))
  (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.3.60)(PORT=1521)))
792041 : nttbnd2addr:using host IP address: 192.168.3.60   <= scan 2
810040 : nttbnd2addr:using host IP address: 192.168.3.61   <= vip 1

Test 1 without LOAD_BALANCE option

Result: It uses the first address.

241600 : nnftrne:Using tnsnames.ora address
(DESCRIPTION=
  (ADDRESS=(PROTOCOL=TCP)(HOST=scan)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL))) for name ORCL2
244355 : niotns:Calling address:
(DESCRIPTION=
  (ADDRESS=(PROTOCOL=TCP)(HOST=scan)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)(CID=(...))))
247168 : nttbnd2addr:looking up IP addr for host: scan
272180 : nlad_expand_hst:Adding an ADDRESS_LIST binding
272217 : nlad_expand_hst:Expanding scan
272811 : nlad_expand_hst:Adding IP 192.168.3.66
272885 : nlad_expand_hst:Adding IP 192.168.3.60
272954 : nlad_expand_hst:Adding IP 192.168.3.65
273028 : nlad_expand_hst:Result:
(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=ORCL)(CID=(...)))
(ADDRESS_LIST=
  (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.3.66)(PORT=1521))
  (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.3.60)(PORT=1521))
  (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.3.65)(PORT=1521))))
273288 : nsc2addr:
(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=ORCL)(CID=(...)))
  (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.3.66)(PORT=1521)))
273400 : nttbnd2addr:using host IP address: 192.168.3.66   <= scan 1
291250 : nttbnd2addr:using host IP address: 192.168.3.64   <= vip 4

Test 2 without LOAD_BALANCE option

Result: It uses the first address again.

432278 : nnftrne:Using tnsnames.ora address
(DESCRIPTION=
  (ADDRESS=(PROTOCOL=TCP)(HOST=scan)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL))) for name ORCL2
433654 : niotns:Calling address:
(DESCRIPTION=
  (ADDRESS=(PROTOCOL=TCP)(HOST=scan)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)(CID=(...))))
435004 : nttbnd2addr:looking up IP addr for host: scan
444083 : nlad_expand_hst:Adding an ADDRESS_LIST binding
444099 : nlad_expand_hst:Expanding scan
444370 : nlad_expand_hst:Adding IP 192.168.3.66
444402 : nlad_expand_hst:Adding IP 192.168.3.60
444432 : nlad_expand_hst:Adding IP 192.168.3.65
444465 : nlad_expand_hst:Result:
(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=ORCL)(CID=(...)))
(ADDRESS_LIST=
  (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.3.66)(PORT=1521))
  (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.3.60)(PORT=1521))
  (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.3.65)(PORT=1521))))
444582 : nsc2addr:
(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=ORCL)(CID=(...)))
  (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.3.66)(PORT=1521)))
444635 : nttbnd2addr:using host IP address: 192.168.3.66   <= scan 1
462798 : nttbnd2addr:using host IP address: 192.168.3.61   <= vip 1

Test 3 without LOAD_BALANCE option

Result: It uses the first address again and again.

331234 : nnftrne:Using tnsnames.ora address
(DESCRIPTION=
  (ADDRESS=(PROTOCOL=TCP)(HOST=scan)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL))) for name ORCL2
333080 : niotns:Calling address:
(DESCRIPTION=
  (ADDRESS=(PROTOCOL=TCP)(HOST=scan)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)(CID=(...))))
334951 : nttbnd2addr:looking up IP addr for host: scan
356652 : nlad_expand_hst:Adding an ADDRESS_LIST binding
356676 : nlad_expand_hst:Expanding scan
357029 : nlad_expand_hst:Adding IP 192.168.3.66
357076 : nlad_expand_hst:Adding IP 192.168.3.60
357119 : nlad_expand_hst:Adding IP 192.168.3.65
357165 : nlad_expand_hst:Result:
(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=ORCL)(CID=(...)))
(ADDRESS_LIST=
  (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.3.66)(PORT=1521))
  (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.3.60)(PORT=1521))
  (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.3.65)(PORT=1521))))
357324 : nsc2addr:
(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=ORCL)(CID=(...)))
  (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.3.66)(PORT=1521)))
357395 : nttbnd2addr:using host IP address: 192.168.3.66   <= scan 1
375420 : nttbnd2addr:using host IP address: 192.168.3.63   <= vip 3

Test 4 without LOAD_BALANCE option

Result: It uses the first address again and again and again.

377932 : nnftrne:Using tnsnames.ora address
(DESCRIPTION=
  (ADDRESS=(PROTOCOL=TCP)(HOST=scan)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL))) for name ORCL2
379892 : niotns:Calling address:
(DESCRIPTION=
  (ADDRESS=(PROTOCOL=TCP)(HOST=scan)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)(CID=(...))))
381804 : nttbnd2addr:looking up IP addr for host: scan
395238 : nlad_expand_hst:Adding an ADDRESS_LIST binding
395260 : nlad_expand_hst:Expanding scan
395603 : nlad_expand_hst:Adding IP 192.168.3.66
395648 : nlad_expand_hst:Adding IP 192.168.3.60
395690 : nlad_expand_hst:Adding IP 192.168.3.65
395734 : nlad_expand_hst:Result:
(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=ORCL)(CID=(...)))
(ADDRESS_LIST=
  (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.3.66)(PORT=1521))
  (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.3.60)(PORT=1521))
  (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.3.65)(PORT=1521))))
395894 : nsc2addr:
(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=ORCL)(CID=(...)))
  (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.3.66)(PORT=1521)))
395964 : nttbnd2addr:using host IP address: 192.168.3.66   <= scan 1
413962 : nttbnd2addr:using host IP address: 192.168.3.62   <= vip 2

Happy SCANning and LOAD_BALANC(E)ing!