Dynamically Parameterized Requests | Teradata Vantage - 17.10 - Dynamically Parameterized Requests - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - SQL Request and Transaction Processing

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
User Guide
Publication ID
B035-1142-171K
Language
English (United States)

Dynamic Parameterization of Literals (DPL) reduces parsing time for repeated, nonparameterized requests that differ only in the eligible literals used in the predicates of WHERE and ON clauses. Eligible nonparameterized requests are treated as identical requests after parameterization of these literals. The literals are used as the data values for the parameters.

A plan with DPL applied is cached with the associated dynamically parameterized request text. Later, when a nonparameterized request is submitted with different literal values, and the dynamically parameterized form of this request matches the cached parameterized request, the saved plan of the parameterized request from the request cache is used. This avoids the need to reparse the request and regenerate the plan.

The Parser generates a plan without applying DPL for the first occurrence of an eligible nonparameterized request, then creates a plan with DPL applied if another request (that matches the first request after applying DPL) is seen for the second time. This allows the request caching logic to compare the performance of the plans and decide whether to continue using a cached plan with DPL applied, or revert to always using the plan without DPL, depending on which plan performs better. Most workloads perform better if you enable DPL.

Whether a request is parameterized, nonparameterized, or dynamically parameterized, the decision to cache or not cache a plan is controlled automatically by the request caching logic.

The DPL logic considers only single-statement requests that have a nonparameterized SELECT statement. It determines automatically whether such a request is eligible for DPL, and which literals in the request are eligible for parameterization.

DPL is disabled by default. The DBS Control general field EnableDynamicParameterization controls whether DPL is enabled. For more information on DBS Control, see Teradata Vantageā„¢ - Database Utilities, B035-1102.

DBQL Logging for DPL

You can use DBQL logging to monitor the usage and impact of DPL. The CacheFlag column in DBC.DBQLogTbl includes values related to DPL as indicated in the following table.
CacheFlag Purpose
D Generic plan from the request cache is used for the request execution.
P Request is parsed to generate the plan by peeking the literal values (specific plan).
N Request is parsed to generate the plan without peeking the literal values (generic plan).
I Parameterized request caching logic has decided a specific plan is always better and this request is ineligible for dynamic parameterization of literals (DPL).