11i: Autoconfig and JDBC Load Balancing

Hi Everyone!!

We had severe performance issues recently with one of our RAC node.
After research we found that there were too many connections made to the affected node.

SQL> SELECT inst_id, COUNT(1) "TOTAL SESSIONS"  from gv$session group by inst_id;

INST_ID         TOTAL SESSIONS
-----------     ---------------------
      1                    1715
      2                    1598
      3                    1807
      4             2584

If you look at the number of sessions made, node 4 is having considerably high than other instances. After the investigation we found that the issue is because of the JDBC settings in the middle tier.

Autoconfig & JDBC connection String:

Autoconfig generates JDBC connection string(apps_jdbc_url) automatically. Even if you set the value of apps_jdbc_url at context XML, autoconfig generates the value on the fly.

APPS_JDBC_URL=jdbc\:oracle\:thin\:@(DESCRIPTION\=(LOAD_BALANCE\=YES)
(FAILOVER\=YES)(ADDRESS_LIST\=(ADDRESS\=(PROTOCOL\=tcp)
(HOST\=prod4.arena.com)(PORT\=1521))(ADDRESS\=(PROTOCOL\=tcp)
(HOST\=prod3.arena.com)(PORT\=1521))
(ADDRESS\=(PROTOCOL\=tcp)(HOST\=prod1.arena.com)(PORT\=1521))
(ADDRESS\=(PROTOCOL\=tcp)(HOST\=prod2.arena.com)(PORT\=1521)))
(CONNECT_DATA\=(SERVICE_NAME\=PROD)))

Looking at the DBC file, most of the 16 mid-tiers got generated with Node4 as a first entry for the parameter APPS_JDBC_URL. That was the cause of the prod4 performance issues.

Metalink searches gave us the TXK patch 6372396.
Patch readme.txt says

The generation of apps_jdbc_url during autoconfig run is now customizable and not automatic as it use to be. By setting the new context variable s_generate_jdbc_connect_descriptor to enable/disable customers can control theregeneration of the apps_jdbc_url when runing autoconfig. This allows customers to keep their existing/customized value of apps_jdbc_url even after running autoconfig.

With the The generation of apps_jdbc_url during autoconfig run is now customizable and not automatic as it use to be. By setting the new context variable s_generate_jdbc_connect_ descriptor to enable/disable customers can control the regeneration of the apps_jdbc_url when runing autoconfig. This allows customers to keep their existing/customized value of apps_jdbc_url even after running autoconfig.

We are in the process of up taking the patch 6372396. As a temporary workaround, we have modified the DBC file entry APPS_JDBC_URL after the autoconfig run.

This indicates us, its time to move the system to R12!!! Meet you all in next R12 post.