Parameterized Requests | SQL Request & Transaction Processing | Teradata Vantage - Parameterized Requests - Advanced SQL Engine - Teradata Database

Vantage has the ability to peek at the parameterized values of a request (such as USING request modifier values, which are user-supplied constant data) in CLIv2 data parcels, and then to resolve those variables in the Resolver. This enables the Optimizer to generate a specific, uncached plan for such requests when the system determines this is the best way to handle the request rather than always generating a cached generic plan.

Data Parcel Peeking Terminology

The following terminology is specific to the parameter value peeking subsystem:

The presence of parameterized data in a request does not automatically determine that the Optimizer will generate a specific request plan for it. There are many parameterized queries for which the query plan generated by the Optimizer does not depend on parameterized values. Such queries do not gain any added performance benefit from generating specific query plans as the result of parameterized value peeking. Furthermore, the negative performance impact of not caching a plan can be high if a request has a short execution time.

For example, consider the following query that specifies a USING request modifier whose single variable x is also used to specify a parameterized predicate condition :x in its WHERE clause.

USING (x INTEGER)
SELECT *
FROM table_1
WHERE column_1 = :x;

If there is a UPI defined on table_1.column_1, then the access path selected by the Optimizer is independent of the value of x because irrespective of its value, assuming that value specifies a valid primary index, a primary index access path is the best access path.

In the context of the parameter value peek subsystem, the following definitions apply:

Parameterized value peeking does not impact the treatment of parameterized PK requests, so the Optimizer does not generate a specific plan for those cases.

The following table indicates whether various types of parameterized request are parameterized-dependent or parameterized-independent.

CREATE TABLE emp (
  emp_id INTEGER,
  dept_id INTEGER)
UNIQUE PRIMARY INDEX (emp_id);

USING (x INTEGER)
SELECT *
FROM emp
WHERE dept_id = 10
;SELECT *
FROM emp
WHERE emp_id = :x
AND   dept_id = 12;

Parameterized Value Peeking

Peeking facilitates the optimization of certain categories of queries by inserting data values that are specific to those queries into the parse tree at an earlier stage of request processing than would otherwise be done. The system always caches generic plans because reusing a cached query plan saves parsing and optimization time. However, reusing a cached generic query plan is not always the best approach to executing a query that provides constant literals that can be used to produce a specific plan tailored closely to that specific request.

The system does not cache specific plans for requests that are parameterized-dependent, because those plans cannot be reused for otherwise identical queries that have different sets of parameterized or built-in function-supplied constant values, but it does cache the other information generated for specific plans. This information includes its SQL text hash, its host character set, its estimated execution costs, its parsing time, and its run time.

When the system determines that a plan for a request should not be cached, the parameterized request and DATE, CURRENT_DATE, TEMPORAL_DATE, CURRENT_TIMESTAMP, USER, and in some cases TEMPORAL_TIMESTAMP built-in function values can be used to optimize the request instead of treating that data as parameters with unknown values. In other words, parameterized value peeking uses the literal data values from a parameterized request and date-related built-in functions, or both, to ensure that the Optimizer generates an optimal, uncached plan for that particular request rather than generating a generic plan and then caching it for future reuse.

A specific plan generated by the Optimizer should be an optimal plan, and its runtime performance should be significantly better than the execution time for an equivalent generic plan. However, there can be cases when the runtime costs of a specific plan and its equivalent generic plan do not differ significantly. In this case, the impact is especially high if the parsing cost is high. To avoid these cases, the system monitors all such requests for their parsing and run times, and keeps the information in the request cache. The system then uses this information to decide between generating a specific or generic plan for a request. For more details about this process, see Request Caching Logic.

Enabling and Disabling Parameterized Value Peeking

By default, parameterized value peeking is enabled. To disable it, you can change the value of the DisablePeekUsing field using the DBS Control utility. For more information about DBS Control, see Teradata Vantage™ - Database Utilities, B035-1102.

Miscellaneous Considerations for Parameterized Value Peeking

Request Caching Logic

The request cache stores the plans of successfully parsed SQL DML requests so they can be reused when the same request is resubmitted. The request cache contains the text of the SQL request and its plastic steps. The plastic steps are referred to as the request plan or simply as the plan.

