New AMP Worker Task Fields in DBQL

Blog
The best minds from Teradata, our partners, and customers blog about relevant topics and features.
Teradata Employee

Several new fields have been added into the DBQLogTbl in the 16.20 software release.  This posting describes the new data, and how it can be useful to you. 

 

There are four of them:

 

  1. ReqAWTTime
  2. MaxReqAWTTime
  3. MinRewAWTTime
  4. MaxReqAWTTimeAmpNum

 

All four are carried as extrafields in the DBQLogTbl, but can be seen in QryLog view under the names above.

 

AWT_DBQL_1620.png

 

Here is an a little more information about these new fields:

 

ReqAWTTime

This field reflects the total time that all AWTs used by the request were being held in service of the request. This includes time that each WorkNew and/or WorkOne AWT work type was held, and also includes the time that an AWT was held by the request step waiting for something to complete its work (a lock, or an internal monitor, for example). It is possible that the time reported in ReqAWTTime will exceed the CPU seconds reported by DBQL for a query since an AWT is usually not continually accessing CPU, sometimes there are waits. It is also possible that it could be longer than the actual request execution time, if there are parallel steps and spawned work.

 

The value of ReqAWTTime is that it provides a sense of how long various queries are holding onto AWTs in combination, so you can identify types of requests that are heavier in their demand and use of AWT than others.

 

MaxReqAwtTime

This field captures the single AWT among all the AWTs used by the request that was held for the longest time. This gives you a sense of how long an AWT was held for the query's longest step.

 

MinReqAwtTime

This field captures the single AWT among all the AWTs used by the request that was held for the shortest time. The only caveat that applies to all Min fields is that the unless the Min field was determined by an all AMP operation step, the Min value may not be accurate.

 

MaxReqAWTTimeAmpNum

This field is this the AMP that had the highest accumulated total of AWT usage time for the whole request.

 

This information could be helpful in analyzing which queries are holding AWTs longer than others. However, workload management does not correlate directly to AMP worker tasks usage.  For example, you cannot classify queries that hold AWTs for long periods of time to a TASM workload or to a TASM throttle on expected AWT usage.  Use of AWTs is not identified until after a query begins execution.

 

However, if most queries in an existing workload share heavy use of AWTs, then that workload could be handled differently by means of existing throttles or priority assignments.  Queries that hold AWTs longer than others may also tend to have higher CPU usage than others and may have larger estimated processing times, and even elapsed times. Those types of workloads may already be being managed by priorities, exceptions, and throttle adequately.

 

This new DBQL data is additional information to supplement what you already may know about when prioritizing different types of work and setting throttle limits.

2 Comments

Hi Carrie,

 

As per above Info we checked one of our Resource Intensive Stats Collection job which runs for close to 90 mins and consumes huge Awt's while it runs and also stays in a single step for a longer period of time as Awt's are consumed and released at Step level. But the info we got in DBQL for Field22,23,25 and 39 is as below:

Is it a logging Issue? But we have other parameters in DBQL properly being collected.

 

Extrafield22   Extrafield23    Extrafield25      Extrafield39

0.21                   0.09             0.00                  486
 

Regards,

Shivraj

Teradata Employee

Hi Shivraj,

 

The units being reported are in seconds, so that does seem to be an odd number for a long-running query.  If you using DBQL Algorithm #3, maybe that is a factor.  I’d suggest you open an incident with the support center and see if they can provide an explanation to you.

 

Thanks, -Carrie