• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Greenhorn
Posts: 6
Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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)
 
Rancher
Posts: 989
9
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
Sheriff
Posts: 10445
227
IntelliJ IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Jaikiran Pai
Sheriff
Posts: 10445
227
IntelliJ IDE Ubuntu
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 6
Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 989
9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 6
Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 989
9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 6
Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?

 
reply
    Bookmark Topic Watch Topic
  • New Topic