Oracle error using JDBC2 pooled connections

[Jez]

Hi,

We have created an ebase application at Hampshire CC, using JDBC2 to connect to an Oracle database. In Dev & Test we had no issues, with only a small number (1-2) of users hitting the application.

We have just put the application into a live Pilot though and have hit a problem. After a short(ish) period of use, we get back an error message back; 'Maximum Open Cursors exceeded error (ORA-01000)'.

Having monitored the number of open cursors for the ebase JDBC2 users, I can see the number of open cursors increasing as users move around the ebase application. If the app use stops for a prolonged period, the open cursors are apparently dropped. However, we don't anticipate such a period of inactivity in a normal working day if the application is put live (we may have tens of users accessing the application concurrently).

After looking into this, there is an Oracle DB setting for the 'maximum number of open cursors per user' which is set at 500 in our DB. We are going to increase this figure to 3000, but I'm concerned we'll still hit the issue in live before the end of a working day (when there will be no users).

Has anyone hit this issue using JDBC2 (which is preferable to JDBC for performance reasons)? Is there an ebase fpl command to close open cursors after a FETCH? Or are there any JDBC2 settings (in UFS.XML?) that might help?

My DB code for reading number of open cursors;
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current'

[AJDulk]

We are using Oracle here and have not seen the error message. Then again, we are using the Oracle Database with the Oracle Application Server.

Some questions:

• Are you using the official Oracle jdbc driver?
  What sort of loads are you talking about before the error occurs?

I doubt setting the maximum cursors per user is going to help. You should look at increasing the cursors of the system and / or expire the cursors quicker.

A quick google search turns up the following:

The problem occurs when your max-pooled statements setting on the datasource is higher than the CURSORS setting in the oracle database.

Let me know if this helps.

[Sarah]

One of our customers has experienced this same error message once before. You need to check that in the ufs.xml file (located in UfsServer/tomcat/conf/Catalina/localhost folder) the preparedStatementCache in the Oracle datasource is set to zero as follows (zero is the default setting):

Code: Select all

<parameter>
   <name>preparedStatementCache</name>
   <value>0</value>
</parameter>

If it isn't set to zero, change it to zero and then you will need to restart Tomcat for the change to take effect.


For information, the Ebase Customer Portal has a Problem Logs section where you can search through queries/problems that customers have experienced in the past. This can be accessed via our website at www.ebasetech.com

[Jez]

Thanks for your replies.

Sarah, I've checked the live ebase server ufs.xml file and the PreparedStatementCache is already set to 0;

Code: Select all

      <parameter>
        <name>preparedStatementCache</name>
        <value>0</value>
      </parameter>

This server was last bounced on Wednesday and this statement has never (knowingly) been changed sinec we went live a year ago, so I'm confident we're already be running with this value.

AJDulk, I like the idea of somehow expiring the cursors sooner, but (looking on the web) I'm getting the impression this has to be done at the ebase/tomcat end rather than it being an Oracle setting on the database? (which fits with Sarah's reply).

I'll try a different version of the JDBC driver to see if that helps.

[Jez]

A quick update on this issue as we’ve made some progress… We've determined that the run-a-way cursors issue is a result of my use of dynamic SQL in a Oracle function, used in a Oracle view, called from ebase.

Instead of re-using existing cursors, using dynamic SQL opens new cursors each time the function is called (3 times per row!) – in some cases 150 cursors were opening per ebase page/table! This is obviously bad use of dynamic SQL on my part.

I have reworked this function to avoid using dynamic SQL and the number of open cursors now appears to level off at a reasonable point.