Client Tools :: Alter Session Of Connections In JBoss Connection Pool

We have a Data Source with min_pool_size (10) and max_pool_size (20). A Data Source is by default a connection pool. By starting a transaction we are retrieving a connection from the pool (i.e., opening it to retrieve data, perform queries, inserts and updates). Our application server is JBoss. An application workflow uses many transactions to build a product. The same connection is not used by the application for the entire workflow; but, it uses and returns them to the connection pool. We do not use Java syntax like "rs.close():"... this is performed by iBATIS.

On the Linux side when we execute a "ps" command (ps -elf|grep -i ora) we see all the Oracle processes. A further refinement of that command (ps -elf|grep -i local=no)shows a list of the "waiting" connections in the connection pool. The DB may be queried with the following syntax:

SELECT schemaname, sid, serial# FROM gv$session where schemaname = 'APP_USER' order by SID;

A list of connected sid and serial numbers is returned, identifying which connections are in use. From here we are able to force the connection to trace by executing the following:

exec dbms_monitor.session_trace_enable(249, 6595, TRUE, FALSE); ! 249 and 6595 being SID and Serial# from query above

There should be no need to execute the inverse, since the connection is returned to the pool when the transaction is committed or rolled back.

exec dbms_monitor.session_trace_disable(249, 6595);

We are trying to trace in order to use the Quest Benchmark Factory. Their instructions request the following syntax be applied to each session:

alter session set events '10046 trace name context forever, level 4';

and again, the inverse should not be necessary.

alter session set events '10046 trace name context off'

When it became too cumbersome to alter each session as it appeared, we issued an "alter system" to monitor (trace) everything. The trace files filled the disk, and four hours of testing was stopped two hours in. Doing a system level trace is probably not a good idea.

My first inclination was to create a post-logon trigger to set trace in the session; however, these connections, coming from an JBoss connection pool, do not logon each time, and I presume that they are not all the "same session".

We opened a support ticket with Quest last Friday and do not have an answer yet. This was the third ticket with them, the first to get Benchmark Factory installed (the original installer did not work). The second ticket was to setting up a shared directory on Linux with a folder on Windows, a setup configuration required by their tool. The third ticket to address this issue.

They needed to contact "the developers" to answer the last two questions. Their latest suggestion is to fix ticket two so we "won't need to trace" anything. How do I set trace in these connections?