Re: The number of used cursors?

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