17.10 - Purging the Request Cache - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - SQL Request and Transaction Processing

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
User Guide
Publication ID
English (United States)

About Purging the Request Cache

Cached plans remain in the request cache until the system spoils them, which means they 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 purged.

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.

Purging Exempt and Non-Exempt Requests

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.

Purging a Full Request Cache

The maximum number of entries possible in the request cache depends on the setting for the DBS Control MaxRequestsSaved field in the Performance group. For more information about the MaxRequestsSaved field, see Teradata Vantageā„¢ - Database Utilities, B035-1102.

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.

Purging Statistics-Bound and Individual Request Cache Entries

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.
  • A data definition request (for example, an ALTER TABLE request) is submitted for a table that has been specified by a cached request.