Cached plans remain in the request cache until the plans are no longer valid. For example, DDL changes alter the database schema and can make previously valid plans nonvalid.
Only those cached requests that reference the changed database object are cleared.
Cached plans are also cleared if the plans are specific to a resolved date value that no longer matches the date returned by the DATE, CURRENT_DATE, or TEMPORAL_DATE built-in functions.The TEMPORAL_TIMESTAMP built-in function value may also be cleared.
Clearing Exempt and Non-Exempt Requests
An exempt request is not be optimized differently if the demographics of the table were to change (assuming that table demographics may change over the period between cache clearings).
An exempt request remains in request cache until space is required or the system is restarted. Exempt requests include primary index requests that are independent of demographic changes, and may include requests that use USIs or nested joins.
All cached requests that are not marked exempt are cleared periodically. The clearning times are phased among the PEs so that all are not cleared simultaneously.
Clearing a Full Request Cache
The maximum number of entries possible in the request cache depends on the system setting.
When all the request cache entries are full, the Parser uses a least-recently-used algorithm to determine which requests to delete from the cache management structure. When an entry is deleted from the data structure, its corresponding cached request is also deleted.
Clearing Statistics-Bound and Individual Request Cache Entries
Periodically, the system clears the request cache of all entries whose access or join plans are dependent on statistics. Entries that use only unique indexes for access are not affected by this periodic clearing.
- The cache becomes full, and space is needed for a new entry.
- A data definition request (for example, an ALTER TABLE request) is submitted for a table that has been specified by a cached request.