Cached plans are good. For repetitive queries, cached plans remove the overhead of parsing and optimizing, and this helps short work perform more consistently. But sometimes cached plans, being general-purpose and one-size-fits-all, are not able to take advantage of specific optimizations that could help selective executions of the query if only the optimizer could see those parameterized SQL values. New in Teradata 12, the optimizer has the opportunity, under specific conditions, to peek at values in a parameterized SQL request. This can lead to better plans for some queries.  Here’s when it happens and how it works.

Sometimes, by incorporating specific values into the production of a parameterized query plan, better plan decisions can be made, such as which of several tables to join first, or what type of access to use for a table. By the way, this peeking approach only applies to parameterized requests of some complexity. Simple requests, with equality on a primary index, a unique primary index, or a partitioning column, will never be subject to peeking and will have the same cache flag values as they always have after the plan has been cached, ‘T’.

How Peeking Works

So, assuming your parameterized request is not such a simple equality request, here’s how peeking works. The first time a parameterized request is seen, the parser peeks at the values in the USING clause, and a plan specific to those values is produced.

This will cause the cache flag for that query’s execution to be set to ‘S’ for ‘specific plan’. At this point in time, no plan is actually cached for re-use, because a value-specific plan was produced. A value-specific plan may be different for each different execution of the same parameterized request, and will only be cached when the exact request, including values, is seen for the second time.

After this first ‘specific plan’ execution is complete, metrics from the execution, as well as the estimates that were produced, are saved in the request cache, in preparation for additional decisions that will be made should this request be seen again. If the parsing engine (PE) time that resulted from the specific plan was a very small percent of the total query execution time, then the optimizer may set a flag to always use a specific plan for this query. If that decision is made, then the cache flag going forward will contain an ‘A’ for ‘always specific’ and no generic plans will be generated for this request as long as its metrics remain in the request cache.

On the other hand, if the PE time that resulted from the specific plan is non-trivial, the second time that parameterized request is seen a generic plan is produced resulting in a cache flag ‘G’. Once both a specific and a generic plan have been generated, the estimates and run-time metrics they each produced can be compared side by side, and further decisions can be made whether to always produce the specific or always produce the generic plan.

A Common Pattern

A common pattern I often see, assuming the same parameterized request is repetitively executed on the same PE, is a cache flag sequence of ‘S’, ‘G’, ‘T’, ‘T’, ‘T’… The first time the query is seen, a specific plan is produced (‘S’), the second time a generic plan (‘G’), and from that point on, the same generic plan is executed (‘T’) until that plan is flushed from the cache.

Examples

Two different examples of how cache flag values can change in Teradata Database 12.0 are shown below.

Parameterized SQL:
This first example of selected DBQLogTbl table output illustrates rows executing the same parameterized request, sorted by start date and start time. Notice the different values for the cache flag, and the different start date and times that are reported.

The 1st row represents a parameterized query that is seen for the first time by this PE. Therefore the values in the USING clause are peeked at, a specific plan is built, and an ‘S’ appears in the cache flag column.

The 2nd row represents the second time the same parameterized request is seen, and because the parsing time used to generate the specific plan was not considered low enough to discount producing a generic plan, the generic plan is produced. Note that this results in a similar parsing time (0.06 seconds) as shown in the 1st row. Now the cache flag reads ‘G’ for generic.

The 3rd row represents the query seen for the 3rd time within a short period of time on the same PE. In this case the ‘T’ in cache flag indicates that this query used the generic cached plan that had been put in the cache by the 2nd query execution. Note that the parsing time is now zero, which is what you would expect when the plan that was used came from the request cache.

The 4th row in the DBQL output is identical to the 3rd row with a cache flag of ‘T”, which indicates that the plan is already in the cache.

With the 5th row, the same query could have continued to use the cached plan, except that so much time has passed (16 days) that the cached plan on the PE has been surely flushed. The process of specific plan, followed by generic plan will begin all over. The request cache on each AMP is flushed every 4 hours.

The 6th row is the same as the 5th, with the passage of time causing the metrics concerning the previous specific plan to be flushed from the cache.

Non-Parameterized SQL:
In the following example, a non-parameterized request is repeated 5 times with only 3-4 seconds between each execution. For this query a blank or a ‘T’ shows up in the cache flag column:

Because the request does not contain parameterized SQL and there is no USING clause, as the query above had, no peeking can take place and the ‘S’ or a ‘G’ in the cache flag column will never appear. A decision to use a specific or a generic plan is never made under these conditions. However, what this example is illustrating is that a non-parameterized query will have its plan cached when it is seen the 2nd time. This means that the 3rd such query will be able to take advantage of a cached plan, even though the query does not contain parameterized SQL.

In the first two entries above, the query is parsed, and the remaining 3 executions of that query use the cached plan, as indicated by the ‘T’ in the cache plan column.