Re: Number of open cursors

Hello Linda

Please, consider that open_cursor is the per-session setting IMHO only in the rare situations you have to increase open_cursor up to 300 or even more 400

Kind regards,
Edward

> I need to find the count of cursors currently open which count against the
> open_cursors limit in init.ora, and the expected behavior when the limit
is
> reached and/or exceeded. References to documentation or dissertations are
> appreciated.
>
> According to the queries below, I have between 510 and 1871 cursors open,
> and my init.ora limit is 200.
>
> I have failures in the front end web product, BroadVision, and am unlikely
> to have the debug capabilities turned on from that side. I'm stuck trying
> to determine SQL failures from the Oracle side. SQL trace level 4 will be
> turned on in the next recycle, and I do not have control of the executing
> SQL for testing purposes.
>
> References are welcome. If I've made something unclear, please let me try
> again.
>
> Regards, Linda
>
> select count(*) from v$open_cursor;
> COUNT(*)
> ---------
> 510
>
> select value, name from v$sysstat where statistic# in (2,3) ;
> VALUE NAME
> --------- ----------------------------------------------------------------
> 4313697 opened cursors cumulative
> 1871 opened cursors current
>
> select substr(name,1,25),substr(value,1,15),isses_modifiable,
> issys_modifiable
> from v$parameter where name like '%cursor%' ;
> SUBSTR(NAME,1,25) SUBSTR(VALUE,1, ISSES ISSYS_MOD
> ------------------------- --------------- ----- ---------
> row_cache_cursors 100 FALSE FALSE
> close_cached_open_cursors FALSE TRUE FALSE
> open_cursors 200 FALSE FALSE
> cursor_space_for_time FALSE FALSE FALSE
> session_cached_cursors 0 TRUE FALSE
>
>
> Reference:
> GV$OPEN_CURSOR select inst_id,kgllkuse, kgllksnm,
> user_name, kglhdpar, kglnahsh, kglnaobj
> from x$kgllk
> where kglhdnsp = 0
> and kglhdpar != kgllkhdl;
>
> V$OPEN_CURSOR select SADDR , SID , USER_NAME ,
> ADDRESS , HASH_VALUE , SQL_TEXT
> from GV$OPEN_CURSOR
> where inst_id = USERENV('Instance');
>
>
>
>
> -----Original Message-----
> Sent: Wednesday, August 30, 2000 1:01 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Hi !
>
> THe better picture can be obtained from
> V$SYSSTAT because V$OPEN_CURSORS
> will not inlde the dynamic cursor stats
>
> SELECT name,value from V$sysstat
> where name like '%cursor%;
>
> K Gopalakrishnan
> Bangalore, INDIA
>
>
>
>
>
> ----- Original Message -----
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Sent: Wednesday, August 30, 2000 4:26 AM
>
>
> > You can issue
> >
> > SELECT COUNT(*) FROM V$OPEN_CURSOR;
> >
> > To be more specific, this can be selected for ALL cursors, or by SID.
> >
> > SELECT * FROM V$OPEN_CURSOR;
> >
> > Best regards,
> >
> > -Ari Kaplan
> > Independent Oracle DBA Consultant
> >
> > <-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
> > <-> For 380+ Oracle tips, visit: <->
> > <-> <->
> > <-> www.arikaplan.com <->
> > <-> <->
> > <-> email: akaplan_at_interaccess.com <->
> > <-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
> >
> >
> > On Tue, 29 Aug 2000, Linda Hagedorn wrote:
> >
> > > Where would I find the number of currently open cursors, instance
wide?
> > >
> > > Thanks,
> > >
> > > Linda Hagedorn
> > >
> > > --
> > > Author: Linda Hagedorn
> > > INET: Linda_at_pets.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).
> > >
> >
> > --
> > Author: Ari D Kaplan
> > INET: akaplan_at_interaccess.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).
>
>
> _________________________________________________________
> Do You Yahoo!?
> Get your free @yahoo.com address at http://mail.yahoo.com
>
> --
> Author: K Gopalakrishnan
> INET: kaygopal_at_yahoo.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).
> --
> Author: Linda Hagedorn
> INET: Linda_at_pets.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
Received on Wed Aug 30 2000 - 13:31:16 CDT