Troubleshooting
Problem
You can use IBM Business Process Manager SQL integration service to interact with the database. In a high workload, you might find that the prepared statement cache hit ratio is very low for the data source that is used by the SQL integration service. After increasing the statement cache size in the data source, an Oracle ORA-01000 error happens.
Symptom
You might see the following exception in SystemOut.log:
java.sql.SQLException: ORA-01000: maximum open cursors exceeded
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:439)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:395)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:802)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:436)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:186)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:521)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:205)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1008)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1307)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3449)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3550)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1374)
at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.pmiExecute(WSJdbcPreparedStatement.java:958)
at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.execute(WSJdbcPreparedStatement.java:625)
at teamworks.sql.SQLClient.executePreparedStatement(SQLClient.java:443)
at teamworks.sql.SQLClient.execute(SQLClient.java:216)
at teamworks.SQLConnector$5.execute(SQLConnector.java:232)
at teamworks.SQLConnector$5.execute(SQLConnector.java:228)
at teamworks.sql.SQLExecutor.doExecute(SQLExecutor.java:134)
at teamworks.sql.SQLExecutor.execute(SQLExecutor.java:79)
at teamworks.SQLConnector.execute(SQLConnector.java:227)
Cause
The Oracle ORA-01000 error occurs because the statement cache size property for the data source is set to a value higher than the maximum number of open cursors in the Oracle database. But, in this case, it is not good to simply increase the maximum number of open cursors in the Oracle database.
The root cause is that bind variable is not used in your SQL integration service. The SQL string that is passed to the SQL integration service contains literal values. Because most of the SQL statements for one SQL integration service have different literal values, a matching statement cannot be found in statement cache (shared pool). The cache hit ratio is very low and the execution plan is generated for each SQL statement every time.
Resolving The Problem
To resolve this issue, use a bind variable in your SQL integration service.
For information on how to use the bind variable in the IBM Business Process Manager SQL integration service, see the following developerWorks article: Using the SQL integration service with WebSphere Lombardi Edition V7.2 and WebSphere Application Server V7.
To learn more about the Oracle ORA-01000 error and its corresponding solution, see the "ORA-01000: maximum open cursors exceeded" connecting to an Oracle database from WebSphere Application Server document.
Product Synonym
BPM
Was this topic helpful?
Document Information
Modified date:
22 June 2018
UID
swg21670886