WebLogic
Server
Support Pattern
Support Pattern
|
|
WebLogic
Server
Support Pattern |
| More Support Patterns |
| Investigating "ORA-01000: maximum open cursors exceeded" |
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.
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.
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.
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.
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.
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.
The following are steps to check what is the cause of the problem and possible solutions.
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) {} |
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.
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.
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.
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()); |
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).
| Need Further Help? If you have followed the pattern, but still require additional help, you can:
|
FeedbackClick Support Pattern Feedback to rate this Support Pattern and comment on such things as:
|
|
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. |
