1

Am working on spring-boot application. While performing load testing we get error

"ORA-01000:maximum open cursors exceeded"

We have following entries for spring boot and jdbc in the pom.xml file

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.1.6.RELEASE</version>
</parent>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
    <groupId>com.oracle</groupId>
    <artifactId>ojdbc6</artifactId>
    <version>11.2.0.3</version>
</dependency>

To fix the issue I tried changing the "ojdbc" from 6 to 8.

<dependency>
    <groupId>com.oracle.jdbc</groupId>
    <artifactId>ojdbc8</artifactId>
    <version>12.2.0.1</version>
</dependency>

but I still got the same issue.

All our backend calls using either "jdbcTemplate" or "namedParameterJdbcTemplate".

The error is happening during load testing. Our Oracle DB cursor limit is 1000. Load test run game the error after 1hr 55mins and failure rate our our service was 0.5% the number of users/threads is set to 25.

Looking for some suggestions to fix this issue.

pooja
  • 21
  • 1
  • 5
  • 2
    https://stackoverflow.com/q/12192592/7505731 will provide you insight on this exception – Hemant Apr 07 '20 at 01:59
  • Thanks for sharing, though most of the content refers to closing of connects result sets etc. However in my code we using jdbctemplate or namedParameterJdbcTemplate and doirectly get the data so not having reference to results or connection etc. – pooja Apr 07 '20 at 04:25

4 Answers4

1

My first suggestion would be to upgrade the update Spring Library including Spring JDBC and JPA version to latest. The earlier spring JPA version was not closing the cursors properly.

This problem occurs commonly because of not cleaning up resultsets, statements or connections. Each ResultSet that you create, uses a cursor on the backend. If you never close the ResultSet, the Statement that created it or the Connection that was used for the statement, those cursors never get closed. As you are using a connection pool, the connections are never physically closed, thus the cursors are never closed either.

What causes the Solve ora-01000 maximum open cursors exceeded java.sql.SQLException problem in JDBC java-

  1. Not closing the JDBC Statement object can cause maximum open cursors exceeded java.sql.SQLException,
  2. Not closing the JDBC PreparedStatement object can cause maximum open cursors exceeded java.sql.SQLException,
  3. Not closing the JDBC CallableStatement object can cause maximum open cursors exceeded java.sql.SQLException,
  4. Not closing the JDBC ResultSet object and Not closing the JDBC Connections object can cause maximum open cursors exceeded java.sql.SQLException

Does Spring JdbcTemplate Close Connections? … Not Always.

Decent developers usually know that they have to try/catch/finally to ensure they clean up connections, file handles, or any number of things. But then, for Java, you hear “just use JdbcTemplate! it does all this boilerplate for you!”. I had, for the longest time, assumed that JdbcTemplate would clean up connections in addition to results sets. In fact, you’ll see this online a lot. But be careful! This does not appear to be the case, or if it is, it is at least data source dependent… and that actually makes sense if you think about their purpose.

When you don't have a Spring managed transaction then yes the JdbcTemplate will call the close() method on the Connection. However if there was already a connection available due to Springs transaction management closing the connection will be handled by Springs transaction support, which in turn also will call close() on the Connection.

EDIT:

Workaround:

Increase the maximum open cursor in the database by executing the following SQL command on the database: 

ALTER SYSTEM SET open_cursors = 1000 SCOPE=BOTH;

This example sets the maximum open cursors to 1000. Change this value as required.

Resolution:

Update the Oracle JDBC Driver to the latest version (12.2.0.1)

There is a new version of the Oracle JDBC driver which fixes the cursor leak.

Mebin Joe
  • 2,172
  • 4
  • 16
  • 22
  • thanks for your answer, however am not holding any reference to resultsets, statements or connections. In my DAO implementation am jus making call to jdbctemplate or namedparameterjdbctemplate and making method calls like queryForObject etc. So am not sure even if I add try catch since I don't hold reference to results etc what would I close then. – pooja Apr 07 '20 at 13:40
  • Also, I have not implemented any connection pooling. – pooja Apr 07 '20 at 13:48
  • @pooja How is your transaction management ? Like creating, opening and closing connection. Is that managed by Spring JDBC Transaction, for eg: Database transaction manager. Are you using any annotations like EnableTransactionManagement or Transactional. – Mebin Joe Apr 07 '20 at 14:58
  • So on some methods which make Dao calls I see we have @Transactional but not on all the methods. And also I have known that we have connection pooling maintained on the tomcat server level where this application gets deployed. – pooja Apr 07 '20 at 17:33
  • We also tried implementing connection pooling by adding following - in pom.xml org.springframework.boot spring-boot-starter-data-jpa 2.2.4.RELEASE and required entries in application.properties but it still gave the error – pooja Apr 07 '20 at 23:47
  • @pooja Could you please share the complete stacktrace. Which DB Transaction(query) is actually leading to the exception ? I have updated the answer with details. Please check and let me know. – Mebin Joe Apr 08 '20 at 03:54
1

Okay, I finally got an answer. My project was EXACTLY same as yours(Spring boot 2.1.6.RELEASE and ojdbc8 12.2.0.1), (additionally Spring data jpa,) and upgrading version to 2.2.5.RELEASE didn't work for me.

I had the same exception occurs when every time I call the same function using certain amount of data(or above) - I tried to get rid of this error by upgrading ojdbc version to 19.6.0.0, adding spring config spring.jdbc.getParameterType.ignore, and so on. None of them works for me.

Finally what I found was the return type of jpa repository method. Originally my method was like this : 

Stream<MyEntity> findByMyColumn(MyColumnType myColumnValue);

note that the return type of this repository method is java.util.Stream. The reason why I get this return type as stream is that this method is used only when the streaming is required. And everytime I execute same procedure, the stack trace contains this method and I feel weird. So I changed the type to List, and the error is GONE!!!

List<MyEntity> findByMyColumn(MyColumnType myColumnValue);

I think that the Stream is holding cursors even if the stream ended. Obviously it's a bug on Spring boot or ojdbc, but none of them seems to recognize or have willing to fix this problem.

Hope that helps to your situation, too.

Taeho Jeong
  • 51
  • 1
  • 5
  • A stream must be closed after usage: https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.query-streaming – stephan f Oct 06 '21 at 10:46
0

ok, this took long for me to come back and post. So for me this issue got resolbved just by having following entry in pom.xml

<dependency>
        <groupId>com.oracle.jdbc</groupId>
        <artifactId>ojdbc8</artifactId>
        <version>12.2.0.1</version>
    </dependency>

and for sometime when it didnt work we realized there was another ojdbc7 which was getting picked up by the environment and it was getting introduced due to some other dependency. So once we suppressed that the ojdbc8 got picked up and our issue got resolved.

pooja
  • 21
  • 1
  • 5
0

Upgrading to the latest oracle jdbc driver solved the problem. We upgraded to : version (12.2.0.1)

Vinny
  • 149
  • 1
  • 4