Maximum open cursors exceeded error

Issue #4 closed
w-diesel
created an issue

Hi Anthony!

We have a lot of PLSQL in our system, with ref cursors in some of them. After we switched from "New connection for every request" to Session Pool, I notice the problem - ref cursors still remain unclosed in DB-server, even after we close cursor or release the connection. The stored procedures called with "Cursor.callproc" method. Some of this procedures were called from Java code and everything went flawless.

I also find thread on mailing-list similar to this issue: http://sourceforge.net/p/cx-oracle/mailman/message/27028242/

This is very similar to what I've see now: An increasing number of open cursors in the DB server, which remain open until we close the connection. The problem is reproduced on both cx_Oracle 5.1.3 and 5.1.2 versions ( Debian Linux 32&64bit ).

Our Oracle DB-server version: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

Oracle libs:

  • ii oracle-instantclient11.2-basic 11.2.0.4.0-2 i386 Instant Client for Oracle Database 11g
  • ii oracle-instantclient11.2-devel 11.2.0.4.0-2 i386 Development headers for Instant Client.
  • ii oracle-instantclient11.2-jdbc 11.2.0.4.0-2 i386 Supplemental JDBC features for the Oracle Instant Client
  • ii oracle-instantclient11.2-sqlplus 11.2.0.4.0-2 i386 SQL*Plus for Instant Client.

||/ Name Version Architecture Description +++-===================-==============-==============-===========================================

  • ii cx-oracle 5.1.2-2 amd64 Python interface to Oracle
  • ii oracle-instantclien 11.2.0.4.0-2 amd64 Instant Client for Oracle Database 11g
  • ii oracle-instantclien 11.2.0.4.0-2 amd64 SQL*Plus for Instant Client.

I would appreciate any assistance that will help track down the cause of the problem. Thank you!

Comments (10)

  1. w-diesel reporter
    • edited description

    The simple script for the problem test:

    import time
    import cx_Oracle
    
    ip = 'server'
    port = 1521
    SID = 'sid'
    dsn_tns = cx_Oracle.makedsn(ip, port, SID)
    
    cpool = cx_Oracle.SessionPool(
        user='developer',
        password='developer',
        dsn=dsn_tns,
        min=1,
        max=20,
        increment=1,
        homogeneous=False)
    
    
    conn = cx_Oracle.connect('developer', 'developer', dsn_tns)
    
    
    #conn = cpool.acquire()
    ora_cur = conn.cursor()
    
    ref_cur = ora_cur.var(cx_Oracle.CURSOR)
    
    q = ora_cur.callproc('developer.TEST.head_list', (1, ref_cur))
    print '___procs time exec: ', time.ctime() 
    
    x = ref_cur.getvalue()
    print x.fetchall()
    print '___fetchall complete at: ', time.ctime()
    
    
    time.sleep(30)
    x.close()
    print '___ref cursor closed at: ', time.ctime()
    
    
    time.sleep(30)
    del x
    print '___ref cursor manualy deleted at: ', time.ctime()
    
    
    time.sleep(30)
    ora_cur.close()
    print '___main cursor closed at: ', time.ctime()
    
    
    time.sleep(30)
    del ora_cur
    print '___main cursor manualy deleted at: ', time.ctime()
    
    
    #cpool.release(self.conn)
    #print '___conn released at: ', time.ctime()
    
    time.sleep(30)
    conn.close()                                              # -- only at this point cursor is closed.
    print '___conn closed at: ', time.ctime()
    

    And SQL-expression for the monitoring of open cursors:

    select count (1) cnt, s.username,max(prev_exec_start) time,q.sql_text
    from v$sesstat a, v$statname b, v$session s,v$open_cursor c,v$sqlarea q
    where a.statistic# = b.statistic# and
    s.sid=a.sid and b.name = 'opened cursors current'   and c.saddr=s.saddr  and q.sql_id=c.sql_id
    and lower(username) like 'developer%'
    and c.cursor_type='OPEN'
    group by q.sql_text,username
    order by 1 desc
    
  2. Krishna Mohan Itikarlapalli

    Hi, two things can impact this.

    If statement cache is on, the top level statements being executed remain open as long as the connection is open (while in the pool in case of session pools). So the statement cache needs to be set accordingly. It looks like by default the session pool in cx_Oracle is turning statement cache on, setting it to the default value of 20.

    The second thing is related to the REF cursors. These need to be closed explicitly. The app above seems to be doing it correctly. Internally, the closure is deferred to a round-trip call on the corresponding connection. So you may not see the number of open cursors going down immediately after cur.close(), but you should see it after application does some round-trip. So if your app is normally doing round-trips, you should not see any cursor leaks.

    It will be good if you can verify the above.

  3. w-diesel reporter

    Hi I checked 5.2(3d92061b8257) and behavior of 5.2 is the same as 5.1.x

    I also want to note about ref cursors, ref cursors in DB remain opened as long as connection persist, but number of opened cursors is not increased with the next invocation of the same procedure with it if cursor was closed properly ( cursor.close() ) The number of opened cursors may vary with a number of opened connections(in SessionPool and in general of course). So, if with SessionPool were established 4 connections to DB(with SessionPool.min and SessionPool.max), in DB, I have seen the 4 identical opened ref cursors respectively.

    I also compared the current behavior with the same PLSQL's procedure with ref cursor in Java - "JDBC Thin Client" driver (ojdbc6_g.jar), and it close ref cursor in DB with cursor.close() invocation almost instantly.

  4. w-diesel reporter

    The fact that ref cursors remain opened in db is persist. I actually don't really understand what you mean by the word "round-trip"? but I didn't notice that number of opened cursors was decreased in db after noticeable amount of time. Like i said before the same procedures with ref cursors closes in JAVA almost instantly.

  5. avnandak
    """
    CREATE OR REPLACE PROCEDURE getemp( emp_c out sys_refcursor)
    IS
    BEGIN
       open emp_c for select * from emp;   
    END;
    /
    """
    import time
    import cx_Oracle
    
    cpool = cx_Oracle.SessionPool(
        user='scott',
        password='tiger',
        dsn='inst1',
        min=1,
        max=20,
        increment=1,
        homogeneous=False)
    
    conn = cpool.acquire()
    #conn = cx_Oracle.connect('scott', 'tiger', 'inst1')
    print conn.stmtcachesize
    ora_cur = conn.cursor()
    ref_cur = ora_cur.var(cx_Oracle.CURSOR)
    q = ora_cur.callproc('getemp', (ref_cur,))
    print '___procs time exec: ', time.ctime() 
    
    x = ref_cur.getvalue()
    print x.fetchall()
    print '___fetchall complete at: ', time.ctime()
    
    time.sleep(10)
    x.close()
    print '___ref cursor closed at: ', time.ctime()
    #conn.ping()
    
    time.sleep(10)
    del x
    print '___ref cursor manualy deleted at: ', time.ctime()
    
    time.sleep(10)
    ora_cur.close()
    print '___main cursor closed at: ', time.ctime()
    #conn.ping()
    
    time.sleep(10)
    del ora_cur
    print '___main cursor manualy deleted at: ', time.ctime()
    
    time.sleep(10)
    cpool.release(conn)
    print '___conn released at: ', time.ctime()
    
    #conn.close()                                              # -- only at this point cursor is closed.
    print '___conn closed at: ', time.ctime()
    
  6. Log in to comment