Turning the Cache OFF

The OFF option disables the SQL statement cache. When you turn caching OFF for your session, no SQL statement cache code is executed for that session.

The SQL statement cache is designed to save memory in environments where identical queries are executed repeatedly and schema changes are infrequent. If this is not the case, you might want to turn the SQL statement cache off to avoid the overhead of caching. For example, if you have little cache cohesion, that is, when relatively few matches but many new entries into the cache exist, the cache management overhead is high. In this case, turn the SQL statement cache off.

If you know that you are executing many statements that do not qualify for the SQL statement cache, you might want to disable it and avoid the overhead of testing to see if each DML statement qualifies for insertion into the cache.

Statement Qualification

A statement that can be cached in the SQL statement cache (and consequently, one that can match a statement that already appears in the SQL statement cache) must meet all of the following conditions:

It must be a SELECT, INSERT, UPDATE, or DELETE statement.
It must contain only non-opaque built-in data types (excluding BLOB, BOOLEAN, BYTE, CLOB, LVARCHAR, and TEXT).
It must contain only built-in operators.
It cannot contain user-defined routines.
It cannot contain temporary or remote tables.
It cannot contain subqueries in the Projection list.
It cannot be part of a multistatement PREPARE.
It cannot have user-privilege restrictions on target columns.
In an ANSI-compliant database, it must contain fully qualified object names.
It cannot require re-optimization.