Re: too many cursors open

On Mar 14, 3:57 pm, "dean" <deanbrow..._at_yahoo.com> wrote:
> On Mar 14, 12:02 pm, DA Morgan <damor..._at_psoug.org> wrote:
>
>
>
>
>
> > dean wrote:
> > > Oracle 10g, Windows.
>
> > > We are getting a periodic error in an application that says we are
> > > opening too many cursors, and I am trying to debug this. Is there a
> > > SQL call to get a count of this number of cursors, so that one can see
> > > exactly which part of the app is doing the mischief? Or is there
> > > another approach? We don't see the error in our test machines, only at
> > > a client site.
>
> > > Any help appreciated.
>
> > > Dean
>
> > SQL> select object_name from dba_objects
> > 2 where object_name like 'GV%CURSOR%';
>
> > OBJECT_NAME
> > -----------------------------------------
> > GV_$SQL_SHARED_CURSOR
> > GV$SQL_SHARED_CURSOR
> > GV_$OPEN_CURSOR
> > GV$OPEN_CURSOR
> > GV_$SESSION_CURSOR_CACHE
> > GV$SESSION_CURSOR_CACHE
> > GV_$SYSTEM_CURSOR_CACHE
> > GV$SYSTEM_CURSOR_CACHE
> > GV_$SQL_CURSOR
> > GV$SQL_CURSOR
> > --
> > Daniel A. Morgan
> > University of Washington
> > damor..._at_x.washington.edu
> > (replace x with u to respond)
> > Puget Sound Oracle Users Groupwww.psoug.org
>
> Thanks for that. I was looking at data from the following query:
> select count(*), SQL_TEXT
> from sys.v_$open_cursor
> where SID = 67
> group by SQL_TEXT
> order by count(*) desc
>
> and I could not find anything that seemed to be a cursor leak. I
> compared the OPEN_CURSORS variable on our test machines with the
> client one and they are both set to 300, yet we don't have an issue.
> Is there some flavor of auto-extend cursors, or some other setting,
> that can interfere with this setting? I checked the spfile and the
> running values, both of them 300.- Hide quoted text -
>
> - Show quoted text -

I found the problem - let me state what it was for reference. Using ADO in Borland world (TADOQuery), if one:

1. Creates a new TADOQuery*,
2. Opens it with some query text,
3. Closes the query,
4. But does NOT delete the query object,

there is STILL an open cursor (despite closing the object). Seems to be a bug in Borland's ADO components.

Well, that's all folks.

Dean Received on Thu Mar 15 2007 - 10:49:06 CDT