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

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