How helpful was this Support Pattern? Rating: Comments:

BEA logo
WebLogic Server
Support Pattern
More Support Patterns
Investigating "ORA-01000: maximum open cursors exceeded"

Problem Description

Oracle uses 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.


Quick Links


Diagnostic Queries

The following SQL queries are useful in diagnosing the ORA-01000 problem. To execute these queries, you need to login 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.
  2. 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.


  3. Get number of open cursors.
  4. 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.


  5. Get the SQL being executed for the cursors.
  6. 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.


Top of Page


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. BEA 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 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) {}

Top of Page


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.


Please be aware that the default statement cache size in WebLogic Server differs from one version to another. Examples:



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 BEA so that Support engineers can further investigate it to determine whether it's a WebLogic Server bug.


Top of Page


Database Driver

Another cause of the "maximum open cursors exceeded" problem could be 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.
  2. In your test case, get JDBC connections directly from the driver and bypass 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.


  3. Try different JDBC drivers.
  4. 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());

  5. XA driver bug.
  6. 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 hit a cursor leak issue in Oracle XA driver. This issue is described in case 3151681 on MetaLink 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://e-docs.bea.com/wls/docs81/jta/thirdpartytx.html#1075181.


If the problem is a JDBC driver problem and you have to use that driver, a workaround to the cursor leak problem is to reset WebLogic connection pool occasionally or shrink connection pool. Please see WebLogic documentation on how to reset of shrink a connection pool (In 8.1, it's documented at http://e-docs.bea.com/wls/docs81/ConsoleHelp/domain_jdbcconnectionpool_control.html).


Top of Page


Known Issues

You can periodically review the Release Notes for your version of WLS for more information on Known Issues or Resolved Issues in Service Packs and browse for ORA-01000 / cursor leak-related issues. For your convenience, see the following:
Of special note, see following CRs that are noted as resolved in Service Pack release notes from respective versions:
Searching will also return Release Notes, as well as other Support Solutions and CR-related information as noted at Need Further Help?. Contract customers who are logged in at http://support.bea.com/ will also see a Browse portlet for both Solutions and Bug Central where latest available CRs can be browsed by Product version.

Top of Page


Need Further Help?
If you have followed the pattern, but still require additional help, you can:
  1. Query AskBEA at http://support.bea.com/ using "ORA-01000: maximum open cursors exceeded", as an example, to discover other published solutions.  Contract Support Customers: Ensure you are logged to access available CR-related information.
  2. Ask a more detailed question on one of BEA's newsgroups at http://forums.bea.com
If this does not resolve your issue and you have a valid Support Contract, you can open a Support Case by logging in at: http://support.bea.com/ .

Feedback

Click Support Pattern Feedback to rate this Support Pattern and comment on such things as:

  • Were you successful in solving your issue with this pattern?
  • Was there any additional information that could be included in this pattern that would help solve your issue.
  • What other Support patterns would you like to see developed?

DISCLAIMER NOTICE:

BEA Systems, Inc. provides the technical tips and patches on this Website for your use under the terms of BEA's maintenance and support agreement with you. While you may use this information and code in connection with software you have licensed from BEA, BEA makes no warranty of any kind, express or implied, regarding the technical tips and patches.


Any trademarks referenced in this document are the property of their respective owners. Consult your product manuals for complete trademark information.