Oracle and "Maximum open cursors exceeded"
Thierry Rouget
Hi,
I am using Weblogic 7.0sp2 with Oracle 9.2.0. Since we are using manual JTA
transactions and the 9.2 drivers are buggy in that respect, we are using the
9.0.1 thin drivers delivered with weblogic.
The problem I have is that after a while, we get the now classic "Maximum open
cursors exceeded" error on connections from our connection pool (used through
a
TX datasource). I have of course checked all our JDBC code and it is fine. We
do not leave any statement/connection open. In fact, I am certain that the
problem is not caused by our applicative code.
The reason I am so positive is that the numbers of open (cached) cursors is
growing, even though there is no activity on our application (I mean no
activity at all). The number of cursors is regurlarly increasing by one
every 5 minutes until it reaches the maximum allowed for a session.
I have listed the statements corresponding to the opened cursors (they
do not belong to our code, as you might have guessed):
SELECT sysdate, user from dual
select longdbcs from javasnm$ where short = :1
select longname from javasnm$ where short = :1
As you can see, there are only three different statements. You can get
the statements from the system view v$open_cursor for a given session
but it will only give one row per different statement. If you want to know
the # of opened cursors in your cursor, use v$sesstat with statistic# = 3
(opened cursor current).
I suspect something is wrong in the connection testing done by weblogic
for the pool (I have activated test on reserved connections and test table
name is "dual") that leaves a resultset/statement behind. What is weird
though is that the refresh period is still 0 (not 5 minutes as you would
expect from the cursor growth rate...).
I would not say that it is an Oracle bug (as stated in some BEA FAQ I read)
since our application JDBC code does not exhibit the same problem. The
problem appeared with recent version of WebLogic for which the session
cursor cache is enabled, I suppose for performance reasons - this
is set by isssuing "ALTER SESSION SET SESSION_CACHED_CURSORS = ...".
Talking about this, does anybody know to which value WebLogic sets this
parameter when intializing the connection (this is neither
documented/configurable)?
Up to now, I have come up with possibly two workarounds, neither of which
is satisfying:
- resetting the pool from time to time
- issuing "ALTER SESSION SET SESSION_CACHED_CURSORS = 0" when I get a
connection from the pool. I have not tested this one personally (read
in a newsgroup that someone else did successfully) but it is supposed
to reset the cursor cache that is causing the trouble.
Any help will be greatly appreciated,
Regards,
Thierry.
Joseph Weinstein
Thierry Rouget wrote:
Hi. We don't make those queries either. I suspect they are internal to the
oracle driver. One thing you can try is to set the size of the pool's
statement cache to zero. Oracle will retain cursors for every statement we
cache. The alternative is also to tell the DBMS to allow a given session
more cursors.
Joe
Slava Imeshev
All you need to do to fix the problem is to increase Oracle instance
open_cursors parameter. The default value is too low, especially if
you use weblogic's cached prepared statements. This value should
be high enough to accommodate the demand. I saw a configuration
where open_cursors=2000. Your DBA should know how to do this.
Hope this helps,
Regards,
Slava Imeshev
"Thierry Rouget" <thierry...@mastercard.com> wrote in message
news:3f0ed3fe$1...@newsgroups.bea.com...
Thierry Rouget
>One thing you can try is to set the size of the pool's statement cache to zero.
I have tried that and it does not work. The session's opened cursors number grows
at the rate of 1 more every 5 minutes, even if the pool's statement cache size
is set to 0 and there is absolutely no activity on my application. About your
remark that this is a driver problem, I have a really hard time believing that
the driver itself generates some activity on its own every 5 minutes. Do you mean
it start its own thread?
>The alternative is also to tell the DBMS to allow a given session more cursors.
I have done this of course but this only delays the problem. Furthermore, resetting
the connection pool periodically (with the command line admin utility) does not
help either as it does not close the open connections/sessions (bug?).
This problem seems to be specific to the WebLogic connection pool. Have you investigated
the matter with Oracle?
Regards,
Thierry.
PS: What about the "ALTER SESSION SET SESSION_CACHED_CURSORS = ..." ? Is it set
by the WebLogic connection pool? If yes, to which value?
Stephen Felts
Weblogic has no code that does the
select longdbcs from javasnm$ where short = :1
select longname from javasnm$ where short = :1
The
SELECT sysdate, user from dual
looks a bit like what you might provide for the statement to
execute for test on reserve. However, if it doesn't
match what you have in your configuration or your
application is doing no work (so you are not reserving
any connections), then it's not related to the testing.
So it looks like it is coming from the driver or some other
software.
"Thierry Rouget" <thierry...@mastercard.com> wrote in message news:3f25...@newsgroups.bea.com...
Thierry Rouget
>Weblogic has no code that does the
>select longdbcs from javasnm$ where short = :1
>select longname from javasnm$ where short = :1
I really wonder where these come from...
>So it looks like it is coming from the driver or some other software.
Any idea what other driver to try (to connect to Oracle 9.2.0 and with XA support),
at least for testing that the problem is driver related? Jdriver?
Regards,
Thierry.
Slava Imeshev
> "Stephen Felts" <St...@spam.com> wrote:
> >Weblogic does not do any ALTER SESSION.
> Ok, thanks for looking that up for me.
>
> >Weblogic has no code that does the
> >select longdbcs from javasnm$ where short = :1
> I really wonder where these come from...
That's is done by oracle itself.
> >So it looks like it is coming from the driver or some other software.
> Any idea what other driver to try (to connect to Oracle 9.2.0 and with XA
support),
> at least for testing that the problem is driver related? Jdriver?
AFAIK it's normal behavior. What's the problem with it?
Regards,
Slava Imeshev
Deyan D. Bektchiev
Although it is done by Oracle itself with the thin driver and inside EJB
transactions we had issues when the prepared statement was not properly
closed when the connection that produces the statement is closed (CR103603).
The solution in our case was to explicitly close the ResultSet that we
had reference on since the prepared statement reference was only held
inside the driver itself and only then we got rid of the cursors leak.
Another solution that could work is to use the OCI driver since it would
use the C API directly for most internal driver calls (start, end,
prepare, etc.) and not plain Java as the thin driver does.
HTH,
Dejan
Slava Imeshev
Closing opened JDBC resources exlicitely is a common sence,
it's not even a FAQ...
As for prepared statments - having weblogic prepared statement
turned on does increase requirementes towards the oracle instance's
max_cursors parameter. Roughly it should be
max_cursors = size_of_the_wl_connection_pool *
size_of_the_preared_statement_cache
Regards,
Slava Imeshev
"Deyan D. Bektchiev" <dejan_b...@appl.net> wrote in message
news:3f29...@newsgroups.bea.com...
Deyan D. Bektchiev
were getting a result set and not closing it.
The connection itself was being closed very shortly after but the
prepared statement that the Oracle driver had created internally was
never closed as it should have been.
So over the course of 5-6 hours we were getting more than 10,000 open
cursors (with 20 connections in the pool and max statement cache of 50)
for the same statement which is used by the driver itself to get the
metadata.
This same scenario without a global (EJB) transaction would behave
properly and the statements would be closed but it was only leaking
those cursors when there was a global transaction.
This was the source of my advice, because not all the time you _have_ to
close all ResultSets -- according to the spec all of them should be
closed when either you close the connection or the prepared statement,
but there are cases when this can lead to the described behavior.
--dejan
Thierry Rouget
"Deyan D. Bektchiev" <dejan_b...@appl.net> wrote:
>Of course it is common sense to close JDBC resources but at one point
>we were getting a result set and not closing it.
>This same scenario without a global (EJB) transaction would behave
>properly and the statements would be closed but it was only leaking
>those cursors when there was a global transaction.
>
>This was the source of my advice, because not all the time you _have_ to
>close all ResultSets -- according to the spec all of them should be
>closed when either you close the connection or the prepared statement,
>but there are cases when this can lead to the described behavior.
There are similarities with the problem I experience:
- I am using global transactions (manually through JTA)
- I am not closing the result sets explicitely (I expected as you mentioned that
closing both
the prepared statements and the connections would be enough).
However, what I do not understand in my situation is why the number of opened
cursors
in the session keeps growing (quite regularly at the rythm of one every 5 minutes)
even if the
application stops completely (i.e. it does not open any connection nor run any
statement)?
Closing the result set explicitely will require a significant amount of work in
our application so I would like to be kinda sure it will be worth it.
Deyan D. Bektchiev
I'm not sure if closing the ResultSets will solve your issue. Its just
some types of ResultSets that apparently didn't get closed in our case
so it was very easy to isolate and fix.
What you can try is use the OCI driver just for a test and see if it
will help.
You can try to reproduce the issue by using the JDBC XA driver in a
standalone program (outside the server) and monitor the number of cursors.
Your last resort will be to open a case with BEA and Oracle support and
have them help you narrowing down the issue.
--dejan
Slava Imeshev
"Thierry Rouget" <thierry...@mastercard.com> wrote in message
news:3f2a...@newsgroups.bea.com...
>
> "Deyan D. Bektchiev" <dejan_b...@appl.net> wrote:
> >Of course it is common sense to close JDBC resources but at one point
> >we were getting a result set and not closing it.
> > ...
> >This same scenario without a global (EJB) transaction would behave
> >properly and the statements would be closed but it was only leaking
> >those cursors when there was a global transaction.
> >
> >This was the source of my advice, because not all the time you _have_ to
> >close all ResultSets -- according to the spec all of them should be
> >closed when either you close the connection or the prepared statement,
> >but there are cases when this can lead to the described behavior.
>
>
> There are similarities with the problem I experience:
> - I am using global transactions (manually through JTA)
> - I am not closing the result sets explicitly (I expected as you mentioned
that
> closing both
> the prepared statements and the connections would be enough).
First, to be on a safe side, I would make sure that Connections, Statements,
PreparedStatements and ResultSets are closed explicitly :)
> However, what I do not understand in my situation is why the number of
opened
> cursors
> in the session keeps growing (quite regularly at the rhythm of one every 5
minutes)
> even if the
> application stops completely (i.e. it does not open any connection nor
run any
> statement)?
That suspiciously looks like the connection pool refresh. Could you post
extract
from your config.xml in part of the connection pool you use, and the
datasource,
if any? We will try to find out what's up.
> Closing the result set explicitly will require a significant amount of
work in
> our application so I would like to be kinda sure it will be worth it.
That should not be a big deal. If you have a reusable helper class, it would
be one line per ResultSet.
Thanks,
Slava Imeshev
Galen Boyer
>
> I am using Weblogic 7.0sp2 with Oracle 9.2.0. Since we are
> using manual JTA transactions and the 9.2 drivers are buggy in
> that respect, we are using the 9.0.1 thin drivers delivered
> with weblogic.
Using earlier versions of drivers to access later versions of
Database servers seems like a source of pain to me.
There aren't any 9.2 drivers you could find and test out? How
bout from Oracle directly?
--
Galen Boyer
Slava Imeshev
"Galen Boyer" <galen...@hotpop.com> wrote in message
news:usmogg...@standardandpoors.com...
> On 11 Jul 2003, thierry...@mastercard.com wrote:
> > I am using Weblogic 7.0sp2 with Oracle 9.2.0. Since we are
> > using manual JTA transactions and the 9.2 drivers are buggy in
> > that respect, we are using the 9.0.1 thin drivers delivered
> > with weblogic.
>
> Using earlier versions of drivers to access later versions of
> Database servers seems like a source of pain to me.
Oracle intiroduced change of behaivior in its 9.2.0 JDBC
driver related to local transaction handling, that makes it
unusable for TX/XA Datasources. 9.0.2 version of the driver
works fine with Oracle 9.2.0.
> There aren't any 9.2 drivers you could find and test out? How
> bout from Oracle directly?
:)
Regards,
Slava Imeshev