V$OPEN_CURSOR and ORA-01000: maximum open cursors exceeded

Let’s today talk about the ORA-01000, during past week application developers are facing the above issue with cursors, during their development, as their clients got stuck. They just pinged me ad updated the same and I had checked it was one of common issue where now-a-days application developers are facing across due to the improper coding while dealing with cursors. I had checked statistics from v$ views and the sessions are opened enormously the cursors and it was not closed properly – perhaps same application is opened /executing from different sessions. So, I thought let us post across the things how to work out and trace out the issue.

Try to check the Check the values of processes,sessions and open_cursors.

   1: SQL> col name format a20

   2: SQL> col value format a20

   3: SQL> select name,value from v$parameter where name in ('processes','sessions','open_cursors');

   4:  

   5: NAME                 VALUE

   6: -------------------- --------------------

   7: processes            150

   8: sessions             248

   9: open_cursors         300

  10:  

  11: SQL> select name,value from v$spparameter where name in ('processes','sessions','open_cursors');

  12:  

  13: NAME                 VALUE

  14: -------------------- --------------------

  15: processes            150

  16: sessions

  17: open_cursors         300

Every other DBA knows that importance of view V$OPEN_CURSOR, perhaps if you check the definition of the view properly “lists cursors that each user session currently has opened and parsed”.

Let’s check one demo on this.

Session :- 1

Logged in as sysdba and check across the view v$open_cursor for user “scott”

select * from v$open_cursor
where user_name='SCOTT'

Didn’t find any rows, as the user does not connected at all.

Session :- 2

C:\Users\Pavan>sqlplus scott/tiger@orcl

SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 8 10:47:55 2010

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select sysdate from dual;

SYSDATE
---------
08-NOV-10

Session 1 :-

Re-executed the script and find the  9 rows returned from the v$open_cursor view. Let’s see and study across what type of internal cursors are opened by ORACLE, as the user executed on an single select statement.

BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;
SELECT DECODE('A','A','1','2') FROM DUAL
select sysdate from dual
BEGIN DBMS_OUTPUT.DISABLE; END;
insert into sys.aud$( sessionid,entryid,statement,ntimestamp
select text from view$ where rowid=:1
SELECT USER FROM DUAL
SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE   (UPPER('
SELECT ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VALUE,DATE_VALUE F

The above is the process of different internal cursors executed over the dictionary in order to return the result set of the executed query. Then I tried to check what is actually happening further..

Let’s see the how is the base table on top of which the v$open_cursor view is working on,

select inst_id,
kgllkuse,
kgllksnm,
user_name,
kglhdpar,
kglnahsh,                   
kgllksqlid,
kglnaobj,
kgllkest,                                              
decode(kgllkexc, 0, to_number(NULL), kgllkexc),
kgllkctp              
from x$kgllk
where kglhdnsp = 0
and kglhdpar != kgllkhdl

x$kgllk – Details about Object locks

Different type of internal cursors which would be acquired by ORACLE during process of execution.

SQL> select distinct kgllkctp  from x$kgllk;

KGLLKCTP
-----------------------------------
PL/SQL CURSOR CACHED
SESSION CURSOR CACHED
OPEN
OPEN-RECURSIVE
DICTIONARY LOOKUP CURSOR CACHED

Now, let’s go back to issue and check the cursors opened at session and from sysstat

SQL> select a.value, s.username, s.sid, s.serial#
  2  from v$sesstat a, v$statname b, v$session s
  3  where a.statistic# = b.statistic# and s.sid=a.sid
  4  and b.name = 'opened cursors current';

then,

SQL> select max(a.value) as highest_open_cur, p.value as max_open_cur
  2  from v$sesstat a, v$statname b, v$parameter p
  3  where a.statistic# = b.statistic#
  4  and b.name = 'opened cursors current'
  5  and p.name= 'open_cursors'
  6  group by p.value;

Note:-  Oracle keeps an info about the cursors in v$open_cursor for some performance reasons even though the cursor is closed, so even I the session is disconnected the cursors persists.

When ever the sql statement executes the “Oracle Precompiler options HOLD_CURSOR and RELEASE_CURSOR” algorithm works – which route maps (or) based on the CURSOR results the goes for Soft (or) Hard parse, which we see across the during the trace execution of queries.