The request cache stores certain categories of successfully parsed SQL requests and their plastic steps so they can be reused, eliminating the need to parse the same SQL request more than once. The request cache is a PE-local in-memory buffer that stores the steps generated during the parsing of a DML request.
The request cache is particularly useful for batch update programs that repeatedly issue the same SQL requests with different data values because all requests submitted during a given session are routed to the same PE, and so access the same request cache.
The request cache is also useful in a transaction processing environment where the same DML requests are entered by a number of users using the same application program.
When the request cache is initialized during Vantage startup, the number of cache entries is set to MaxRequestsSaved, a DBS Control field in the Performance group.
Immediate Caching and Non-Immediate Caching
Not all requests are cached, and not all cached requests are cached immediately.
When a request has a data parcel (specified in Teradata SQL by a USING request modifier), the system may cache it immediately using a generic plan, or it may decide to use a specific plan. In this discussion, the term data parcel always refers to a data parcel set. A non-iterated request is associated with only one data parcel, while an iterated request is associated with multiple data parcels. A request can also have no data parcels associated with it.
By exposing parameterized values before determining a plan for a request, it becomes possible to generate a more optimal plan for some categories of specific requests than could be generated without first peeking at those values. The term parameterized is used instead of USING because it is possible to send both data and DataInfo parcels with a request without specifying a USING request modifier and to specify a USING request modifier with just a data parcel and no DataInfo parcel. See Teradata® Call-Level Interface Version 2 Reference for Mainframe-Attached Systems, B035-2417 and Teradata® Call-Level Interface Version 2 Reference for Workstation-Attached Systems, B035-2418 for information about Data and DataInfo parcels.
The result is identical to what would be achieved had you, for example, specified constants instead of USING variables in all points in the request where the Optimizer considers value predicates. See Parameterized Requests for details about how this is done.
The following examples show SQL statements that produce requests with data parcels:
USING (a INTEGER, b INTEGER, c SMALLINT) INSERT INTO tablex VALUES (:a, :b, :c); USING (d SMALLINT, e INTEGER) EXEC macroy (:d, :e);
If these requests were submitted as part of a data-driven iterative request, then multiple data parcels would be involved (see Teradata® Call-Level Interface Version 2 Reference for Mainframe-Attached Systems, B035-2417 or Teradata® Call-Level Interface Version 2 Reference for Workstation-Attached Systems, B035-2418 for more information about iterated requests).
The system does not immediately cache macro executions that do not specify USING request modifiers because they do not have data parcels. The following macro does not have a data parcel because it does not have a USING request modifier:
EXEC macroz (100, 200, 300);
The Parser considers macro parameter values provided at execution time in the request text to be a part of the request parcel, not a part of the data parcel.
If a request does not have a data parcel, its plastic steps are not cached immediately. Instead, a hash value derived from the request text is stored in one of the first-seen entries in the cache management data structure.
If the same request is submitted to the Parser a second time, the request can be considered for dynamic parameterization of literals (DPL). The system may move its entry from the first-seen area into one of the cache entries with the plastic steps. In some cases, a plan is not cached until the request is seen a third time. For more information about DPL, see Dynamically Parameterized Requests.