Parsing Times and Expediting Express Requests (2018)

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

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

 

If yes, consider "expediting" express requests. This blog posting explains what that means, how it works, and when it can help you. The goal of expediting express requests is to get the best performance you can from parsing when the system is being fully utilized. 

 

Note: This is a rewrite of an earlier blog posting ("Expediting Express Requests") with a similar name that I posted back in 2014. Enhancements have been made to this capability since then, so it's worth giving this posting a read even if you are familiar with how the option used to work.

 

Let's start with a little background.

 

What is an Express Request?

When data dictionary information is needed by modules in the parsing engine (PE), and this data cannot be found in the data dictionary cache, an express request is issued.  An express request is a stream-lined request that goes directly to the AMPs to retrieve 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.

 

Express requests also bypass workload management rules, such as throttles or filters. Express requests are critical to the flow of work being processed in the database, so they will always be sent to the AMPs when submitted and never be placed in a delay queue or rejected due to a TASM or TIWM rule.

 

Most express requests are simple, such as getting a user record or validating a table name, and are issued as single-AMP requests that go to one AMP. They use row hash read locks. If there is write lock on the row hash of the dictionary row an express request is trying to access, the 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 the dirty read, as the row 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 processed 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 volume of separate express requests for data that will required by a single SQL request. And, as already mentioned, whether or not the data being sought is already in the dictionary cache will have a very big influence on the number of express requests that are required. 

 

ExpressRequests_on_PE.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

How Much Parsing Time Is Spent on Express Requests?

You can see the wall clock time that was spent in processing express requests for a given query in DBQL output. Usually this number is close to zero because express requests tend to be very short. This data is in a 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 wall-clock 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 cache on the parsing engine.  Zero in the ParserExpReq column means that the wall clock time for express requests was less than 0.01 seconds which is less than the two decimal places displayed by the field. 

 

99.9% of theDBQLogTbl rows from my shared test system showed ParserExpReq values of either zero or NULL.  I would expect that to be similar on your platform. But as you can see from the data above, taken from that test 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 the request 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.  

 

ParserExpReq can peak to some number of seconds if one or more of the express requests were blocked once they got to the AMP. The usual reason for this delay is AMP worker task exhaustion.

 

What Does Expediting a Request Do?

Expediting a request flags the request for special performance advantages.  All queries within a tactical workload are automatically expedited, and the express requests coming from tactical work will usually benefit from this status as well. By using the EnableExpediteExp setting in DBS Control (described below) you have the ability to expedite all express requests, whether or not the parent request is running in an expedited workload.

 

Here’s how EnableExpediteExp might help your non-tactical analytic work. When a request is expedited it is able to use special reserve pools of AMP worker tasks (AWTs), that are intended for tactical queries only. There is a finite number of AWTs on each AMP, and when the system is busy query steps often have to wait to get one. If there is a shortage of AWTs on your platform, use of these special AWT 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:

https://community.teradata.com/t5/Blog/Reserving-AMP-Worker-Tasks-Don-t-let-the-Parameters-Confuse-Y...

 

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.

 

Express Request Assignment and AWT Work Types

Express requests require an AWT in order to access data dictionary data on the AMPs. This AWT will be of a predictable work type depending on the status (expedited vs. non-expedited) they receive. The default behavior is for express requests to use the status (expedited vs. non-expedited) of the workload that the session has classified to during session logon. This is the workload that is reported as SessionWDID in the DBQLogTbl. See this blog posting for more information on how SessionWDID is determined:

https://community.teradata.com/t5/Blog/New-Simplified-Approach-to-Parsing-Workload-Selection/ba-p/68...

 

If the SessionWDID represents a workload that is not expedited, then all express requests that are issued on behalf of queries running in that session will use AWT work types of Work00. Express requests can spawn other express requests, and when that happens, Work01 work type will be used.  This is the default behavior.

 

If the SessionWDID is an expedited workload, as might be the case if the session were dedicated to tactical requests, then all express requests that are issued on behalf of queries running in that session will use AWT work types of Work08. Spawned express requests under those conditions will use work type Work09.

 

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  could provide better query performance for parsing activities during stressful times.  

 

Steps You Can Take to Expedite Express Requests

In order to use the expedite express request capability you will need to change an internal DBS Control parameter called EnableExpediteExp. Guidance on changing internal settings is provided by Teradata support personnel.

 

The EnableExpediteExp setting has three possible settings:

  • 0 = Use current behavior, all express requests go to Work00/01 AWT work types (non-expedited sessions) or to Work08/09 work types (expedited sessions)
  • 1 = Parser express requests will be expedited and use Work09/10 work types for all requests but only if tactical AWTs have been reserved
  • 2 = Parser express requests will be expedited and use Work09/10 work types only if the current workload is expedited by workload management and tactical AWTs have been reserved

If you set EnableExpediteExp = 1, then all express request for all queries will be expedited and will use slightly higher work types of Work09/10, even when the request undergoing parsing is running within a non-expedited workload. 

 

If you set EnableExpediteExp = 2, then only express requests issued on behalf of an expedited workload will be expedited, and they will use slightly higher work types: Work09/10.

 

At least two AWTs must be defined in the tactical reserve pool for EnableExpediteExp options to make a difference. AWT reserve pools must be contain two or more AWTs before option 1 or option 2 can be enabled.

 

Here is the key difference between how express requests are managed for expedited requests under the default setting of EnableExpediteExp = 0 and how they are managed when that setting has been changed to EnableExpediteExp = 2:

  • When EnableExpediteExp = 0 the express requests will use Work08/09 AWT work types
  • With EnableExpediteExp = 2 those same express requests will use Work09/10 work types, a step higher

When express requests start at Work09 (and spawn to Work10 if needed), they avoid use of the busiest expedited work type: Work08. Not only are there usually fewer Work09 AWTs active at any point in time, but there is less competition for AWTs in the Work08 reserve pools. This gives expedited express requests a small boost in priority and a greater likelihood of delivering quick turnaround. It could also reduce the Work08 in-use counts, and may even prevent Work08 tasks from reaching the maximum allowable AWT limit. Similar to Work01, the Work09 work type does not come with a limit.

 

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 already been setup with a reserve of two or greater. 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.