Investigating ORA-01000: maximum open cursors exceeded WebLogic Server

by AjitSunday, August 31, 20140 Comments

Problem Description

Oracle uses the OPEN_CURSORS parameter to specify the maximum number of open cursors a session can have at once. When this number is exceeded, Oracle reports an ORA-01000 error. When this error is propagated to WebLogic Server, a SQLException is thrown.
java.sql.SQLException: ORA-01000: maximum open cursors exceeded
This pattern discusses possible causes and solutions of this error when using WebLogic Server.

Problem Troubleshooting

Please note that not all of the following items would need to be done. Some issues can be solved by only following a few of the items.

Troubleshooting Steps

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 a 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.

Common Causes and Solutions

The following are steps to check what is the cause of the problem and possible solutions.

Code Practice

The most common cause of this problem is that JDBC objects are not properly closed. Use the result from the third query in the Diagnostic Queries to trace back in application code to make sure that all JDBC objects are properly closed. Oracle recommends that you explicitly close JDBC objects, such as Connections, Statements, and ResultSets, in a finally block to make sure that all JDBC objects are closed under normal or exception condition. Here is a general example:
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;

try {
    conn = getConnection(); //Method getConnection will return a JDBC Connection
    stmt = conn.createStatement();
    rs = stmt.executeQuery("select * from empdemo");
    // do work
} catch (Exception e) {
    // handle any exceptions
} finally {
    try {
        if(rs != null)
            rs.close();
    } catch (SQLException rse) {}
    try {
        if(stmt != null)
            stmt.close();
    } catch (SQLException sse) {}
    try {
        if(conn != null)
            conn.close();
    } catch (SQLException cse) {}
}
Please avoid any code practice that abandons JDBC objects. The following practice obtains a new Connection, Statement, and ResultSet in each loop iteration but it doesn't close JDBC objects for each iteration. Hence, it causes a JDBC objects leak.
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
String[] queries = new String[10];
//Define queries

try {
    for(int i = 0; i < 10; i++) {
        conn = getConnection();
        stmt = conn.createStatement();
        rs = stmt.executeQuery(queries[i]);
        // do work
    }
} catch (Exception e) {
    // handle any exceptions
} finally {
    try {
        if(rs != null) 
            rs.close();
    } catch (SQLException rse) {}
    try {
        if(stmt != null) 
            stmt.close();
    } catch (SQLException sse) {}
    try {
        if(conn != null)
            conn.close();
    } catch (SQLException cse) {}
Although Statements and ResultSets should be closed when a Connection is closed, per JDBC specification, it's a good practice to explicitly close Statements and ResultSets right after you finish using them if you create multiple Statements on one Connection object. If you don't explicitly close Statements and ResultSets right away, cursors may accumulate and exceed the maximum number allowed in your DB before the Connection is closed. For example, in the following code snippet, ResultSets and Statements should be closed when Connection is closed in the finally block. However, this code snippet creates multiple Statements and ResultSets on one connection. Before the loop is finished, the "maximum open cursors exceeded" problem may have occurred.
Connection conn = null;

try{
    conn = getConnection();

    for(int i = 0; i < NUM_STMT; i++) {
        Statement stmt = null;
        ResultSet rs = null;

         stmt = conn.createStatement();
         rs = stmt.executeQuery(/*some query*/);
        //do work
    }
} catch(SQLException e) {
     // handle any exceptions
} finally {
    try{
        if(conn != null)
            conn.close();
    } catch(SQLException ignor) {}

Statement Caching

To increase performance, WebLogic Server provides a feature to cache prepared statements and callable statements when you use connection pool. When WebLogic Server caches a prepared or callable statement, in many cases, the DBMS will maintain a cursor for each open statement. Hence, statement caching could be a source of the "maximum open cursors exceeded" problem. The Statement Cache Size attribute determines the total number of prepared and callable statements to cache for each connection in each instance of the connection pool. If you cache too many statements, you may exceed the limit of open cursors on your database server. In WebLogic Server 8.1 and higher, the default prepared statement cache size is 10. See http://download.oracle.com/docs/cd/E14571_01/web.1111/e13737/jdbc_datasources.htm#JDBCA171 for more details.
To determine whether the "maximum open cursors exceeded" problem is related to statement caching, you may turn this feature off by setting statement cache size to 0 or reduce the cache size to see whether you still get the error. If the problem doesn't occur when you reduce the cache size, your original statement cache size on your connection pool was too big or the limit of open cursors in your DBMS is too low. You may need to consider to adjust either value. If you see number of open cursors keeps growing on a connection but don't see this behavior when you set statement cache size to 0, it could indicate a cursor leak problem. This could be caused by the JDBC driver you are using or it could be a WebLogic Server bug. Please try a different JDBC driver. If the same problem occurs with different JDBC drivers, please report this problem to Oracle so that Support engineers can further investigate it to determine whether it's a WebLogic Server bug.

Database Driver

Another cause of the "maximum open cursors exceeded" problem could be a JDBC driver problem. In order to isolate whether the problem is a driver problem or WebLogic connection pool problem, you may try the following if you have a reproducible test case.

  1. Get connection from the driver directly.
    In your test case, get JDBC connections directly from the driver and bypass the WebLogic connection pool. Don't close the connections, however, just keep them open in an array or some other structure, and see if there is still a cursor leak. The reason of not closing connections is to simulate the behavior when connection pool is used. When using connection pool, connection.close() doesn't really close the physical connection but returns the connection to the pool instead.
  2. Try different JDBC drivers.
    You may try a JDBC driver from a different vendor or an updated version of driver to see whether the problem still occurs. You may use metadata to verify that a correct driver is used. Sample code is like this:
    Connection conn = getConnection();
    DatabaseMetaData dmd = conn.getMetaData();
    System.out.println("JDBC Driver Name is " + dmd.getDriverName()); 
    System.out.println("JDBC Driver Version is " + dmd.getDriverVersion());
  3. XA driver bug.
    If you are using Oracle XA driver and you see a lot of queries like SELECT count (*) FROM SYS.DBA_PENDING_TRANSACTIONS in the database, you may have hit a cursor leak issue in Oracle XA driver. This issue is described in defect 3151681 in My Oracle Support and it's fixed in version 10.1.0.2.

    Also, when you use XA driver, please ensure to enable XA on the Database Server (e.g., grant select on dba_pending_transactions to public), as documented at http://download.oracle.com/docs/cd/E14571_01/web.1111/e13731/thirdpartytx.htm#i1089081.

0 Comments