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.

2 comments:

Kai said...

Hai ,

Thanks for your wonderful posts.. really helpful...you've said that you have modified the DBC file entry APPS_JDBC_URL after the autoconfig run.Can u tell us what kind of modification you made ?

Thanks
Kai

Suresh Lakshmanan said...

Hi Kai,

Thanks for the comment. We have modified JDBC connection parameters in DBC file of mid-tiers. First mid-tiers first node will be node A. second mid-tiers first node will be node B and its made in round robin way. So that connection will be distributed across the nodes.

Hope this clarifies you. Let me know if you need more on this.

Thanks
Suresh