Configure Oracle Load Balancing

The most common multiple listener configuration is the case where each database instance has its own listener. However, it is also possible to define several listeners, one for each incoming protocol.
Listener load balancing is the term used to describe the distribution of connections among Oracle listeners to ensure that incoming requests do not have to wait for a listener connection. By having connections distributed among a number of listeners, no single listener is likely to be overburdened, and connection time will be faster.

SELECT network "Protocol",
      DECODE( SUM(totalq), 0, 'No Responses',
      SUM(wait)/SUM(totalq) || ' hundredths of seconds')
     "Average Wait Time per Response"
FROM 
  v$queue q, 
  v$dispatcher d
WHERE 
  q.type = 'DISPATCHER'
  AND 
  q.paddr = d.paddr
GROUP BY network;

You can define multiple mts_listener_address parameters in the init.ora file, one for each listener. For multiple listeners to be enabled for multi-threaded servers, the database init.ora file must include the mts_multiple_listeners parameter.

MTS Server was replaced by Oracle Shared Server beginning with the release of Oracle 9i. The dispatcher for each multi-threaded server registers with all listeners designated by the mts_listener_address init.ora parameters. When a connection request is made, the client interface code randomizes among listeners within the same community.
The listener redirects the client connection request to the least-used dispatcher, which connects the client to a shared server process.
Similarly, the clients that want to connect to a dedicated server randomize their connection requests among the listeners on the same protocol for that server as listed in their tnsnames.ora. The randomly selected listener spawns a server process and hands off the incoming connection to the server.

You cannot have listener load balancing with pre-started dedicated servers because a pre-started dedicated server process registers only with the listener that started it. The following diagram illustrates load balancing. Notice that you can have more than one listener for a single database of any kind. The listeners for a dedicated server must be on the same node as the server. Listeners for a multi-threaded server can be distributed on different nodes; the dispatchers are able to register with listeners across nodes.
View the code below to see an example.

If a set of databases is configured to provide equivalent service (such as a replicated database) on the network, you can use listener load balancing among several listeners that all listen for more than one instance of the database. To configure listener load balancing, the administrator must configure multiple listeners for each database. There can be multiple listeners on the same platform as the database, or, for multi-threaded servers, the listeners can be on different nodes.
The next lesson discusses how to set up connection pooling.

Before moving on to the next lesson, click the Exercise link below to see how well you understand load balancing.
Load Balancing MTS - Exercise