Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Open_Cursors

Re: Open_Cursors

From: Peter Sylvester <peters_no_spam_please_at_mitre.org>
Date: Tue, 13 Apr 2004 13:08:02 -0400
Message-ID: <c5h6pi$82b$1@newslocal.mitre.org>


Try running the following query on your system:

select c.user_name, count(*), s.SQL_TEXT from v$open_cursor c, v$sql s
where c.user_name is not null and c.user_name <> 'SYSTEM' and c.ADDRESS=s.ADDRESS and c.HASH_VALUE=s.HASH_VALUE group by c.user_name, s.SQL_TEXT
/

Counts greater than one likely indicate a bug in your application, in which resultsets and/or preparedstatements are not getting closed properly. From the SQL you can tell if it is localized.

You don't mention which J2EE container you use, but some of them wrap the JDBC calls and close out any dangling references when the connection is returned to the pool. I think JBOSS does this.

--Peter

Ron West wrote:
> Mark Bole <makbo_at_pacbell.net> wrote in message news:<wtHec.36238$Pr7.8852_at_newssvr29.news.prodigy.com>...
>
> Actually, this is CFMX which is a full J2EE Application. They way it
> works is you write some SQL statements in a tag, the application then
> executes the statement and returns a recordset.
>
> We have no access to the database connection pool so there is no way
> for us to leave connections open. That is all handeled by the CFMX
> Application.
>
> That was my question. We have not seen this issue on CF5 (a non-Java
> implementation) and we have seen tons of bugs in the MX product as it
> relates to the database drivers so I was just assuming that this was
> potentially another bug.
>
> A few of our customers that have the problem have the cursors set to
> 300. I did not want to go back to them and tell them to bump that up
> to say 1000 or 1500 if
>
> A) That would chew up a ton of memory and potentially cause the server
> to be unstable
>
> B) Not fix the problem, if this was just some sort of race game and by
> bumping it up to 1000 it would just extend the time between the error
> messages.
>
> Thanks for your input.
>
>

>>Another agreement, from another perspective:  with numerous "open 
>>cursor" error situations from a Java front-end, every time it has turned 
>>out to be application code that opens a cursor but doesn't close it, 
>>typically an in-house library routine for DB access.  Ask your 
>>developers to list all the ways that the application can open a 
>>cursor... you may be surprised.
>>
>>Funny developer statement:  "Gee, can't Oracle just know to close the 
>>cursor when I'm done with it?".
>>
>>If you have a connection pool of some sort, just close and re-open it 
>>every so often, that is one pragmatic way to solve the problem. ;-)
>>
>>--Mark Bole
Received on Tue Apr 13 2004 - 12:08:02 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US