ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Monitoring Open Cursors & Troubleshooting ORA-1000 Errors

Monitoring Open Cursors & Troubleshooting ORA-1000 Errors

原创 Linux操作系统 作者:thompsun 时间:2011-01-21 12:24:57 0 删除 编辑

In this Document
  Purpose
  Scope and Application
  Monitoring Open Cursors & Troubleshooting ORA-1000 Errors
  References


Applies to:

PL/SQL - Version: 10.2.0.4 and later   [Release: 10.2 and later ]

Purpose

This article contains information on how different cursors are managed and monitored in PL/SQL.

Scope and Application

It addresses issues with the open_cursors parameter, as well as the V$OPEN_CURSOR view in the context of implicit, declared, and dynamic cursors.

Monitoring Open Cursors & Troubleshooting ORA-1000 Errors

This section covers the monitoring and managing of the open cursors.

The number of open cursors can limit operation in PL/SQL procedures and SQL*Plus sessions. While the parameter open_cursors sets the limit, programming issues can cause the following error:

ORA-1000 maximum open cursors exceeded

Three important values are the following:  

  1. init.ora parameter open_cursors
  2. V$OPEN_CURSOR view
  3. V$SYSSTAT view

These values are similar, but differ in their accounting of Dynamic Cursors.

Note: Dynamic cursors are those opened using DBMS_SQL.OPEN_CURSOR().

The means to compute those values are as follows: 

View v$open_cursor

The following SQL calculates the:

implicit cursors used + distinct explicit cursors opened + dynamic cursors PARSED and NOT CLOSED.

It also:
- Accumulates dynamic cursors PARSED and NOT CLOSED over a session
- Available to system/manager
- 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.

SQL> select count(*) from v$open_cursor;

COUNT(*)
----------
139


View v$sysstat

The following SQL calculates the:

implicit cursors used + distinct explicit cursors opened + dynamic cursors OPENED.

It also:
- Accumulates dynamic cursors OPENED and NOT CLOSED over a session
- 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.

SQL> select value from v$sysstat where statistic# = 3;

VALUE
----------
37


init.ora parameter open_cursors

This parameter equal:
implicit cursors used + distinct explicit cursors opened + dynamic cursors OPENED.

It also:
- Accumulates dynamic cursors OPENED and NOT CLOSED over a session

SQL> show parameters open_cursor

NAME TYPE VALUE
------------------ ----------- ---------
open_cursors       integer     300


ORA-1000

The following are several items to check when encountering ORA-1000 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 determined as follows in SQL*Plus:

SQL> 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 determined as follows in SQL*Plus:

SQL> select sql_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.

The following are two code snippets that can help diagnose ORA-1000. Text lines are shown for each cursor.

-- snippet 1

set serveroutput on
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;
/

Num cursors open is 144
Cursor #1
text: select priority from resource_mapping_priority$ where attrib
Cursor #2
text: select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.fla
Cursor #3
text: select value, consumer_group from resource_group_mapping$ wh
   :
   :
   :
Cursor #142
text: select col#,intcol#,reftyp,stabid,expctoid from refcon$ wher
Cursor #143
text: table_1_ff_14f_0_0_0
Cursor #144
text: SELECT COUNT(*) FROM V$OPEN_CURSOR
Cursor #145
text: select /*+ index(idl_sb4$ i_idl_sb41) +*/ piece#,length,piec

PL/SQL procedure successfully completed.


-- snippet 2

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

VALUE NAME
---------- ----------------------------------------------------------------
47767 opened cursors cumulative
41 opened cursors current

References

NOTE:1012266.6 - Overview of ORA-1000 Maximum Number of Cursors Exceeded

Troubleshooting Details

Diagnostic Queries

The following SQL queries are useful in diagnosing the ORA-01000 problem. To execute these queries, you need to logged into the database as an administrator or your DB Admin grants you permissions to select from those v$ views.

  1. Check OPEN_CURSORS parameter value in your database.
    Oracle uses the initialization parameter OPEN_CURSORS in init.ora to specify the maximum number of cursors a session can have at once. The default value is 50. Unfortunately, this default value is usually too small for systems such as WebLogic Server. To find out the value of OPEN_CURSORS parameter in your database, you may use the following query:
    SQL> show parameter open_cursors;

    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    open_cursors                         integer     1000
    It is important to set the value of OPEN_CURSORS high enough to prevent your application from running out of open cursors. The number will vary from one application to another. Assuming that a session does not open the number of cursors specified by OPEN_CURSORS, there is no added overhead to setting this value higher than actually needed.
  2. Get number of open cursors.
    The query below shows number of open cursors for each session opened by user 'SCOTT' in descending order.
    SQL> select o.sid, osuser, machine, count(*) num_curs

      2  from v$open_cursor o, v$session s
      3  where user_name = 'SCOTT' and o.sid=s.sid
      4  group by o.sid, osuser, machine
      5 order by  num_curs desc;
           SID OSUSER               MACHINE                                              NUM_CURS
    ---------- ---------------- ------------------------------------------------- ----------
           217                                m1                                                           1000
            96                                 m2                                                            10
           411                                m3                                                             10
            50                                test                                                              9
    When you use connection pool in WebLogic Server, the user_name in this query should be the user_name you use to create the connection pool, assuming that the connection is retrieved from a connection pool. The query result also gives machine names. From the query result, identify SIDs with high number of open cursors and machine names on which you run your WebLogic Servers.

    Please note that v$open_cursor can track dynamic cursors (cursors opened using dbms_sql.open_cursor()) PARSED and NOT CLOSED over a session. It doesn't track unparsed (but opened) dynamic cursors. Using dynamic cursors is not common in application. This pattern assumes that dynamic cursors are not used.
  3. Get the SQL being executed for the cursors.
    Take the SID identified from the above query result and run the following query:
    SQL> select q.sql_text
      2  from v$open_cursor o, v$sql q
      3  where q.hash_value=o.hash_value and o.sid = 217;

    SQL_TEXT
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    select * from empdemo where empid='212'
    select * from empdemo where empid='321'
    select * from empdemo where empid='947'
    select * from empdemo where empid='527'
    ...
    The result shows what queries are being executed on the connection. It gives you a starting point to trace back to see where is the source of open cursors.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11134237/viewspace-684420/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-01-11

  • 博文量
    96
  • 访问量
    251586