Previous Topic: Dynamic SQL CachingNext Topic: Impact of Database Definition Changes

Using Dynamic SQL › Dynamic SQL Caching › Searching the Cache

Searching the Cache

When a search is made in the cache for a matching SQL statement, a cache hit occurs when a matching entry is found. The following factors are considered in determining whether an SQL statement matches a cache entry:

A literal comparison of the statement's text is made against each cache entry until a match is found. A literal comparison avoids the overhead of parsing but has the consequence that an entry may not match because of differences in such things as case and spacing. For example, the following three statements are considered different if using a literal comparison:

Select * from EMPLOYEE
Select * from   EMPLOYEE
select * from employee

Specifying values as literals instead of as dynamic parameters can also result in unequal comparisons. The following two statements would be textually identical if a dynamic parameter had been used in place of the numeric values 100 and 101:

select * from DEMOEMPL.EMPLOYEE where EMP_ID = 100
select * from DEMOEMPL.EMPLOYEE where EMP_ID = 101

Note: While the use of dynamic parameters can increase the frequency of finding a matching cache entry, it may occasionally prevent the optimizer from choosing the most efficient access strategy.

When a dynamic statement that relies on a default schema is cached, both the statement text and the default schema are saved. When the cache is searched for a statement that relies on a default schema, both the statement's text and the session's default schema must be equal to their cached equivalents for the entry to match. Consider the following two statements. The first will match a cached entry regardless of the default schema in effect for the SQL session. The second will match only if the default schema in effect for the SQL session is the same as that in the cache:

select * from DEMOEMPL.EMPLOYEE
select * from EMPLOYEE

The name of the dictionary to which an SQL session is connected is always saved in the cache and compared to the session's dictionary during a search of the cache. If the two are not the same, then the cache entry does not match.

If an SQL statement references a temporary table, it will not be cached since each temporary table instance can be structurally different from others of the same name. Therefore, no statement that references a temporary table will match a cache entry.