What is the Estimated Processing Time?

Blog
The best minds from Teradata, our partners, and customers blog about whatever takes their fancy.
Teradata Employee

The question of what Estimated Processing Time actually is comes up a lot. For example, the DBQLogTbl table carries an “EstProcTime” Value. If you are EXPLAIN-savy, then you’ve bumped up against “Estimated Time” numbers in almost every step of every query plan you’ve ever looked at. You TASM users frequently rely on “Estimated Processing Time” as a classification criteria to manage the priority that a query will enjoy. Some think Estimated Processing Time is an estimate of clock seconds, and others believe it represents CPU seconds. Here’s what it actually is.

First, it’s consistent. Estimated Processing Time comes from the same source whether it’s within Database Query Log (DBQL), EXPLAIN text or used by workload management. It represents the cost estimate the optimizer produced for a given query step, or for the entire query. Estimated Processing Time takes into account CPU, I/O, and network (BYNET) estimated costs in combination, and self-adjusts for higher-powered hardware.

The original purpose of Estimated Processing Time was to allow the optimizer to compare different costs for different database operations when building a plan. And even today, it really has no absolute meaning except within the optimizer's algorithms. However, if good statistics have been collected, estimated processing time can give you a reasonable sense of whether the query is likely to be very short or very long, or somewhere in between. Because it usually correlates with the resulting resource requirements of the request, Estimated Processing Time is a popular secondary classification criterion when using TASM, or for use with the step time threshold option when defining throttles.

5 Comments
Enthusiast
then what is the realy user response time?
And how to get the auctual run time?
the gateing time = user response time - auctual run time?
Teradata Employee
Response time is not the same thing as estimated processing time, although there is often a correlation.

Response time is the difference in wall clock time between the time the query was submitted and the time an answer set was returned. Response time can be calculated (at least for the database side of things) from DBQLogTbl data, by subtracting starttime from firstresptime.

Response time can be influenced by many things, such as the priority of the work that is running, other activity on the platform, lock contention, resource shortages, or hardware problems.

Estimated processing time is something different. It is the optimizers view on the overall cost of the query plan, relative to other potential plans that it considers. It is possible for a query with a low estimated processing time to run for a long time, just as it is possible for a query with a high estimated processing time to run faster than you might expect.

It's just like if someone goes to a doctor when they are young, the doctor might say this person can expect to live to be 100 years old. But then this person might get hit by car when he's 40, so his actual life-span is quite different from the estimate. The same is true with estimated processing time, and actual response time.
Enthusiast
Hi Carrie,

I am using DBQL logs to examine the relationship between actual query duration and TDWMEstTotalTime (for the purposes of classifying user queries into workloads based on Estimated Processing Time). I have noticed a few things which I am hoping you can clarify.

(1) The TDWMEstTotalTime and EstProcTime fields of the dbqlogtbl table appear to be recording estimated query time in milliseconds and seconds respectively - is this true ?

(2) The explain text for some steps do not list an estimated processing time. I seem to remember reading somewhere that this happens when there is only one way of performing the step and therefore the optimiser doesn't need the estimated time as there are no alternative actions to compare it to. Nevertheless, is there an estimate made for such steps and are they reflected in TDWMEstTotalTime and EstProcTime?

(3) Some queries in dbqlogtbl have a null value for TDWMEstTotalTime (the corresponding EstProcTime value is always 0.00 in this case). For the purposes I have described above should I treat this as a null (truly unknown) or zero ?

(4) With the old Teradata Dynamic Workload Manager tool it was possible to ignore cost estimates below a specified confidence level. I can't find this option in Viewpoint. Can it be done or is it no longer relevant ?

P.S. I am using Teradata 12.0.3.33

Thanks
Teradata Employee
Hi Andrew,

1. You are correct. TDWMEstTotalTime and EstProcTime fields of the dbqlogtbl table are in milliseconds and seconds respectively.

TDMWEstTotalTime is kept internally as milliseconds and that is how DBQLogTbl reports it. But the view (QryLogTDWM) does divide by 1000 before displaying the data.

DBQLogTbl QryLogTDWM

34 0.033715
15,697 15.697290
12 0.012486
156 0.156234

2. Both DBQL and TDWM are only able to use the estimated step values that have been supplied by the parser. They essentially keep a running total of the values given. If a step does not happen to have an estimated time value, it is skipped in the calculation of total time.

3. There is a fix for this situation. See DR 158531.

4. The minimum explain confidence level was dropped as a qualification option a couple of releases ago. I was in favor or retiring that option because I found for many users that it was confusing and did not usually work the way you might expect.

In addition, confidence levels are only marginally controlled by stats collection decisions making it difficult to predict when you might or might not receive a specific confidence level. This is especially true for queries that have a large number of steps. I have an article here on Dev X that goes into how confidence levels work, if you want more detail on why it is not very reliable for classification or qualification purposes.

Thanks, -Carrie
Teradata Employee
Sorry my example got all squished together, let me try to make that clearer:

DBQLogTbl _ _ QryLogTDWM

34 _ _ _ _ _ _ _ _0.033715
15,697 _ _ _ _ _ 15.697290
12 _ _ _ _ _ _ _ _ 0.012486
156 _ _ _ _ _ _ _ 0.156234