For sites using Teradata Active System Management (TASM), workload classification determines which workload will manage the AMP activity when a query executes.  Workload classification takes place after all the parsing engine (PE) work for a query has completed, including optimization.

But at what priority does the parsing happen?  And can the priority of parsing be raised?   

How Parsing Priority was Initially Determined

First, consider how parsing priority used to be determined by TASM back in the V2R6 and Teradata 12.0 days.

In the early TASM days, the first query in a session would execute its PE work at the M (default) priority.   From that point on, subsequent queries in the same session would use the workload that the previous query in the session classified to for its AMP activity.  Query demotions were not factored into this decision, only the workload where the previous query in the session began its execution.

If the previous query in the session classified to workload called WD-Qry-High, for example, then the next query in the session would parse in the allocation group associated to the WDQry-High workload, even if this second query itself was very long.  The opposite was also true.  If the first query in a session classified to WD-Qry-Low workload, even if the next query in the session was very short, such as a single-AMP query, it’s parsing would take place at the priority of WD-Qry-Low.

As a result of this earlier approach, cases came up where parsing was running at a lower priority than expected. 

How Parsing Priority is Determined Today

The solution was an enhancement that is in Teradata 12.0.2.22, which lets TASM proactively parse at a more predictable and in many cases a higher priority.  With this change, a query's PE work takes advantage of what is called "session classification" which happens at logon time.   Session classification maps the PE work of a query to a workload, but only based on the WHO classification criteria.  All other criteria are ignored.   Query detail that allows mapping to WHO classification (referred to as Request Source criteria in Viewpoint) is available at session logon and includes criteria such as Account string, Application, Username or Profile.  After examining this logon-related information associated with the query, TASM attempts to match the query to one or more workloads based on the WHO criteria. TASM picks the workload that is associated with the highest priority scheduler relative weight that satisfies the WHO criteria of the query.

Because PE classification is on WHO criteria alone, it is likely that a match could be done to several workloads that share the same WHO specifications, but are differentiated using different query characteristics (such as estimated processing time).  The important part of this enhancement is that TASM picks out the workload with the highest relative weight, giving PE work for that group a somewhat higher priority than AMP work, unless that AMP work is also running at the highest priority among workloads with the same WHO criteria.

This means that parsing should be running at a more predictable priority with 12.0.22 and beyond.  However, it still may be low priority for situations where the highest-priority workload based on WHO criteria alone is a low priority.  If that is the case, here's something you can think about doing:

Setting up a Special Parsing-Only Workload

If you are using TASM, you  can design a special workload for parsing only and associate it with a high priority. For this workload's classification, include all (or a subset if you wish) of their users' usernames in the workload's WHO criteria (for example, username = *).  By doing so, all queries from all (or a subset of) users will match to that workload for their PE work because it is such a high priority compared to other workloads that might support those same user names.   You can do the same thing using any WHO criteria that you wish, account string for example, or application.

But an important second step needs to be taken.  You must remember to put in dummy secondary classification for that parsing-only workload so that no query will ever successfully map to the workload for it's AMP work.  Applying this secondary classification criteria will make sure no queries actually execute their AMP work there.  For example, you could create a special table just for this purpose with one row and one column.  Then specify that table in the parsing workload’s secondary classification criteria. 

This technique is not going to reduce the resources required to do parsing, if the plan is complex or many decisions have to be made by the optimizer in producing the plan.  However, it may speed up the time to do that parsing, because parsing may now be running at a higher priority.

Of course, as is true with all workload management decisions, you need to examine the tradeoffs involved.  When you increase the priority of one type of work, by definition you reduce the priority of some other work.  So make sure you are keeping an eye on the overall balance of priorities on your platform.  And you may want to only apply this parsing-only workload to just a critical set of queries, rather than all active queries.

 

Discussion
rfenwick 2 comments Joined 10/04
07 Mar 2013

Carrie - thanks for this freat article.  I always enjoy your blog!
I didn't realize the process had changed, I thought it was always in the default partition.  Is there a way to see into which WD parsing is being classified?  I'm assuming that since parsing is part of the query, the WD I see in DBQL is the query's classification after parsing.
Also, is there a way to classify prepare statements?  We have many applications that issue prepare statements, and I have difficulting classifying them where I want.  For example, I have a single amp tactical application that classifies on name and single or few amps (to protect the system in case they put in an all amp operation).  I would like the prepare statement to classify to the same WD, as it should be in an expedited WD as well.  However, it doesn't.  It falls through and ends up in the default. 
I can open an incident, but it appears that 0 AMPS doesn't classify as single or few amps. 

Ruth Fenwick

carrie 431 comments Joined 04/08
07 Mar 2013

Hi Ruth,
Yes, you can see the WDID for parsing in the DBQLogTbl.  It depends on the release as to which field.  In TD14 / TD14.10, the field is named SessionWDId.  Prior to that, it was logged in ExtraField1.
I would expect prepare statements to use session classification, and go to the highest priority workload considering just the WHO criteria of the query.   I spoke to a couple of associates, and neither could confirm that one way or the other.  At a minimum make sure the WHO criteria (now called request source criteria) of the query matches the WHO criteria of the workload where you want it to go, if you have not already done so.  
You are correct that the prepare will have zero AMPs, but that will not be considered for session classification because number of AMPs is not a WHO criteria, unless for some reason the prepare is not being recognized as actually being parsing.
I think you are going to have to open an incident to get a definitive answer on that.   
Thanks, -Carrie

