I have admittedly struggled to completely understand how RAC load balances connections. As I think I finally understand it, I thought I would post my findings.
Normally, when you find a resource that describes server side load balancing, it simply gives you a URL to use, or suggests you use the OracleDataSource with connection pooling “implicitly” enabled. I never understood how it works, though.
You *do* use the OracleDataSource with ConnectionCachingEnabled set to true. Using this configuration does not, at *physical* connection time, use what is the least loaded node (which is the assumption under which I labored for months). You simply provide the java class (or C#, etc.) with a URL that is configured with LOAD_BALANCE = TRUE, and it will round robin to each node an approximately equal number of connections. The load balancing comes *after* that.
Let’s say you create a pool with 100 connections. The URL that asks for LOAD BALANCING will then create about 50 physical connections to each node. Your application then begins requesting a connection as needed from this pool of physical connections, frequently connecting and disconnecting.
Let’s then say that a process/processes that consume(s) large amounts of CPU begins running on the first node. As new application connections requests come in, the connection manager will choose from its pool of connections based on information it has received from the ONS (Oracle Notification Services) processes on each node in the cluster. Since ONS has posted the connection manager in the java application that node 2 is less loaded, you should see less newly created “logical” connections using the first more heavily utilized node.
If you look in GV$SESSION at that point, you will still see an approximately equal number of connections to each node. However, the “logical” connections “lent” to application requests for such are taken from the second node, as it can presumably provide a better response time (if that is the service goal).