Understanding LOAD_BALANCE parameter in TNSNAMES.ORA

This parameter can be entered under DESCRIPTION_LIST, DESCRIPTION or ADDRESS_LIST. After setting LOAD_BALANCE to a positive value, the list of addresses is processed in a random sequence.

Values: on, yes, true, off, no, false.

Default: ON for DESCRIPTION_LIST. Please note that for DESCRIPTION  and ADDRESS_LIST it is OFF by default.

Don’t be confused with the keyword balance. Oracle client does not know which database node is least loaded, this parameter only chooses addresses randomly there is no real balancing here.

The real balancing is a server-side task, when you connect to the SCAN listener it finds a least loaded node and redirects the connection to that node. LOAD_BALANCE=ON will help you to distribute the load between SCAN listeners but not evenly.

In the following test scenario, we will see how behaves client connection when using LOAD_BALANCE parameter.

Client side TNS:

  CLIENT_CON =
   (DESCRIPTION =
    (LOAD_BALANCE=ON)
     (TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)
     (ADDRESS = (PROTOCOL = TCP)(HOST =10.10.10.10)(PORT = 1522))
     (ADDRESS = (PROTOCOL = TCP)(HOST =11.11.11.11)(PORT = 1522))
     (ADDRESS = (PROTOCOL = TCP)(HOST =12.12.12.12)(PORT = 1522))
     (ADDRESS = (PROTOCOL = TCP)(HOST =13.13.13.13)(PORT = 1522))
     (ADDRESS = (PROTOCOL = TCP)(HOST =14.14.14.14)(PORT = 1522))
     (ADDRESS = (PROTOCOL = TCP)(HOST =15.15.15.15)(PORT = 1522))
    (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = orclgg)
     ))

In the above connection string, I have used two other parameters. If you are not faimiliar with them, please see a brief explanation bellow:

TRANSPORT_CONNECT_TIMEOUT:

“The TRANSPORT_CONNECT_TIMEOUT parameter specifies the time, in seconds, for a client to establish a TCP connection to the database server. The default value is 60 seconds.” For more information, click here

RETRY_COUNT:

“To specify the number of times an ADDRESS list is traversed before the connection attempt is terminated.” For more information, click here         

Enable client tracing by specifying the following parameters in client sqlnet.ora file:

TRACE_LEVEL_CLIENT = USER
TRACE_FILE_CLIENT = MY_SQLNET.TRC
TRACE_DIRECTORY_CLIENT = /SQLTRACE_FOLDER
TRACE_TIMESTAMP_CLIENT = on
TRACE_UNIQUE_CLIENT = ON
DIAG_ADR_ENABLED = OFF

Try the connection using the above TNS alias and analyze generated trace file.

[10-MAR-2019 15:38:27:271] nttbnd2addr: using host IP address: 13.13.13.13
[10-MAR-2019 15:38:30:274] nttbnd2addr: using host IP address: 14.14.14.14
[10-MAR-2019 15:38:33:276] nttbnd2addr: using host IP address: 11.11.11.11
[10-MAR-2019 15:38:36:279] nttbnd2addr: using host IP address: 15.15.15.15   <- instead of 12 it chose 15
[10-MAR-2019 15:38:39:282] nttbnd2addr: using host IP address: 12.12.12.12
[10-MAR-2019 15:38:42:286] nttbnd2addr: using host IP address: 10.10.10.10
[10-MAR-2019 15:38:45:289] nttbnd2addr: using host IP address: 15.15.15.15
[10-MAR-2019 15:38:48:292] nttbnd2addr: using host IP address: 14.14.14.14
[10-MAR-2019 15:38:51:294] nttbnd2addr: using host IP address: 13.13.13.13
[10-MAR-2019 15:38:54:297] nttbnd2addr: using host IP address: 12.12.12.12
[10-MAR-2019 15:38:57:298] nttbnd2addr: using host IP address: 10.10.10.10
[10-MAR-2019 15:39:00:299] nttbnd2addr: using host IP address: 11.11.11.11
[10-MAR-2019 15:39:03:302] nttbnd2addr: using host IP address: 11.11.11.11  <- Here it used the same address again
[10-MAR-2019 15:39:06:303] nttbnd2addr: using host IP address: 14.14.14.14
[10-MAR-2019 15:39:09:306] nttbnd2addr: using host IP address: 15.15.15.15
[10-MAR-2019 15:39:12:309] nttbnd2addr: using host IP address: 12.12.12.12
[10-MAR-2019 15:39:15:312] nttbnd2addr: using host IP address: 10.10.10.10
[10-MAR-2019 15:39:18:314] nttbnd2addr: using host IP address: 13.13.13.13
[10-MAR-2019 15:39:21:315] nttbnd2addr: using host IP address: 15.15.15.15
[10-MAR-2019 15:39:24:318] nttbnd2addr: using host IP address: 13.13.13.13

