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:
ColdFISH is developed by Jason Delmore. Source code and license information available at coldfish.riaforge.org
Error Executing Database Query.
[Macromedia][Oracle JDBC Driver][Oracle]ORA-01000: maximum open cursors exceeded
1Error Executing Database Query.
2[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:
ColdFISH is developed by Jason Delmore. Source code and license information available at coldfish.riaforge.org
SELECT COUNT(v.value) as OPENCURSORCOUNT
FROM V$SESSTAT v, V$SESSION s
WHERE v.statistic# = 3 and v.sid = s.sid
1SELECT COUNT(v.value) as OPENCURSORCOUNT
2FROM V$SESSTAT v, V$SESSION s
3WHERE v.statistic# = 3 and v.sid = s.sid
To view other cursor information, use the following query:
ColdFISH is developed by Jason Delmore. Source code and license information available at coldfish.riaforge.org
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
1SELECT v.value as numopencursors ,s.machine ,s.osuser,s.username
2FROM V$SESSTAT v, V$SESSION s
3WHERE v.statistic# = 3 and v.sid = s.sid
My core expertise focuses on architecting multi-lingual, enterprise-level digital solutions, including custom CMS's and large-scale applications which span multiple devices and computing languages.
Comments