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.