15.10 - Request Cache - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
User Guide
featnum
B035-1142-151K

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 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 Teradata Database start‑up, the number of cache entries is set to MaxRequestsSaved, a DBS Control field. The default value of MaxRequestsSaved is 600 and the value can be modified within the range of 300 - 2000.

The total size of the request cache that can be saved is limited to 100 MB. This limit is checked only if the number of request cache entries used is more than 300. If the number of request cache entries in use is 300 or less, then 100 MB limit is not checked.

By increasing the number of request cache entries, cached plans may stay in memory longer, improving processing time for requests that have been coded to take advantage of cached plans.

The Parser checks the request cache at the beginning of the parsing process, before the Syntaxer step, but after the Request has been checked for format errors. If it finds a matching cached request, the Parser bypasses the Syntaxer, Resolver, Optimizer, and Generator steps, performs a discretionary access control security check (if required), and proceeds to the OptApply stage.

Note that the Parameterized Value Peek subsystem is a component of the Parser, and it, too, operates on all incoming SQL requests. See “Peeking at Parameterized Values in the Data Parcel” on page 28).

The Parser does not always cache non‑primary index parameterized requests that are sent with a Data parcel. Depending on the parameterized values, the Optimizer might choose a different execution plan than what has been cached because reusing the cached plan might not always be the best choice. To deal with this possibility, the system plugs the parameterized constant values into a request before the Optimizer has to determine which query plan to use.

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 and Teradata Call-Level Interface Version 2 Reference for Workstation-Attached Systems 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 “Peeking at Parameterized Values in the Data Parcel” on page 28 and Ghazal et al. (2009) for details about how this is done.

Because cached requests and their plastic steps can be shared across logons (and privileges can be revoked), the Parser always makes a discretionary access control security check on them. The first time a request is parsed, the Resolver builds a list of required privileges, and stores that list with the request. When the cached request is reused, the list of privileges is checked for the new user.

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 chapter, 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.

Whether a request uses a generic or specific plan is determined by the Parameterized Value Peek subsystem (see “Peeking at Parameterized Values in the Data Parcel” on page 28). The Parameterized Value Peek subsystem determines whether or not the plan should also be cached.

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 or Teradata Call-Level Interface Version 2 Reference for Workstation-Attached Systems for more information about iterated requests).

The system does not immediately cache parameterized macros 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 parameter values provided at execution time 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 and its plastic steps are cached, and the system moves its entry from the first-seen area into one of the cache entries in the cache management data structure.

Each request cache consists of several major components:

  • A data structure to manage the cached requests
  • This structure contains information about one of the cached requests, plus a number of first-seen entries containing information about requests that have not yet been cached.

    The structure is always memory‑resident and its size changes dynamically so it is only as large as necessary to contain all the cached requests.

  • The SQL text and plastic steps for the individual requests
  • The cached requests consist of the text of the SQL requests and, optionally, their plastic steps.

    Individual cached requests are stored in PE memory.

    Although the request cache is not shared among the PEs on a system, the data management structure is shared among all Parser tasks for the same PE, and cached requests can be shared across sessions and logons for a given PE.

    Teradata Database uses the following criteria to determine whether to cache a DML query plan or not.

     

    If a DML request specifies …

    THEN its steps are …

    a USING request modifier

    cached the first time the request is parsed if the request is USING‑independent (see “Peeking at Parameterized Values in the Data Parcel” on page 28).

    If the request is USING‑dependent, then the steps might not be cached.

    no USING request modifier

    not cached the first time the request is parsed.

    If the request is parsed a second time, then the steps are cached.

    The determination of whether the cached plan is specific to the request or generic is made by the Parameterized Value Peek subsystem (see “Peeking at Parameterized Values in the Data Parcel” on page 28).

    A new request is considered to match an existing cached request when all of the following criteria are met.

    To match a cached request, a new request must:

  • Match the candidate cached SQL request text character-for-character.
  • An exception is made only for values in the Data parcels of USING‑independent requests.

  • Have been submitted from a client with the same host format as the candidate cached request (see International Character Set Support for details).
  • Use the same character set as the candidate cached request (see International Character Set Support for details).
  • Use the same collation sequence as the candidate cached request (see “SET SESSION COLLATION” in SQL Data Definition Language and International Character Set Support for details).
  • Match the date value returned by the DATE, CURRENT_DATE, or TEMPORAL_DATE built‑in functions if the request is specific to a resolved date. The Parser purges such plans when it encounters the same request for a changed DATE, CURRENT_DATE, or TEMPORAL_DATE built‑in function value.
  • This topic outlines the process used to determine whether a new request matches a cached request.

    By using a combination of the hashed value derived from the request text, length information, and other flags, the Parser can make a preliminary identification of matching requests without comparing the new request to each cached request, as seen in the following process.

    1 The Parser creates a preliminary list of variables derived from the new request that consists of the following items:

  • SQL text hash value
  • Request parsing time
  • Request run time
  • Request error conditions
  • Request length
  • Various other flags
  • 2 The Parser tests the preliminary variable list for the new request against the same information for the requests in the request cache.

     

    IF the preliminary information …

    THEN the Parser …

    matches a cached request entry in the cache management structure

    fetches the full cached request for comparison.

    does not match a cached request entry in the cache management structure

    compares a hash of the new request to the hash values of its first-seen entries.

  • If a match is found, the entry is moved to one of the slots in the data structure and its SQL text and plastic steps are stored in the request cache.
  • If no match is found, the entry is marked as first-seen and cached for future reuse.