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.