If I told you there was a way you might be able to speed up parsing time for your queries, would you be interested?  

In Teradata Database 14.10.02 there is a new capability that allows you to expedite express requests, and I’d like to explain how that works, describe when it can help you, and make some suggestions about how you can use it to get the best performance you can from parsing when the system is under stress.  But first a little background.

What is an express request?

When data dictionary information is needed by modules in the parsing engine (PE), and it cannot be found in the data dictionary cache on that PE, an express request is issued.  That stream-lined request goes directly to the AMPs and attempts to find that data dictionary information.  Because the database code itself is issuing express requests (rather than an end user), and the database can trust itself to do the right thing, these very short requests are allowed to bypass the resolver, security, parsing and optimization modules and are sent directly to the AMPs.

Most express requests are single-AMP requests that go to one AMP.  If there is lock on the row hash of the dictionary row they are trying to access, an express request will be resent to that AMP with an access lock applied.   If data from the data dictionary is accessed using an access lock, that data is not cached as it is the result of a dirty read and that row or rows may be in the process of undergoing change.

Several different modules in the parsing engine can submit express requests.  The figure below lists some of the dictionary information that express requests access, and which modules issue the requests. Even a simple query may require 30 or more express requests to be issued, and they will be issued serially.  Things like the number of database objects referenced in the SQL, the number of statistics that the optimizer looks for, and the complexity of access rights can influence the amount of separate requests for data that will required.  

 

Assessing parsing time spent on express requests

Starting in Teradata Database 14.0 you can see the wall clock time that was spent in processing express requests. Usually this number is close to zero.  This data is in a new column in DBQLogTbl named ParserExpReq.   Below is a sample of columns from several rows of DBQLogTbl output showing ParserExpReq, intentionally selected to show variation.  The unit reported in ParserExpReq is seconds.

NumSteps

AMPCPUTime

ParserCPUTime

ParserExpReq

6

0.52

0.02

0.01

955

0.32

1.51

27.47

15

0.6

0.04

19.26

12

30.41

0.02

0.01

9

268.85

0.04

0

4

0.07

0.02

?

26

55.02

0.38

1.96

In many cases ParserExpReq will be NULL.   You will see a NULL when no express requests were issued because all the data was found in the data dictionary.  Zero means that the wall clock time for express requests was less than 0.01 seconds.  99.9% of theDBQLogTbl rows from my shared test system showed ParserExpReq values of either zero or NULL.  I would expect that to be the same on your platform.   But as you can see from the data above, taken from that same system, there were occasional times when ParserExpReq was reporting some number of seconds (close to half a minute in the worst case above), even when the CPU time for parsing was very low. 

ParserExpReq reports wall clock time for the execution of all express requests combined on behalf of a query, and will not correlate directly to ParserCPUTime.  The usual reason for ParserExpReq to be a higher number of seconds is that one or more of the express requests were blocked once they got to the AMP.   This could happen if the AMP has exhausted AMP worker tasks.

What does expediting a request do?

Expediting a request marks it for special performance advantages.  In SLES11 and current SLES10 releases, all queries within a tactical workload are automatically expedited.  As of 14.10.02 you have the capability of expediting express requests as well.  

Here’s why that might make a difference for you.  When a request is expedited it is able to use special reserve pools of AMP worker tasks (AWTs), intended for tactical queries only.  If there is a shortage of AWTs on your platform, use of these reserve pools can speed up the elapsed time of a request, as the request no longer has to wait for another request to complete and free up an AWT so that it can begin to execute.

See this blog posting on reserving AMP worker tasks for more information on how expedited requests take advantage of reserve pools:

http://developer.teradata.com/blog/carrie/2010/01/expedite-your-tactical-queries-whether-you-think-they-need-it-or-not

In addition to being given access to special reserve pools of AWTs, expedited requests are given other small internal boosts that are coded into the database.  While probably not noticeable in most cases, these slight performance advantages can contribute to completing work more quickly, especially on a platform with a high degree of contention.

The standard way that express requests are assigned to AMP worker tasks

Prior to taking advantage of this enhancement in 14.10.02, express requests were sent to the AMP in a message classified as a Work01 work type message.  Message work types are used to indicate the importance of the work that is contained in the message.  Work01 is used for spawned work on behalf of a user-initiated query (such as the receiver task during row redistribution).  It is a step up from new work (which runs in Work00).  

If there is a delay in getting an AWT, Work01 messages queue up in the message queue ahead of Work00 messages (new work sent from the dispatcher), but behind all the other work types.   If there are no AWTs available in the unassigned AWT pool at the time the message arrives, and the three reserves for Work01 are in-use, the message will wait on the queue.  This can increase the time for an express request to complete.  

 

How express requests are assigned to AMP worker tasks with this enhancement

If you take advantage of this enhancement, then messages representing express requests that arrive on the AMPs may be able to use the Work09 work type and reserve pool.  Work09 is a more elevated work type and is the work type assigned to spawned work from an expedited request.   Use of Work09 for express requests only happens, however, if there are AMP worker tasks reserved for the Work09 reserve pool.  If there are no reserves in Work09, then Work01 will continue to be used.

For more information on work types and reserve pools read this posting:

http://developer.teradata.com/blog/carrie/2011/10/reserving-amp-worker-tasks-don-t-let-the-parameters-confuse-you

