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" <[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 n
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" <[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).
RE: The number of used cursors?
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).
RE: The number of used cursors?
V$SQL_CURSOR (Oracle8i). Got this off of my V$ view poster from TUSC. I believe you just need to select count(*) for the number used. Ken. -Original Message- Sent: Monday, July 23, 2001 9:55 AM To: Multiple recipients of list ORACLE-L 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). LEGAL NOTICE Unless expressly stated otherwise, this message is confidential and may be privileged. It is intended for the addressee(s) only. Access to this E-mail by anyone else is unauthorized. If you are not an addressee, any disclosure or copying of the contents of this E-mail or any action taken (or not taken) in reliance on it is unauthorized and may be unlawful. If you are not an addressee, please inform the sender immediately. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Fowler, Kenneth R 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).
Re: The number of used cursors?
open_cursors is not a sum for the total number of users, but rather the maximum number of cursors an individual user can have open at any one time v$open_cursor SQL> desc v$open_cursor NameNull?Type --- SADDRRAW(4) SID NUMBER USER_NAMEVARCHAR2(30) ADDRESS RAW(4) HASH_VALUE NUMBER SQL_TEXT VARCHAR2(60) >From: Szecsy Tamas <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: The number of used cursors? >Date: Mon, 23 Jul 2001 05:55:24 -0800 > >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). _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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).
RE: The number of used cursors?
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).
Re: The number of used cursors?
Use the view v$open_cursor. --- Szecsy Tamas <[EMAIL PROTECTED]> a écrit : > 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). = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Vos albums photos en ligne, Yahoo! Photos : http://fr.photos.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= 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).
