Today one of our sites started throwing an "ORA-01000: Too many open cursors" error. I have exactly 0 control over our server environment nor do I have access to the ColdFusion administrator. I have even less influence on our Oracle environment. We noticed the problem just before our daily scrum meeting so if nothing else, it got me out of that. Of course the first thing our sys admin did was contact our Oracle DBAs to see if they were having any problems (let the finger pointing begin, well actually I started it). They verified our data source connections and sent me a copy of the log files, which I already know weren't going to help much.
In my research, one of the Google results I came across made me chuckle. It was a Macromedia (now Archived Adobe) TechNote blaming the problem on Oracle. It said to change the OPEN_CURSORS and CLOSE_CACHED_OPEN_CURSORS settings.
It would appear that the Max Pooled Statements setting was increased in CF9, which we just upgraded too. If ColdFusion tries to keep more cursors open than Oracle will allow this error will occur. ColdFusion does this with queries using cfqueryParam and cfstoredproc to improve performance.
From Livedocs:
Max Pooled Statements enables reuse of prepared statements (that is, stored procedures and queries that use the cfqueryparam tag). Although you tune this setting based on your application, start by setting it to the sum of the following:
Unique cfquery tags that use the cfqueryparam tag
Unique cfstoredproc tags
Don't mistake Max Pooled Statements for the Maximum number of cached Queries under Server Settings > Caching, they're not the same. Max Pooled Statements can be found in the advanced settings of the data source (but I think only for SQL Server and the native Oracle driver).
The problem is, for some reason our data source was setup to connect using ODBC rather than the native Oracle drivers that come with ColdFusion Enterprise. There IS NO Max Pooled Statements setting for ODBC connections under advanced settings. Does that mean there's a similar setting in the background somewhere for miscellaneous databases? Was this setting increased in CF9? I don't know. I looked but can't find anything.
We finally got the problem solved by recreating the data source using the Oracle drivers and lowering the Max Pooled Statements setting to 100. This is probably low, but the site doesnt get that much traffic and it's below the OPEN_CURSORS setting.
If any of you smart types know why we experienced this problem with and ODBC connection, please comment!
Posts
Just thought I'd mention this will blow up on a high traffic site. I saw this while on a gov't contract. We had to tell the DB admins to constantly monitor. In our instance our Oracle server was used by many projects.
ReplyDeleteOne other thing to watch is Driver behavior on the CF server. There were also instances, in our case anyways, where just a CF server restart did not fix connection issues.
are all your queries fully cfqueryparam'ed?
ReplyDeletei have seen this caused by some mixed queries with both bound params and unbound params
you can query the system tables ( can't remember which one of the top of my hrad, ask google) and see what is being thrown at the database
Some are cfstoredproc, some use cfqueryparam, others are without the cfqueryparams. It's a legacy system that was built by people that don't know how to spell cf.
ReplyDeleteselect * from v$parameter where name = 'open_cursors'
ReplyDeletewill give the max number of cursors available.