rtefft 2 comments Joined 03/09
18 Mar 2013

Carrie,
Would this approach be useful for speeding up execution of complex stored procedures?  We have a handful that have many small queries in loops (ugh I know, but unavoidable).  The amount of data is small so I believe we are just drowning in parsing all the micro-queries.  Any thoughts would be appreciated.  Thanks for all the great insights.
-Rich

carrie 431 comments Joined 04/08
20 Mar 2013

Rich,
 
Good question!
 
Yes, the stored procedure code itself will run under session classification, the same as parsing.   If you look at the DBQL row for the SP call, you will not see the workload matched to based on session classification in the WDID field.  But rather in a field called SessionWDID in 14.0.  In releases earlier than 14.0, the WDID for session classification is carried in ExtraField1.

Roopalini 22 comments Joined 05/08
08 Apr 2013

Hi Carrie,
Thanks for the wonderful article. Could you let me know, if there are any reasons for EXPLAIN to run for long time? One of the users on the box is trying to optimize his query and when he runs a explain on the query, it takes 23 seconds to just run the explain. I checked the DBQLOGTBL and found that the parseCPUTime is 23 seconds. I am unable to understand why running an EXPLAIN should take 23 seconds to give the output? Since we have TASM enabled, I was thinking if the workload priority is causing it, however I ran the query with system ID and it still took the same 23 seconds and so I ruled out TASM. Could you help me understand the reason ?
Thanks
 
 

carrie 431 comments Joined 04/08
09 Apr 2013

There are many things that can impact parsing elapsed time.  Some of them include:
 
- a very complex plan has to be constructed
- the optimization processing is running at a low priority
- the system is very busy doing other work
- the node has a large number of AMPs also competing for time on the CPUs
- there is a lot of data dictionary access required, to get table, column and statisitics info
- the data dictionary cache on the PE is too small to keep all the above details cached
- there is contention in the database when accessing the data dictionary (as performed by express requests)
- you are running out of AMP worker tasks on the AMPs, slowing down express requests
 
Check the actual workload the parsing was executing in.  See my earlier response just above your comment for which DBQLogTbl field to look at.
 
Also, see my current Teradata Magazine column to find out how to look at how much time express requests are taking:
http://www.teradatamagazine.com/v13n01/Tech2Tech/Favorable-Outcomes/
 
Thanks, -Carrie

11 Aug 2013

Hai Carrie  , can u plz provide me the detailed description about join stratagies,join indexes and collect statatics ? because i am new to teradata.
 
Thanks  &  Regards
Hari Krishna
 
 

harikrishna kanet

carrie 431 comments Joined 04/08
12 Aug 2013

Please post questions such as this that are off the topic of the blog posting and of a general nature to Teradata Forum, at:
http://forums.teradata.com/forum
 
Also, you can read about how Teradata works in all the ways mentioned above by accessing the official product documentation at:
 
http://www.info.teradata.com/
 
Thanks, -Carrie

Pigula 1 comment Joined 03/13
01 Apr 2014

Hello, Carrie. 
This article is helpful, especially the list of things that can impact parsing elapsed time.  
The customer has a very complex query that runs in a total of 22.5 seconds, of which 17.5 seconds are spent parsing.   
The difference from other cases in the blog is that the Teradata system is a 2-node 2700 on Teradata 14.10.  (no TASM).
Are there any adjustments we can make to the environment to cut down parsing time?
Thanks.
Bill Pigula

carrie 431 comments Joined 04/08
04 Apr 2014

Hi Bill,
 
Unfortunately, I don't have any suggestions I can make to improve parsing time on an appliance.  The comments made in my blog posting apply only to sites with TASM and that have defined multiple different workloads for the same WHO criteria.  In SLES 10, the performance group  is determined solely on account string, and you don't have WDIDs.  Both parsing and AMP work will use that same performance group.    I can't think of any way to get around that.
 
You could open an incident with the support center and have them look at specific cases where parsing was unreasonably high and see what they can suggest to you.  Obviously, if you can cache plans by means of parameterized SQL, that can dramatically reduce parsing time.  Sometimes increasing the dictionary cache can eliminate the need to go to the database as often during parsing to get specific dictionary information.   There also may be some DBS Control parameters that could be tuned.  But I am not the right person to attempt to advise you on system-type settings.  
 
Thanks, -Carrie

Somik.Dalal 2 comments Joined 10/12
13 Jun 2014

Hi Carrie,

How Parsing Priority can be adjusted on TD 14.10 version? 

Can we set up Parsing only workload on TD 14.10 version?

 

Regards - Somik

carrie 431 comments Joined 04/08
13 Jun 2014

Somik,
 
Yes.  Everything described in the posting above applies to 14.10 and later Teradata releases as well.  Parsing will run under the Session WDID in 14.10, just as it has for all the realeases starting in 12.0.22.  You can actually see the SessionWDID column in DBQLogTbl and that will tell you the priority that parsing ran under for that query. 
 
The most important thing about setting up a high priority workload for parsing is to make sure that you give it adequate classification crtieria that makes it impossible for the AMP work that a query does to run there.  You don't want that workload to show up in DBQLogTbl WDID field of any query.  Just the SessionWDID.
 
Thanks, -Carrie  

Somik.Dalal 2 comments Joined 10/12
16 Jun 2014

Thanks Carrie for your response.
Regards - Somik

You must sign in to leave a comment.