RE: Number of open cursors

Hello Linda

Maybe you find something like this

SQL> l
  1 select ses.sid, sys.name, ses.value   2 from v$sysstat sys, v$sesstat ses
  3 where ses.value > 200 and
  4 sys.statistic# = ses.statistic# and   5* sys.statistic# = 3
SQL> /       SID NAME
VALUE

--------- ----------------------------------------------------------------
---------
       86 opened cursors current
268
      104 opened cursors current
295
      257 opened cursors current

Ed
>
>
> Christine,
>
> Have you used any of the information in this paper to count
> the complete
> number of cursors per session to compare to the limit of
> open_cursors? I've
> gone through it, and three other MetaLink papers looking for
> a method to
> group the number of all open cursors by session to see if a
> given session is
> reaching the open_cursor limit. I'm sure someone has done
> this before, and
> I don't want to reinvent the same code (Ok, I'm lazy. :)
>
> V$open_cursors does not include dynamic cursors, and dynamic
> cursors count
> against the open_cursors limit. So for a system that has a
> significant
> number of dynamic cursors, as I have, the v$open_cursors
> does not give an
> accurate picture of open cursors per session.
>
> V$sysstat counts all the open cursors, including dynamic,
> but the view
> doesn't contain a session or sid field to group and count
> the cursors by
> session or sid.
>
> If I've missed something, please let me know.
>
> Thanks, Linda
>
> -----Original Message-----
> Sent: Wednesday, August 30, 2000 2:44 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Attached is a white paper sent to me from Oracle regarding
> all this. Those
> interested read on.........
>
> Take Care,
> Cheers!
> Christine Turner
> DBA
> IPS Sendero
> Scottsdale, Arizona 85251
>
> *************************************************************
> ***************
> ******************************
> Article-ID: <Note:76684.1>
> Circulation: REVIEW_READY (INTERNAL) ***Oracle Confidential -
> Internal Use Only***<Help:KRSTATUS.REVIEW_READY>
> Folder: PLSQL <Topics:2414.0>
> Topic: ** Miscellaneous PL/SQL articles **
> <Articles:2414.0.25.0>
> Title: Monitoring open cursors, ORA-1001
> Document-Type: BULLETIN
> Impact: MEDIUM
> Skill-Level: NOVICE
> Updated-Date: 06-APR-2000 00:23:43
> References:
> Shared-Refs:
> Authors: BBARNHAR.US
> Attachments: NONE
> Content-Type: TEXT/PLAIN
> Keywords: OPEN_CURSOR;
> Products: 11;
> Platforms: GENERIC;
>
> PURPOSE
>
> Describe some ways to monitor open cursors, and to diagnose
> ORA-1001 errors.
>
>
> SCOPE & APPLICATION
> PL/SQL programmers.
>
>
> RELATED DOCUMENTS
> Oracle 8i Reference [Data Dictionary] part A67790-01 p3-70, p3-119
>
>
>
>
> Introduction
>
> This article discusses how different cursors are managed and
> monitored
> in pl/sql. It addresses issues with the open_cursors parameter,
> the v$open_cursor view in the context of implicit, declared
> and dynamic
> cursors.
>
>
> Monitoring and managing open cursors.
>
> In PL/SQL procedures and in SQL-Plus sessions, the number of
> open cursors
> can
> limit operation. While the parameter open_cursors sets the
> limit, there
> are programming issues that can cause the ORA-1001 "maximum
> open cursors
> exceeded"
> error.
>
> Three important values are: the init.ora parameter
> open_cursors, the view
> called v$open_cursor, and the view v$sysstat.
>
> They are similar, but differ in their accounting of Dynamic Cursors.
> [ Dynamic cursors are those opened using dbms_sql.open_cursor() ]
> Here are the means to compute those values:
>
> >>> View v$open_cursor
> 'select count(*) from v$open_cursor' =>
> implicit cursors used +
> distinct explicit cursors opened +
> dynamic cursors PARSED and NOT CLOSED.
> Accumulates dynamic cursors PARSED and NOT CLOSED over a session.
> This view is available to system/manager.
> This view includes the text of open cursors - helpful
> for debugging.
> Since this view does not track unparsed (but opened)
> dynamic cursors,
> the count(*) may not show all cursors that count
> against open_cursors.
>
> >>> View v$sysstat
> 'select value from v$sysstat where statistic# = 3' =>
> implicit cursors used +
> distinct explicit cursors opened +
> dynamic cursors OPENED.
> Accumulates dynamic cursors OPENED and NOT CLOSED over a session.
> This view is available to system/manager.
> Since this view does track unparsed (but opened) dynamic cursors,
> the statistic#3 shows all cursors that count against
> open_cursors.
>
>
> >>> init.ora parameter open_cursors =
> implicit cursors used +
> distinct explicit cursors opened +
> dynamic cursors OPENED.
> Accumulates dynamic cursors OPENED and NOT CLOSED over a session.
>
>
> Here are some things to look at when encountering ORA-1001 in pl/sql:
>
> 1. Be sure that all dbms_sql cursors opened at DECLARE time
> are closed.
> Every unclosed OPEN counts against open_cursors.
> The number of open cursors can be seen this way in sql-plus:
> 'select value from v$sysstat where statistic# = 3'
>
> 2. Be aware that v$open_cursor only tracks the CUMULATIVE
> number of implicit + distinct explicit cursors in the procedure PLUS
> unclosed dynamic cursors that have been PARSED in the session.
> Note: it does /not/ include any dynamic cursors that were
> opened but not
> parsed.
> The text of the parsed, open cursors can be seen this way in
> sql-plus:
> 'select text from v$open_cursor'
>
> 3. Dynamic cursors persist from run-to-run in a session, but are not
> closeable after a procedure has completed. This can accumulate and
> error-out with open_cursors after a number of runs.
> They will not appear in v$open_cursors after a session.
>
>
> Here are two code snippets that can help diagnose ORA-1001.
> It shows the
> text
> lines for each cursor.
>
> -- snippet 1
> declare
> cursor opencur is select * from v$open_cursor;
> ccount number;
> begin
> select count(*) into ccount from v$open_cursor;
> dbms_output.put_line(' Num cursors open is '||ccount);
> ccount := 0;
> -- get text of open/parsed cursors
> for vcur in opencur loop
> ccount := ccount + 1;
> dbms_output.put_line(' Cursor #'||ccount);
> dbms_output.put_line(' text: '|| vcur.sql_text);
> end loop;
> end;
>
> -- snippet 2
> select value, name from v$sysstat where statistic# in (2,3);
>
>
>
>
> -----Original Message-----
> From: Linda Hagedorn [SMTP:Linda_at_pets.com]
> Sent: Wednesday, August 30, 2000 1:24 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Number of open cursors
>
> I'd like to include the dynamic cursor counts since they
> count against
> open_cursors.
>
> I set Brians query to report the sessions with more than 20
> open cursors.
>
> select SID, count(sid)
> from v$open_cursor
> group by sid
> having count(sid) > 20
> order by 2 desc ;
>
> Any ideas on how to get the total number of open cursors,
> dynamic included,
> for a session?
>
> Thanks, Linda
>
> -----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
>
>
>
> --
> 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: Christine Turner
> INET: christine.turner_at_ips-sendero.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
Received on Thu Aug 31 2000 - 02:38:54 CDT