15.10 - Purging the Request Cache - Teradata Database

Teradata Database SQL Request and Transaction Processing

Teradata Database
Programming Reference
User Guide

Cached plans remain in the request cache until the system spoils them, which means they are no longer valid. Whenever the Parser receives a DDL request, it broadcasts a spoil message to all PEs. DDL changes alter the database schema and can make previously valid plans nonvalid.

The spoil message causes PEs to delete only those cached requests (and their corresponding entries in the cache management data structure) that reference the database object that is changed by the DDL request.

Cached plans are also purged if they 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. In some cases, the TEMPORAL_TIMESTAMP built‑in function value is also purged.

The system purges such plans when the same request is seen for a changed CURRENT_DATE or DATE built‑in function.

An exempt request is one that would not be optimized differently if the demographics of the table were to change (assuming that table demographics might change over the period between cache purges).

If a request is exempt, it remains in request cache until space is required or until the system is restarted. Exempt requests include primary index requests that are independent of demographic changes, some types of requests that use USIs, and some types of nested joins.

All cached requests that are not marked exempt are purged periodically. The purge times are phased among the PEs so that all are not purged simultaneously.

The maximum number of entries possible in the request cache depends on the setting for the MaxRequestsSaved field in the DBS Control record (see Utilities for details). The default is 600 entries, with minima and maxima at 300 and 2,000 entries, respectively. You can increase the values in increments of 10 entries.

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.

Periodically, the system purges 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 purge.

The system purges request cache entries individually under the following conditions:

  • The cache becomes full, and space is needed for a new entry.
  • In this case, the steps for the least-recently used request are discarded.

  • A data definition request (for example, an ALTER TABLE request) is submitted for a table that has been specified by a cached request.
  • In this case, the cached plan for the request is discarded.