The important point in all of this is that if you are often, or even occasionally, out of AWTs, then making sure your express requests are not going to be impacted when that condition arises could provide better query performance for parsing activities during stressful times.  

Steps you have to take

The default behavior for express requests will remain the same when you upgrade to 14.10.02 or 15.0.   In order to expedite express requests you will need to put in a request to the support center or your account team asking them to change an internal DBS Control parameter called:  EnableExpediteExp.

The EnableExpediteExp setting has three possible settings:

0 = Use current behavior, all express requests go to Work01 (the default)

1 = Parser express requests will be expedited and use Work09 for all requests if AWTs have been reserved

2 = Parser express requests will be expedited and use Work09 only if the current workload is expedited by workload management and AWTs have been reserved

If you set EnableExpediteExp = 1, then all express request for all queries will be expedited, even when the request undergoing parsing is running in a workload that itself is not expedited.   If you set EnableExpediteExp = 2, then only express requests issued on behalf of an expedited workload will be expedited. 

Marking EnableExpediteExp as 1 or 2 is going to provide a benefit primarily in situations where there is some level of AWT exhaustion and where an AMP worker task reserve pool for tactical work has been setup. You can look at ParserExpReq in DBQL to check if you are experiencing longer parsing times due to express request delays.   If you are, have a conversation with the support center about whether this is the right change for you.

 

Discussion
rasikatyagi 1 comment Joined 06/14
16 Jul 2014

Hi All,
We are using Teradata Studio Express Version: 14.10.01.201310271204.
 
When we execute a single statement, it pops up message "Result set contains at or over 2,000 rows.Cancel at 2,000 rows in accordance with the settings?" Is there some setting by which it can tell us 2000 should be cancelled out of how many rows, i.e. total rows being selected in the message box itself?
Rasika

carrie 430 comments Joined 04/08
16 Jul 2014

Rasika,
 
I have never used Teradata Studio Express.  I think you may have posted this question on the wrong blog.
 
Sorry I cannot help you.    You could try posting your question on Teradata Forum.
 
Thanks, -Carrie

Vinodraj 1 comment Joined 01/13
16 Jul 2014

Hi Rasika,
'Max Display row Count' is set to 2000 by default in preference.
You can increase your limit accordingly. Check out 'Max Display row Count' variable under following location
Preference -> Teradata Datatools Preference -> Result set Viewer Preference
Cheers,
Vinod
 
 

-
Vinod

SarathyG 1 comment Joined 09/06
2 months ago

Hi Carrie,
In a senario, where we have set EnableExpediteExp = 1 and AWTs have been reserved, and we have many workloads defined under Tactical, then my understanding is that, Tactical queries are likely to be highly impacted due to high request rate for work09, as tasks from all type of workloads will compete for work09 resulting in flowcontrol ?? 
For EnableExpediteExp = 2, am able to understand the clear benefit as its extra perf booster for tactical queries(alone) in terms of parsing.
What will be the ideal senario for using EnableExpediteExp = 1 ? 
 
 

- Sarathy G

carrie 430 comments Joined 04/08
2 months ago

Sarathy,
 
Although it is possible,  I would not expect tactical queries to be impacted much, if at all, from competition with express requests.    Express requests for the most part are extremely fast (that is why they are named express requests) unless they have to wait for an AMP worker task.  Most of them go to one AMP, not all-AMPs.     In addition, if your tactical applications are well-tuned, primarily single-AMP requests, then they will mostly use work08 anyway, not work09, and there will be no conflict at all.
 
The only way to know if there is an impact like you describe is to monitor the SAWT table for inuse counts and max inuse counts for work09, before and then after you change the EnableExpediteExp setting.  If max in use counts for work09 are higher than your reserve number in either case, increase your reserve number.  
 
Flow control on work09 is pretty unusual.  Even if a work09 message cannot get an AWT, that work09 message will be placed in the message queue ahead of work08, work00, work01 and other lower work types, and will get off the queue sooner than those others, so flow control is very unlikely. Most commonly it is only workone messages that experience flow control.
 
The benefit for expedite express requests for all requests is that it adds a small boost in parsing times for all queries, which is often desirable.  However, option 2, which only provides that benefit to already-expedited requests, is a fine choice as well.  It's up to you.   
 
Thanks, -Carrie

danR 2 comments Joined 01/13
1 month ago

Hi Carrie:
Our company is starting the migration from 13.10 to 14.10.  With the new 14.0 features, including 'columnar' partitioning, ...,  what should we look out for as possible 'hickup's - in the move.
thank you
danr
 

danR

carrie 430 comments Joined 04/08
1 month ago

Dan,
 
That's a question that is better addressed to your Teradata account team.  Or you might want to ask that question on one of the Teradata Q&A forums and get feedback from others who have been through the process.  I don't tend to get involved in the onsite migrations in any detailed way.
 
One thing to realize is that statitics collection functionality has been enhanced in several ways in 14.10.   You can get up to speed on that by reading two orange books by RK Korlapati on Statistics Enhancements in 14.0 and 14.10, and by checking out these postings on my blog at Dev X:
 
New opportunities for statistics collection in Teradata 14.0
 
Statistics Threshold Functionality 101
 
Easing Into Using the New AutoStats Feature
 
Thanks, -Carrie

You must sign in to leave a comment.