Elevating Parsing Priority
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 220.127.116.11, 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.