From the above output we can conclude that addresses were chosen randomly.

Let’s comment LOAD_BALANCE parameter in connection string or explicitly specify LOAD_BALANCE=OFF. Increase RETRY_COUNT until 5 to see a better picture.

[10-MAR-2019 15:53:08:108] nttbnd2addr: using host IP address: 10.10.10.10
[10-MAR-2019 15:53:11:109] nttbnd2addr: using host IP address: 11.11.11.11
[10-MAR-2019 15:53:14:110] nttbnd2addr: using host IP address: 12.12.12.12
[10-MAR-2019 15:53:17:111] nttbnd2addr: using host IP address: 13.13.13.13
[10-MAR-2019 15:53:20:112] nttbnd2addr: using host IP address: 14.14.14.14
[10-MAR-2019 15:53:23:114] nttbnd2addr: using host IP address: 15.15.15.15

[10-MAR-2019 15:53:26:117] nttbnd2addr: using host IP address: 10.10.10.10
[10-MAR-2019 15:53:29:120] nttbnd2addr: using host IP address: 11.11.11.11
[10-MAR-2019 15:53:32:123] nttbnd2addr: using host IP address: 12.12.12.12
[10-MAR-2019 15:53:35:124] nttbnd2addr: using host IP address: 13.13.13.13
[10-MAR-2019 15:53:38:127] nttbnd2addr: using host IP address: 14.14.14.14
[10-MAR-2019 15:53:41:131] nttbnd2addr: using host IP address: 15.15.15.15

[10-MAR-2019 15:53:44:132] nttbnd2addr: using host IP address: 10.10.10.10
[10-MAR-2019 15:53:47:135] nttbnd2addr: using host IP address: 11.11.11.11
[10-MAR-2019 15:53:50:139] nttbnd2addr: using host IP address: 12.12.12.12
[10-MAR-2019 15:53:53:142] nttbnd2addr: using host IP address: 13.13.13.13
[10-MAR-2019 15:53:56:144] nttbnd2addr: using host IP address: 14.14.14.14
[10-MAR-2019 15:53:59:147] nttbnd2addr: using host IP address: 15.15.15.15

[10-MAR-2019 15:54:02:150] nttbnd2addr: using host IP address: 10.10.10.10
[10-MAR-2019 15:54:05:153] nttbnd2addr: using host IP address: 11.11.11.11
[10-MAR-2019 15:54:08:156] nttbnd2addr: using host IP address: 12.12.12.12
[10-MAR-2019 15:54:11:159] nttbnd2addr: using host IP address: 13.13.13.13
[10-MAR-2019 15:54:14:160] nttbnd2addr: using host IP address: 14.14.14.14
[10-MAR-2019 15:54:17:161] nttbnd2addr: using host IP address: 15.15.15.15

[10-MAR-2019 15:54:20:164] nttbnd2addr: using host IP address: 10.10.10.10
[10-MAR-2019 15:54:23:165] nttbnd2addr: using host IP address: 11.11.11.11
[10-MAR-2019 15:54:26:167] nttbnd2addr: using host IP address: 12.12.12.12
[10-MAR-2019 15:54:29:170] nttbnd2addr: using host IP address: 13.13.13.13
[10-MAR-2019 15:54:32:171] nttbnd2addr: using host IP address: 14.14.14.14
[10-MAR-2019 15:54:35:174] nttbnd2addr: using host IP address: 15.15.15.15

[10-MAR-2019 15:54:38:175] nttbnd2addr: using host IP address: 10.10.10.10
[10-MAR-2019 15:54:41:178] nttbnd2addr: using host IP address: 11.11.11.11
[10-MAR-2019 15:54:44:182] nttbnd2addr: using host IP address: 12.12.12.12
[10-MAR-2019 15:54:47:184] nttbnd2addr: using host IP address: 13.13.13.13
[10-MAR-2019 15:54:50:187] nttbnd2addr: using host IP address: 14.14.14.14
[10-MAR-2019 15:54:53:190] nttbnd2addr: using host IP address: 15.15.15.15

If you turn off LOAD_BALANCE then addresses are chosen using round-robin until one succeeds or until (RETRY_COUNT * #_of_addresses).