By cj on Feb 05, 2016
Developers starting out with Node have to get to grips with the
called when least expected! While you are still in the initial
hacking-around-with-node-oracledb phase you may sometimes encounter
ORA-01000: maximum open cursors exceeded.
Here are some things to do about it:
Avoid having too many incompletely processed statements open at one time:
If cursors are opened with
dbms_sql.open_cursor()in a PL/SQL block, close them before the block returns - except for REF CURSORS being passed back to node-oracledb. (And if a future node-oracledb version supports Oracle Database 12c Implicit Result Sets, these cursors should likewise not be closed in the PL/SQL block)
Make sure your application is handling connections and statements in the order you expect.
Choose the appropriate Statement Cache size. Node-oracledb has a statement cache per connection. When node-oracledb internally releases a statement it will be put into the statement cache of that connection, but its cursor will remain open. This makes statement re-execution very efficient.
The cache size is settable with the
stmtCacheSizeattribute. The appropriate statement cache size you choose will depend on your knowledge of the locality of the statements, and of the resources available to the application: are statements re-executed; will they still be in the cache when they get executed; how many statements do you want to be cached? In rare cases when statements are not re-executed, or are likely not to be in the cache, you might even want to disable the cache to eliminate its management overheads.
Incorrectly sizing the statement cache will reduce application efficiency. Luckily with Oracle 12.1, the cache can be automatically tuned using an
More information on node-oracledb statement caching is here.
Don't forget to use bind variables otherwise each variant of the statement will have its own statement cache entry and cursor. With appropriate binding, only one entry and cursor will be needed.
Set the database's
open_cursorsparameter appropriately. This parameter specifies the maximum number of cursors that each "session" (i.e each node-oracle connection) can use. When a connection exceeds the value, the
ORA-1000error is thrown. Documentation on
Along with a cursor per entry in the connection's statement cache, any new statements that a connection is currently executing, or ResultSets that haven't been released (in neither situation are these yet cached), will also consume a cursor. Make sure that
open_cursorsis large enough to accommodate the maximum open cursors any connection may have. The upper bound required is
stmtCacheSize+ the maximum number of executing statements in a connection.
Remember this is all per connection. Also cache management happens when statements are internally released. The majority of your connections may use less than
open_cursorscursors, but if one connection is at the limit and it then tries to execute a new statement, that connection will get
ORA-1000: maximum open cursors exceeded.