ColdFusioning.com
My personal blog about ColdFusion, code, Google, & computers...
ColdFusioning: Pronunciation \kold-fy�-zhn-ing\ Noun: The action of one that writes ColdFusion.
Maximum Open Cursors Exceeded on Oracle with ColdFusion
After beginning a new project for a client who required I use Oracle (I'm using version 11.1.0.6.0) for the database backend, I started running into the following error:
Error Executing Database Query.
[Macromedia][Oracle JDBC Driver][Oracle]ORA-01000: maximum open cursors exceeded
On solution is to raise the number of cursors in the Oracle configuration. Since this is not always possible or could be difficult to figure out without a dba, there is a solution in ColdFusion.
The problem arises from using cfqueryparam. ColdFusion pools queries so when the parameter changes, but the rest of the query doesn't, it can execute faster. For ColdFusion to pool a statement though, it must keep the cursor open on the database.
To solve the problem, you could remove all the cfqueryparam from your queries. However this is HIGHLY DISCOURAGED because you will lose the performance gains and also open your code up for SQL injection attacks.
Instead, simply go into the ColdFusion Administrator and lower the "Max Pooled Statements" setting. ColdFusion 9 has a default of 300. Lowering the setting to 100 solved the problem. You can experiment with the highest maximum based on your database settings.
To see how many open cursors you currently have open, run the following query:
SELECT COUNT(v.value) as OPENCURSORCOUNT
FROM V$SESSTAT v, V$SESSION s
WHERE v.statistic# = 3 and v.sid = s.sid
To view other cursor information, use the following query:
SELECT v.value as numopencursors ,s.machine ,s.osuser,s.username
FROM V$SESSTAT v, V$SESSION s
WHERE v.statistic# = 3 and v.sid = s.sid
Comments (3) | Posted At : Aug 8, 2009 9:14 AM | Views: 1294
Follow me on Twitter
| 
del.icio.us
| 
Digg It!
| 
Linking Blogs
Just a note here: the CF default value for open cursors is very low (I would argue *inappropriately* low).
It would be better to have a word to your DBA (or just DIY, it's just changing a setting) and get them to up the open cursors value on the Oracle end of things. The setting can range from 0 to 5000 (I think it's 5000... that sort of magnitude anyhow), and in my experience for even a moderately sized / busy web app, one is going to want that figure to be at least 1000.
Whilst your advice to lower it will make the error go away, it's really just treating the symptom, not the problem itself.
It's probably worth googling "oracle open cursors recommended setting" and read some of the material people have posted.
--
Adam
One thing isn't clear, though. There happens to be some confusion within Oracle about the meaning of "open cursor".
One meaning is cached JDBC PreparedStatement object. This is the number that goes up in connections from a cf app that uses a great many cfquery operations with bound (cfqueryparam) parameter variables. There's a big efficiency gain in Oracle from caching these. These are controlled by an Oracle parameter called SESSION_CACHED_CURSORS. You can find the number of these in use by counting rows in V$OPEN_CURSOR.
The other meaning of "cursor" is in-use JDBC ResultSet object, also known as open cursor. These are controlled by the OPEN_CURSORS Oracle parameter. You can find out how many of these there are from the v$sesstat view where name='opened cursors current'
These guys can leak. If the number of open cursors gets above OPEN_CURSORS some hapless user gets an ORA-01000 error.
The thing is, they do seems to leak in my CFMX7 implementation.