Turning the Cache ON

Use the ON option to enable the SQL statement cache. When the SQL statement cache is enabled, each statement that you execute passes through the SQL statement cache to determine if a matching cache entry is present. If so, the database server uses the cached entry to execute the statement.

If the statement has no matching entry, the database server tests to see if it qualifies for entry into the cache. For the conditions a statement must meet to enter into the cache, see Statement Qualification.

Restrictions on Matching Entries in the SQL Statement Cache

When the database server considers whether or not a statement is identical to a statement in the SQL statement cache, the following items must match:

• Lettercase
• Comments
• White space
• Optimization settings
  • SET OPTIMIZATION statement options
  • Optimizer directives
  • The SET ENVIRONMENT OPTCOMPIND statement options or settings of the OPTCOMPIND environment variable, or of the OPTCOMPIND configuration parameter in the ONCONFIG file. (If conflicting settings exist for the same query, this is the descending order of precedence.)
• Parallelism settings
  • SET PDQPRIORITY statement options or settings of the PDQPRIORITY environment variable
• Query text strings
• Literals

If an SQL statement is semantically equivalent to a statement in the SQL statement cache but has different literals, the statement is not considered identical and qualifies for entry into the cache. For example, the following SELECT statements are not identical:

SELECT col1, col2 FROM tab1 WHERE col1=3;

SELECT col1, col2 FROM tab1 WHERE col1=5;

In this example, both statements are entered into the SQL statement cache.

Host-variable names, however, are insignificant. For example, the following select statements are considered identical:

SELECT * FROM tab1 WHERE x = :x AND y = :y;

SELECT * FROM tab1 WHERE x = :p AND y = :q;

In the previous example, although the host names are different, the statements qualify, because the case, query text strings, and white space match. Performance does not improve, however, because each statement has already been parsed and optimized by the PREPARE statement.