Re: Open_Cursors

From: Ron West <rwest_at_paperthin.com>
Date: 19 Apr 2004 05:13:45 -0700
Message-ID: <641efd59.0404190413.6cc26e59@posting.google.com>

Peter Sylvester <peters_no_spam_please_at_mitre.org> wrote in message news:<c5h6pi$82b$1_at_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
> /

Thank you, this was _exactly_ the kind of information that I was looking for!!

> 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.

We did have results in here for a site that was hardly used. In fact we had some entries from sites that had not been worked on since december.
One SQL statement that was common amongst the multiple sites was the following:
"set transaction isolation level read committed"

We use common code like:
<cftransaction>

  <cfquery> .. some insert statement ..</cfquery>
  <cfquery> .. some insert statement ..</cfquery>
  <cfquery> .. some insert statement ..</cfquery>

</cftransaction>

It appears that there may be a bug in this code. The transaction must be running the query and never closing the connection properly.

> 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

We use CFMX which runs on top of JRun.

Thanks again. Received on Mon Apr 19 2004 - 07:13:45 CDT