One issue I have come across recently is the leak of cursors in a application in a high concurrency more than 600 users and more than 30 pages served per second. With this conditions, after 10 minutes of test, I started to see the ORA-01000 Maximum Number of Cursors Exceeded exception frequently.
If I executed the above second query, I saw that some users of my application had really reached the 300 real opened cursors that are the maximum number of cursors per session configured in Oracle. We can see this parameter with the following query:
cursor keeped opened which could mean two things: a bug in the server application or the Oracle driver or that the application server was using any kind of setences cache ... it was a good thing that the problem was the second supposition.
The application was being executed in WebSphere which has a statement cache to optimize the processing of prepared statements and callable statements implementing the PreparedStatement interface with a propietary IBM class. When the number of cached statements is less than the size of this cache, a close over the statement doesn't really close the
resources, it is like a "soft" close. The execution of the query to show the opened cursors shows the real opened cursors, these "soft" closed cursors too!!
The problem was that the size of this cache was configured with a value of 1000 so when the Oracle limit of 300 was reached, started to fail with ORA-01000 exceptions because no statement was close because the total count was always <1000(<300 because of the Oracle limit)
If I executed the above second query, I saw that some users of my application had really reached the 300 real opened cursors that are the maximum number of cursors per session configured in Oracle. We can see this parameter with the following query:
SELECT name, value FROM v$parameter WHERE name='open_cursors'After analyzing the application source code deeply, I confirmed that it was releasing the database resources properly, CallableStatements in this case, so the problem seemed to be another. After a little debugging I realized that when the PreparedStatement was closed the
cursor keeped opened which could mean two things: a bug in the server application or the Oracle driver or that the application server was using any kind of setences cache ... it was a good thing that the problem was the second supposition.
The application was being executed in WebSphere which has a statement cache to optimize the processing of prepared statements and callable statements implementing the PreparedStatement interface with a propietary IBM class. When the number of cached statements is less than the size of this cache, a close over the statement doesn't really close the
resources, it is like a "soft" close. The execution of the query to show the opened cursors shows the real opened cursors, these "soft" closed cursors too!!
The problem was that the size of this cache was configured with a value of 1000 so when the Oracle limit of 300 was reached, started to fail with ORA-01000 exceptions because no statement was close because the total count was always <1000(<300 because of the Oracle limit)
No comments:
Post a Comment