Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Number of open cursors

RE: Number of open cursors

From: Christine Turner <christine.turner_at_ips-sendero.com>
Date: Thu, 31 Aug 2000 07:28:07 -0000
Message-Id: <10605.115972@fatcity.com> 





Unfortunately...I have not completed my research on this.  I have a 
procedure that is knocking out the cursor count and hitting that dreaded 
maximum number of cursors error message.  Of course to alleviate the 
problem you can raise the number of cursors, but when is enough, enough??? 
 I am currently on a 7.3.4, NT platform and have been able to reproduce 
this problem all the way through version 8.1.6.  At this point, management 
has said enough and has instructed me to put this research on the back 
burner because of deadlines.  And of course there it has sat for the last 4 
months.  I do know however, that there is some sort of "bug" within Oracle 
regarding all this, but to prove that has been almost impossible for me 
because of time constraints.



Listed below are some commands that I used for my research and between the 
three of them the "count" of the cursors is very close.  You are correct 
though, v$sysstat doesn't give you sid...but in my case it wasn't necessary 
for me to track that.  I apologize my info isn't more informative, 
hopefully it gets you closer to an answer.



Good Luck!


Take Care,


Christine Turner


DBA



IPS Sendero


Scottsdale, Arizona  85251








set serveroutput on size 300000;



select a.sql_text


from v$session s, v$open_cursor o, v$sqlarea a
where

s.saddr=o.saddr and
s.sid=o.sid and
o.address=a.address and
o.hash_value=a.hash_value and
s.schemaname='OWNER';





select * from v$open_cursor;



select value, name from v$sysstat where statistic# in (2,3);



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;


/






-----Original Message-----

From:	Linda Hagedorn [SMTP:Linda_at_pets.com]
Sent:	Wednesday, August 30, 2000 11:47 PM
To:	Multiple recipients of list ORACLE-L
Subject:	RE: Number of open cursors





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.






-----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:28:07 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US