Setting module and action in OC4J connection pool

From: Sidney Chen <huanshengchen_at_gmail.com>
Date: Wed, 28 Dec 2011 10:27:07 +0800
Message-ID: <CAM_ddu-CqCzbVeA9nTuedLOuenG_=SvN4XXjgnr6POGCwu--hg_at_mail.gmail.com>

Hi
We've around 2000 dedicated connection on 3 nodes rac on Linux X86_64. Most of the connections comes for the OC4J. We create around 20 service name for OC4J connection, to spread the loading on the 3 nodes. Because many connection pools in the OC4J share the same service name, some common Service have hundreds of sessions, there is no mapping from application module to db sessions. When there is performance problem in specific module and we start trouble-shooting, it's difficult to sort out which session need to be enabled sql trace.

Now we want to set the session attribute with module and action name, using

DBMS_APPLICATION_INFO.SET_MODULE, so that we can use
DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE and
DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE to do end-to-end tracing.

The OC4J connection pool provide the config validate-connection and validate-connection-statement, we can set the DBMS_APPLICATION_INFO.SET_MODULE as the validate statement. but the problem is the statement will be called to validate the connection every time before the Java code need to run a sql, there will be too many executions and impact the DB performance. A online click may call the validate statement hundreds of times. So we can't apply this change to production.

So We below question now.
1. If We want to validate the connection every 10 minutes, not every call to db, it is possible?
2. Setup hundreds of service name? use different service name for various application module, not sure if any overhead on the listener or db?

or any other suggestion, thanks.

Here comes the sample connection pool config, setting the DBMS_APPLICATION_INFO.SET_MODULE as validate statement:

<connection-pool name="BBCScheduleDSPool"
  min-connections="5"
  max-connections="30"
  validate-connection="true"
  validate-connection-statement="begin DBMS_APPLICATION_INFO.SET_MODULE ('ModuleX','ActionY');end;"
  auto-commit="false"
  used-connection-wait-timeout="30"
  inactivity-timeout="30"
  connection-retry-interval="1"
  max-connect-attempts="3">
  <connection-factory
    factory-class="oracle.jdbc.pool.OracleDataSource"     user="bbc_party"
    password="bbc_party"
    url="jdbc:oracle:thin:_at_bbctest02:1521:srv_name">   </connection-factory>
</connection-pool>

-- 
Regards
Sidney Chen


--
http://www.freelists.org/webpage/oracle-l

Received on Tue Dec 27 2011 - 20:27:07 CST