RE: Number of open cursors

From: Linda Hagedorn <Linda_at_pets.com>
Date: Wed, 30 Aug 2000 15:43:45 -0700
Message-Id: <10604.115927@fatcity.com>

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-----
From: Christine Turner [mailto:christine.turner_at_ips-sendero.com] Sent: Wednesday, August 30, 2000 2:44 PM To: Multiple recipients of list ORACLE-L Subject: RE: Number of open cursors

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:

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'
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'
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

Received on Wed Aug 30 2000 - 17:43:45 CDT