The SQL statement cache

The SQL statement cache is a feature that lets you store in a buffer identical SQL statements that are executed repeatedly so the statements can be reused among different user sessions without the need for per-session memory allocation. Statement caching can dramatically improve performance for applications that contain a large number of prepared statements. However, performance improvements are less dramatic when statement caching is used to cache statements that are prepared once and executed many times.

Use SQL to turn on or turn off statement caching for an individual database session when statement caching is enabled for the database server. The following statement shows how to use SQL to turn on caching for the current database session:

SET STATEMENT CACHE ON

The following statement shows how to use SQL to turn off caching for the current database session:

SET STATEMENT CACHE OFF

If you attempt to turn on or turn off statement caching when caching is disabled, the database server returns an error.

For information about syntax for the SET STATEMENT CACHE statement, see the HCL OneDB™ Guide to SQL: Syntax. For information about the STMT_CACHE and STMT_CACHE_SIZE configuration parameters, see the HCL OneDB Administrator's Reference and your HCL OneDB Performance Guide. For information about the STMT_CACHE environment variable, see the HCL OneDB Guide to SQL: Reference.