This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JBoss/WildFly and the fly likes Datasource connection and SQLException ORA-0392: exceeded session limit on CPU usage (Oracle g11) Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Products » JBoss/WildFly
Bookmark "Datasource connection and SQLException ORA-0392: exceeded session limit on CPU usage (Oracle g11) " Watch "Datasource connection and SQLException ORA-0392: exceeded session limit on CPU usage (Oracle g11) " New topic
Author

Datasource connection and SQLException ORA-0392: exceeded session limit on CPU usage (Oracle g11)

Helmut Neubauer
Greenhorn

Joined: Nov 15, 2012
Posts: 6

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.

Thanks, Helmut
By the way, I'm using a JBoss AS 7.1 (standalone)
E Armitage
Rancher

Joined: Mar 17, 2012
Posts: 888
    
    9
Do not let your queries use the same connection. Get a connection from the pool, use it and return it to the pool with close.

You can tell Jboss to check for connection validity from the pool using

for Oracle.

As for the invalid logon error, how did you get the first connection that exceeded the limit and how is that different from how got the connection that had the logon error?
Jaikiran Pai
Marshal

Joined: Jul 20, 2005
Posts: 9915
    
158

Which version of JBoss AS is this? There are ways to set an idle timeout on the connection in the pool, so that they are actually closed and not held in the pool after some idle time.

[My Blog] [JavaRanch Journal]
Jaikiran Pai
Marshal

Joined: Jul 20, 2005
Posts: 9915
    
158

Just noticed that you already mentioned you are using JBoss AS 7.1. In your standalone*.xml file (the one which you are using), in the datasources system you can set the idle connection timeout for the relevant datasource(s). See this xsd for details https://github.com/wildfly/wildfly/blob/7.1.1.Final/build/src/main/resources/docs/schema/jboss-as-datasources_1_0.xsd#L538
Helmut Neubauer
Greenhorn

Joined: Nov 15, 2012
Posts: 6

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?
E Armitage
Rancher

Joined: Mar 17, 2012
Posts: 888
    
    9
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.
Helmut Neubauer
Greenhorn

Joined: Nov 15, 2012
Posts: 6

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!
Regards, Helmut
E Armitage
Rancher

Joined: Mar 17, 2012
Posts: 888
    
    9
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.
Helmut Neubauer
Greenhorn

Joined: Nov 15, 2012
Posts: 6

Unfortunately, I stumbled upon it again.

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?

 
jQuery in Action, 2nd edition
 
subject: Datasource connection and SQLException ORA-0392: exceeded session limit on CPU usage (Oracle g11)
 
Similar Threads
On DB Connections, MVC2, and Sessions
Can I use EJBQL for BMP?
java jdbc and oracle - maximum open cursors exceeded
question about getConnection(usr,pwd)
Unable to Close Connection In Case Of java.sql.Exception