Re: The number of used cursors?

Hi Tamas,

V$SQL_CURSOR is providing the number of cursors for the querying session, but Jon's query, give the current opened cursors for all session. If you restrict v$sesstat to the sid of your session and v$sql_cursor to status not 'CURNULL', you will get the same number. That's:

  select value as open_cursors
   from sys.v_$sesstat s

      , sys.v_$statname n
  where s.statistic# = n.statistic

     and n.name = 'opened cursors current'
     and s.sid=your_sid;

is the same that querying

select count(*)

   from v$sql_cursor
  where status != 'CURNULL';

HTH
Àngel

At 03.15 24/7/01 -0800, you wrote:
>Tamas:
>
>I've done some looking into this issue and I think it depends on what you
>want when you say want to know the number of "used" cursors. If you mean
>those that have been opened AND parsed, then a count from v$open_cursor will
>give you that (for the most part). If, however, you want to know the number
>of dynamic cursors that have been opened (parsed or not), then the statistic
>'opened cursors current' will give you that number. Keep in mind, too, that
>a row in v$open_cursor does not necessarily mean that the cursor is open and
>in use. For performance reasons, cursors are not "closed", but "cancelled."
>This allows most system resources to be released while still allowing the
>cursor to be reused if need be. There is currently no view that ill provide
>this information as to how many cursors are really open and in use (i.e.,
>open and not cancelled).
>
>Hope this helps.
>
>Jon Walthour
>
>----- Original Message -----
>To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
>Sent: Tuesday, July 24, 2001 5:55 AM
>
>
> > Thank you for the anwers.
> >
> > If I try Jon's version I get a big number that does not seem to be related
> > to the number of maximum open cursors ( it's much higher).
> >
> > If I query the V$SQL_CURSOR view, then I get a number the could be the
>value
> > I look for.
> >
> > Could some one shed some light on this?
> >
> > Regards
> >
> > Tamas Szecsy
> >
> > -----Original Message-----
> > Sent: Monday, July 23, 2001 5:29 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> >
> >
> > Try this:
> >
> > select sum(value) as open_cursors
> > from sys.v_$sesstat s
> > , sys.v_$statname n
> > where s.statistic# = n.statistic#
> > and n.name = 'opened cursors current';
> >
> > Jon Walthour
> >
> > >--- Original Message ---
> > >From: Szecsy Tamas <tszecsy_at_GEOMETRIA.hu>
> > >To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> > >Date: 7/23/01 9:55:24 AM
> > >
> >
> > >Hi,
> > >
> > >I would like to know the the number of used cursors at any given
> > time. Is
> > >there a select statement that does this for me? I would like
> > to decide if
> > >the growing number of concurent users for a given database has
> > reached the
> > >point where the maximum open cursor init ora parameter got to
> > be increased.
> > >
> > >Thank you in advance.
> > >
> > >Tamas Szecsy
> > >--
> > >Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > >--
> > >Author: Szecsy Tamas
> > > INET: tszecsy_at_GEOMETRIA.hu
> > >
> > >Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > >San Diego, California -- Public Internet access / Mailing
> > Lists
> > >--------------------------------------------------------------------
> > >To REMOVE yourself from this mailing list, send an E-Mail message
> > >to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
> > and in
> > >the message BODY, include a line containing: UNSUB ORACLE-L
> > >(or the name of mailing list you want to be removed from).
> > You may
> > >also send the HELP command for other information (like subscribing).
> > >
> >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Jon Walthour
> > INET: jonw_at_fuse.net
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Szecsy Tamas
> > INET: tszecsy_at_GEOMETRIA.hu
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
>
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Jon Walthour
> INET: jonw_at_fuse.net
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Angel Munte Salvador
  INET: angel_at_edreams.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).