RE: Number of open cursors

From: Christine Turner <christine.turner_at_ips-sendero.com>
Date: Wed, 30 Aug 2000 13:42:55 -0000
Message-Id: <10604.115919@fatcity.com>

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

Received on Wed Aug 30 2000 - 08:42:55 CDT