node-oracledb

15. Statement Caching

Node-oracledb's execute() method uses the
Oracle OCI statement cache
to make re-execution of statements efficient. This cache removes the
need for a separate 'prepare' method which is sometimes seen in other
Oracle APIs: there is no separate 'prepare' method in node-oracledb.

Each non-pooled connection and each session in the connection pool has
its own cache of statements with a default size of 30. Statement
caching lets cursors be used without re-parsing the statement.
Statement caching also reduces meta data transfer costs between the
node-oracledb and the database. Performance and scalability are
improved.

In general, set the statement cache to the size of the working set of
statements being executed by the application.

Statement caching can be disabled by setting the size to 0. Disabling
the cache may be beneficial when the quantity or order of statements
causes cache entries to be flushed before they get a chance to be
reused. For example if there are more distinct statements than cache
slots, and the order of statement execution causes older statements to
be flushed from the cache before the statements are re-executed.

The statement cache size can be set globally with stmtCacheSize:

var oracledb = require('oracledb');
oracledb.stmtCacheSize = 40;

The value can be overridden in an oracledb.getConnection() call:

var oracledb = require('oracledb');
oracledb.getConnection(
  {
    user          : "hr",
    password      : "welcome",
    connectString : "localhost/XE",
    stmtCacheSize : 40
  },
  function(err, connection)
  {
    . . .
  });

The value can also be overridden in the poolAttrs parameter to
the createPool() method.

With Oracle Database 12c, the statement cache size can be automatically tuned with the
External Configuration oraaccess.xml file.