ORA-01000 and Sentences Cache

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:

  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)

PreparedStatements and Oracle Cursors

One thing we must always bear in mind when an application goes to a production environment is that it uses the shared resources properly. In this case, I'll talk about Oracle cursors related to PreparedStatements. Every time we execute a PreparedStatement a cursor is opened and it is not released until an explicit close is not invoked. The right way is to release the PreparedStatement, the ResultSet if exists and the Connection, for example:

// Create a connection or get one from a pool
Connection con = getConnection();
...
PreparedStatement ps = null;
ResultSet rs = null;

try {
 ps = con.prepareStatement(sqlIsi);
 rs = ps.executeQuery();
}finally {
 if (rs!=null)
   try {rs.close();} catch(SQLException e) {}
 if (ps!=null)
   try {ps.close();} catch(SQLException e) {}
}

...

// Release the connection:
//   close the connection or return it to the pool
con.close();

We always have to release the resources in a finally block so the release is always executed even if exceptions are thrown in the try block. Doing that we'll avoid cursors leaks and the ugly ORA-01000 Maximum Number of Cursors Exceeded.

Concerning to the execution speed, every time a query is invoked, Oracle have to parse it. In the case of PreparedStatements, sentences with a high probability of being executed multiple times, the parsing time can be high. This time can be reduced by caching the sentences once parsed and that's what Oracle does. Oracle maintains a cache table where it stores the parsed sentences, speeding up the execution when the same ones are frequently invoked. As this cache is not unbounded, we have to invoke the PreparedStatements in a proper way: they have to be parametrized. For example:

  select name,number from person where id=2451
  select name,number from person where id=?

From a functional point of view, the above sentences are identical, they retieve the information of a person from his/her Id. But from a performance point of view they are very different. If that kind of sentences have to be executed a lot of times, the overall execution of sentences will perform better with the second sentence because the cache will be working well.

In the first case, if the Id is different, every time this sentence is invoked, it has to be parsed because it really is a new sentence:

  select name,number from person where id=2451
  select name,number from person where id=1121
  select name,number from person where id=5489
  select name,number from person where id=...

In the second case, the sentence is always the same:

  select name, number for person where id=?

We can monitorize the cached sentences using the following query:

  SELECT a.sid, user_name, status,
  osuser, machine, c.sql_text
  FROM v$session b, v$open_cursor a, v$sql c
  WHERE a.sid = b.sid
  AND a.address=c.address
  AND a.user_name IN (
    SELECT DISTINCT user_name FROM v$session)
  AND user_name={user_name}
  ORDER BY user_name DESC, a.sid, c.sql_text, 3;

where {user_name} has to be replaced by the desired user. Even though in the above sentence we are querying from the open_cursor table, this table doesn't contain real opened cursors but cached statements!!!

In order to know the real opened cursors of a session, we have to use a sentence like the following:

  SELECT a.value, s.username,
    s.sid, s.serial#, p.spid
  FROM v$sesstat a, v$statname b,
    v$session s, gv$session s2
  JOIN gv$process p ON p.addr = s2.paddr
  AND p.inst_id = s2.inst_id
  WHERE a.statistic#=b.statistic#
  AND s.sid=a.sid AND s.sid=s2.sid
  AND s.username={user_name}
  AND b.name = 'opened cursors current';

It is important to remark that the CallableStatement interface extends from PreparedStatement so all said about PreparedStatements also applies to classes implementing this interface. This means that sentences like:

  BEGIN mypackage.myprocedure(param1,param2);END;

must be replaced by:

  BEGIN mypackage.myprocedure(?,?);END;

in order to improve the performance using the statements cache properly.