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_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: