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" <[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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Angel Munte Salvador
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).
