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" <[EMAIL PROTECTED]>
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 <[EMAIL PROTECTED]>
> >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> >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: [EMAIL PROTECTED]
> >
> >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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
