Home » SQL & PL/SQL » Client Tools » Alter Session of Connections in JBoss Connection Pool (11gR2, RedHat 6, JBoss 5.1.0 and iBATIS 2.3.4.726)
Alter Session of Connections in JBoss Connection Pool [message #564431] Thu, 23 August 2012 12:45 Go to next message
debrucer2
Messages: 6
Registered: August 2012
Location: San Diego California
Junior Member
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 help with 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.

This is not an acceptable answer.

How do I set trace in these connections? Any ideas or suggestions?

Thanks,
David

Re: Alter Session of Connections in JBoss Connection Pool [message #564442 is a reply to message #564431] Thu, 23 August 2012 16:33 Go to previous messageGo to next message
BlackSwan
Messages: 23037
Registered: January 2009
Senior Member
since the apparent goal is to identify poorly performing SQL, consider not using connection pooling in conjunction with Quest Benchmark Factory; since they don't play nice together.
Re: Alter Session of Connections in JBoss Connection Pool [message #564443 is a reply to message #564442] Thu, 23 August 2012 16:47 Go to previous messageGo to next message
debrucer2
Messages: 6
Registered: August 2012
Location: San Diego California
Junior Member
Patient: Doctor, it hurts when I do this.
Doctor: Don't do that!

I need to trace these sessions. Benchmark Factory doesn't need the connections, it needs the trace files and they need to be created how Quest says.

Another argument could be used to scrap Quest and use AWR, provided by the vendor, Oracle. That's not how we plan on going about it.

Any other ideas?

David
Re: Alter Session of Connections in JBoss Connection Pool [message #564445 is a reply to message #564443] Thu, 23 August 2012 16:54 Go to previous messageGo to next message
BlackSwan
Messages: 23037
Registered: January 2009
Senior Member
http://www.oracle.com/technetwork/articles/oracledrcp11g-1-133381.pdf
Re: Alter Session of Connections in JBoss Connection Pool [message #564447 is a reply to message #564445] Thu, 23 August 2012 17:05 Go to previous messageGo to next message
debrucer2
Messages: 6
Registered: August 2012
Location: San Diego California
Junior Member
BlackSwan wrote on Thu, 23 August 2012 14:54
cannot use links until posted more than five times... and this is a new account


This document looks like it could contain answers; but not relevant to the JBoss application server. We are also not using MTS. I have reviewed and sent the link home to do so again; but, I do not believe this is the answer.

This has to be a relatively common requirement. It's hard to believe that other DBAs haven't resolved it.

Any other ideas anyone?
Re: Alter Session of Connections in JBoss Connection Pool [message #564448 is a reply to message #564447] Thu, 23 August 2012 17:10 Go to previous messageGo to next message
BlackSwan
Messages: 23037
Registered: January 2009
Senior Member
>This has to be a relatively common requirement.
Today was first time I heard about Quest Benchmark Factory.
Re: Alter Session of Connections in JBoss Connection Pool [message #568691 is a reply to message #564448] Mon, 15 October 2012 17:47 Go to previous message
debrucer2
Messages: 6
Registered: August 2012
Location: San Diego California
Junior Member
BlackSwan wrote on Thu, 23 August 2012 15:10
>This has to be a relatively common requirement.
Today was first time I heard about Quest Benchmark Factory.


Yes. It was pretty new to me also; however, using middle-tier connection pooling is not new. JBoss has been around for a long time Smile

We've scrapped any idea of using Quest Benchmark Factory for more than one reason. Thanks for the feedback. I do appreciate you very much.

David
Previous Topic: SQL Developer Code Formatting issue
Next Topic: need help to setup oracle for my course practice..
Goto Forum:
  


Current Time: Mon Nov 24 12:20:24 CST 2014

Total time taken to generate the page: 0.28434 seconds