We've recently had occasional issues with ORA-01000: maximum open cursors exceeded errors in PL/SQL code that (probably too heavily) relies on lots of EXECUTE IMMEDIATE dynamic SQL. The issues are generally mitigated by increasing the OPEN_CURSORS parameter, in some cases to values high as 10000.
Some general details (oxymoron?)…
- The code runs as a stored procedure called every minute from DBMS_SCHEDULER.
- The stored procedure queries a “queue table” and iteratively processes each record retrieved, up to 20000 records per call.
- For each record, a package procedure is called which will, in turn, call other package procedures and functions, often nested very deeply within complex logic that may further query other data and iterate through it.
- The package procedures and functions often utilize EXECUTE IMMEDIATE to perform dynamic SQL/DML where embedded SQL/DML is not practical or was beyond the skill level of the original authors to make work.
- All this performs remarkably well (from our perspective), with tens of thousands of “queue table” records processed by every per-minute job execution, although there are numerous opportunities for improvement.
- These jobs constitute the lion's share of the processing burden on our DB, but we share our DB server with another related DB whose workload would track with ours somewhat proportionally. If either DB is busy, both would be, but the SQL on the other DB isn't tuned as well as ours is ;^) . In other words, if one of us is struggling, the other usually feels it too.
- This arrangement is duplicated in a few dozen environments, as we provide similar but segregated services for lots of different customers, some of which have dedicated processing resources.
When we've seen the ORA-01000: maximum open cursors exceeded errors, the “queue table” volume was somewhat higher than “normal” (whatever that means), so our intuition is that it's related to bursts of heavy load. I'm pretty sure we don't have any “cursor leaks” as I have never captured any evidence of this in V$OPEN_CURSORS (like I do when our Java code doesn't clean up its JDBC statement resources) but can see V$SESSTAT reporting the opened cursors current value going up, although I don't believe they are reporting the exact same thing (the former from the session session cursor cache, the latter not).
I worried that increasing OPEN_CURSOR to a large value would be wasteful of DB memory for all sessions, but have since read (somewhere on AskTOM) that this only sets an upper limit, and that the memory for open cursors is doled out in 64 cursor chunks only as needed.
My somewhat ignorant theory is that Oracle will create cursors as it needs to (benefiting from numerous intentional features to make this as efficient as possible) and will asynchronously clean them up once they’re no longer needed with a separate thread akin to Java garbage collection once the cursors go out of scope and are no longer useful, so not to burden the application session with housekeeping tasks. On a very busy system, this asynchronous cursor cleaner-upper may not keep up with all the application session's cursors that have gone out of scope, since the cleaner-upper thread may have a lower execution priority than the application's do-the-work thread.
Is there any validity to this theory?
So far, I've not been able to concoct a SQL query fully enumerate all the open cursors that V$SESSTAT reports in its opened cursors current value to know exactly which statements are comprising this count and how many times each is present. V$OPEN_CURSORS typically tracks with SESSION_CACHED_CURSORS (set to 50) or maybe just a few higher. I've never seen a V$OPEN_CURSORS count higher than the low 60s for the problematic sessions even when opened cursors current is in the 1000s.
We've added some function result caching which has appeared to help with the open cursor burden and speed some things up considerably (the best way to improve performance is to not do unnecessary work) but I'm still at a loss as to how to truly evaluate our situation to see if we're OK with just setting OPEN_CURSORS to be “big enough” or if we need to do more.
Thanks for listening.
Rick