• When parameterized value peeking is enabled, requests are cached or not according to the actions described by the following table.

Otherwise, the database parses the request again and generates a specific plan for it.

In all other cases, the Optimizer generates the specific plan for all requests until the cache is purged. This includes the case when executing the generic plan fails, such as when it aborts or returns a fatal error during execution.

A performance benefit is expected to be seen when the system decides to generate specific plans for all subsequent submittals of the request. A performance benefit is expected to be seen when the system decides to use the cached plan for all subsequent submittals of the request.

You can submit a MONITOR SESSION request to determine the number of request cache hits in a session. You can then use that information to determine whether a generic plan has been cached and used.

A specific plan is generally more optimal than its equivalent generic plan because its parameterized values have been substituted as literals. There are, however, some requests for which the specific plan cost is the same as that for the equivalent generic plan. The logic that replaces the parameterized values to generate a specific plan cannot distinguish between queries for which the estimated specific and generic plan times are identical.

The request plans for the following two requests are identical, and peeking at parameterized values cannot help to generate more optimal plans for them. But requests such as the previous SELECT example can benefit from parameterized value peeking if a join index like the following is defined on the target base table for the query.

For example, consider the following table definition.

CREATE SET TABLE MYDB.t2, NO FALLBACK,NO BEFORE JOURNAL,
  NO AFTER JOURNAL,CHECKSUM = DEFAULT (
  i INTEGER,
  j INTEGER,
  k INTEGER,
  l INTEGER,
  c CHARACTER(400) CHARACTER SET LATIN NOT CASESPECIFIC
                   DEFAULT 'a')
PRIMARY INDEX (i)
INDEX (j);

The statistics collected on the table show the following number of unique values for the NUSI column j and the NUPI column i.

Date     Time     Unique Values      Column Names
-------- -------- ------------------ --------------------------
06/02/17 12:51:22              1,537 j
06/02/17 12:56:10                 60 k

Now consider the following query against table t2, showing the relevant step:

EXPLAIN USING (a INTEGER) SELECT *
                          FROM t2
                          WHERE j = 58
                          AND   k = 100000+i;

...
3) We do an all-AMPs RETRIEVE step from MYDB.t2 by way of an all-rows
   scan with a condition of ("(MYDB.t2.k = (100000 + MYDB.t2.i )) AND
   (MYDB.t2.j = 58)") into Spool 1 (group_amps), which is built
   locally on the AMPs. The size of Spool 1 is estimated with high
   confidence to be 10 rows. The estimated time for this step is
   0.17 seconds.
...

Also consider the following query, which is also made against table t2, showing the relevant step:

EXPLAIN USING (a INTEGER) SELECT *
                          FROM t2
                          WHERE j = 58
                          AND   k =:a + i;

...
3) We do an all-AMPs RETRIEVE step from MYDB.t2 by way of an all-rows
   scan with a condition of ("(MYDB.t2.k = (:a + MYDB.t2.i )) AND
   (MYDB.t2.j = 58)") into Spool 1 (group_amps), which is built
   locally on the AMPs. The size of Spool 1 is estimated with high
   confidence to be 10 rows. The estimated time for this step is
   0.17 seconds.
...

Now create table t3 as a copy of table t2 with data and consider the following join index definition:

CREATE JOIN INDEX j1 AS
  SELECT *
  FROM t3
  WHERE j > 58
  AND k > i+3;

Consider the following query and assume the USING value for a1 is 4:

EXPLAIN USING (a1 INTEGER)
        SELECT *
        FROM t3
        WHERE j = 80
        AND   k = i+:a1;

You can see that the value 4 has been explicitly inserted in the text for step 3, where it is highlighted in boldface type.

...
3) We do an all-AMPs RETRIEVE step from MYDB.J1 by way of an 
   all-rows scan with a condition of 
   ("(MYDB.J1.k = (MYDB.J1.i +  4  )) AND
   (MYDB.J1.j = 80)") into Spool 1 (group_amps), which is built
   locally on the AMPs. The size of Spool 1 is estimated with no
   confidence to be 126 rows. The estimated time for this step is
   0.14 seconds.
...