You can work around this by setting
MAX_COMMIT_PROPAGATION_DELAYto zero. However that does impose extra load on the cluster.
The problem arises because your connect string (in the JDBC URL) specifies a RAC service, something like this:
This typically load-balances across all nodes in an annoyingly random and yet not entirely effective way - especially when there is a sudden increase in connections. Oracle’s default load balancing mechanism is particularly badly suited to a connection pool.
What you'd really like to do is to pin application sessions to database nodes - so that you can be (reasonably) sure that a given user, once logged on, will keep on using the same RAC node (node failure excepted, of course). Just have each app server connect to its 'own' database server.
Unfortunately, the JDBC URL configuration is clustered. Whatever you set it to has to work for all nodes. So we came up with a simple workaround:
- Turn load balancing off
- Add some new IP address mappings to each app server node's
- use the 'virtual' hostnames in the JDBC URL
Now you have this URL:
and in each
/etc/hostsyou have something like:
10.20.30.1 rac1.prt.stl.int rac1 db1
10.20.30.2 rac2.prt.stl.int rac2 db2
10.20.30.3 rac3.prt.stl.int rac3 db3
On each app server, the mappings from racN to dbN are modified, so that each app server "prefers" its own RAC server, forming a stovepipe.
If the app server tier is on nodes app1, app2 and app3, the mapping is:
|RAC node||app1 mapping||app2 mapping||app3 mapping|
So in normal operation, we have a stovepipe all the way down from web to app to database tiers. Ideally we would have the web load balancer intelligently cluster like users together (eg by organisation or region) to further reduce cluster overhead, but that's currently politically unacceptable. Another time, perhaps!