This week's book giveaway is in the OO, Patterns, UML and Refactoring forum. We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line! See this thread for details.
I'm using a XA datasource to a Oracle g11 database in my EJB (SLSB). Sometimes I get a SQLException while doing some queries, message: ORA-0392: exceeded session limit on CPU usage (Oracle g11).
The database admin has set a resource limit for each user session. I can't change this limit. Everytime the JBoss opens a new connection to the database, a new session is started. If all my queries use the same connection, they might use too much CPU time in total, so a SQLException is thrown.
I tried to solve the problem by closing the actual connection and get a new connection using the datasource (getConnection()). Afterwards I retry my statement. Now I get a new SQLException: "ORA-01012: not logged on".
Now my questions:
Is it possible, the JBoss don't realize the problem and keeps the useless connection in his pool after I released it (with close())? I didn't set the pool limits for the datasource, so the JBoss might have only one connection? And calling getConnection() on the datasource returns the same useless connection I had before?
Does anyone have some information about the pooling behavior? I want to know when the JBoss decides to create a new connection or to give me an old connection.
Any idea how to solve my problem? Can I force the JBoss to kill the useless connection? I don't want to manage my own connections using some JDBC stuff.
By the way, I'm using a JBoss AS 7.1 (standalone)
Thanks for your ideas. I tried the last days to solve my problem, but I wasn't successful.
An idle timeout reduces the probability to get a broken connection but it can't avoid it, except I put some waiting stuff in my code. But that is an ugly solution.
I tried to validate the connection (with valid-connection-checker and afterwards with check-valid-connection-sql), but it didn't help. Even with the validation there might be broken connections, because the session limit can appear while doing a query:
- get connection
- do query
- close connection
- get connection (maybe the connection I had before, the JBoss chooses one for me)
- do query and exceed session limit (because the connection was used before for other queries)
So I have to catch the last exception and do a retry. Now I get the "ORA-01012: not logged on" (despite of validation). Is this possible?
Joined: Mar 17, 2012
Valid connections check at least stops the appserver from giving you a connection that has already timed out.
Connection timeout setting helps tell the appserver not to keep connections that for a period longer than the database timeout setting.
If your connection becomes invalid then I suggest just discarding it and getting another one from the pool. It sounds like the DB session timeout you have is creating more problems than whatever problems it's trying to solve. If your application queries are taking longer than that limit then definitely the limit was not well thought out or the queries need to be optimized so as to stay withing the prescribed limits. You need to consider your application load and how likely a connection is going to become invalid in the application before deciding on what settings to use for the pool (or even considering if the pool helps depending on how long that database setting is).
As I said before, if you get a new connection from the pool the same way all the time then you shouldn't get invalid access on some call unless if the database has more weird settings that we don't know about.
Now I solved it. It wasn't a problem of a single query. I had to set some additional datasource parameters concerning the timeouts:
Not only the idle-timeout was relevant, the allocation-retry settings seemed to be important, too.
The validation settings couldn't avoid the logged off error, because it seemed, the Oracle settings didn't allow an immediate new connection, so the JBoss throwed a SQLException. I still had to wait some more seconds, before I retried with a new connection after a "RA-0392: exceeded session limit on CPU usage". However, The <idle-timeout-minutes> setting already reduced the probability for the "RA-0392: exceeded session limit on CPU usage"
Thanks for your help!
Joined: Mar 17, 2012
Helmut Neubauer wrote:... the Oracle settings didn't allow an immediate new connection, so the JBoss throwed a SQLException...
Great. You should probably request and go through all settings the DBA has put and verify that it won't affect your application in some way.
Former I forced the JBoss to create new database connections by changing my timeout settings (especially the idle-timeout-minutes). Now the time between some of my queries is very small, so the timeout settings don't affect the JBoss database connection pool.
In addition the validation settings don't seem to work:
I get the already mentioned exception after getting a new connection by:
My only ugly idea is to use a Thread.sleep() to force a timeout and with it a new connection (only one bean instance is accessing the datasource, so it will work). But I'm not happy to use thread manipulation in an EJB where it isn't allowed to. Another possible solution is to use timers (at least EJB compliant), but then I have to do much more change than adding a simple Thread.sleep.
So my questions:
- Has any one another idea to force the JBoss to open a new connection?
- Has any one an idea why my validation settings don't seem to work?
subject: Datasource connection and SQLException ORA-0392: exceeded session limit on CPU usage (Oracle g11)