PROVE IT !!If you know it then PROVE IT !! Skill Proficiency Test

Teradata : Parsing Workload Selection

As of Teradata Database 14.10.05 and 15.00.03, a simplified approach to determining which workload will support session management and parsing has been adopted , This posting describes this more straightforward approach which will be used starting in these 14.10 and 15.0 releases, and going forward in all future releases.

An earlier posting titled:  “A Closer Look at How to Setup a Parsing-Only Workload”  explains how session and parsing workload assignment takes place prior to this more simplified approach.

Background:  Session Classification

“Session classification” happens at session logon time.  Session classification determines which TASM or TIWM workload will be used by tasks on the PE that get the session established before any queries are issued.  These parsing engine tasks do things such as validate the user who is logging in, and they execute under the control of a workload, just like AMP work does.  Once a session begins to submit queries, all parsing and optimizing work on behalf of the queries will run at the session priority that has already been established for this session at logon time.

The workload that a session classifies to can be identified in DBQLogTbl in field SessionWDID.  The workload used for session handling is the same workload that will support all parsing and optimizing activity for the queries that are executed within that session.

How a Session Classifies to a Workload

A session classifies to a single workload, based strictly on the “Who” classification criteria (referred to as Request Source criteria in Viewpoint) of the workload.  Information about a session is available at session logon time and includes Who classification criteria such as Account string, Application, Username and Profile.

A workload often has other classification criteria in addition to Who criteria.  All other non-Who classification criteria that the workload might have (such as estimated processing time) are ignored at session classification time. That non-Who criteria will be used only to determine which workload is chosen when a request executes on the AMPs.

TASM and TIWM determine the workload that will support session management (and secondarily, request parsing for the session’s requests) by taking the following steps:

  1. Sort all workloads by priority (highest priority workloads first).   In SLES 10 priority is expressed by relative weight.  In SLES 11 priority is based on tier first, and within each tier the highest global weight.  If there are multiple virtual partitions, all Tier 1 workloads will sort above all Tier 2 workloads, etc.  In Timeshare, all Timeshare Top workloads will sort above all Timeshare High workloads, etc.
  2. Select the first workload encountered from within the sorted list where there is a match between the session logon information and the Who criteria of the workload.   This selection process does not take into consideration the number of matches between a session and a workload’s Who criteria. The first workload that has its Who criteria matched by the session information will be selected.  Note that if there are multiple Who criteria on the workload being considered, the session must match all of those criteria for the workload to be considered for session management and parsing.

Below are two examples that illustrate how this selection takes place:

In the example above, all sessions from User A will classify to WD-Quick.  WD-Quick is the highest priority workload among all workloads that User A will match to.  In addition, WD-Quick has only a single classification criteria—on User.   WD-Quick won’t be tested against a session’s Account because parsing workload selection only needs to match to the workload’s defined Who classification criteria, in this case User.

In this second example, all sessions from User A and Account YY will match to WD-Slow.  Such sessions will not match to WD-Medium because WD-Medium requires both User = A and Account = XX to be satisfied.

In summary, the highest priority workload is selected for parsing where there is a match on the workload’s defined Who criteria, as long as there are no mismatches between other session characteristics and other Who criteria on the same workload.

Setting up Special Parsing-Only Workloads

With this background, you can set up one, several or even many workloads specifically for parsing if you wish.  Or you can just use the existing workloads and observe the information in the SessionWDID field in DBQLogTbl output using this new understanding to make better sense of the priority at which parsing is taking place.

If you decide to set up a single higher-priority workload for all parsing activities for all requests, then you will want to create a new workload and give it a very high priority.

To make sure this workload is chosen to perform parsing for all requests, consider including a Who criteria (such as Username, or Account) using the broadest possible matching scope (Username = * or Account = *).  Only a single wild card Who classification criteria is required because the selection is not based on the number of criteria matches.

It is recommended that Profile classification criterion not be used in defining a parsing-only workload, unless all users accessing the platform have an actual Profile assigned.   When there is wild card classification on Profile (Profile = *), any session that logs on without providing a Profile will be disqualified from the workload.

A Second Important Step

Because you only want parsing work to run in the high-priority parsing-only workload, and you are using wild card Who criteria, an important second step needs to be taken so you can prevent AMP work from running there (it is a parsing-only workload after all).  You must put in dummy secondary classification for that parsing-only workload so that no query will ever successfully map to the parsing-only workload for its AMP work.

For example, you could create a special dummy table just for this purpose that you are confident no one actually accesses. Then specify that table in the parsing workload’s secondary classification criteria.  Or you could setup multiple criteria that are contradictory and therefore highly unlikely to ever be represented together in a query.  For example, you could add query characteristic criteria that mandates that only single/few AMP queries will run in the workload, and at the same time include very large plan estimates, as shown in the following example.

Establishing a parsing-only workload 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, the same number of CPU seconds will be required to accomplish parsing. However, a high priority parsing-only workload may speed up the time to do that parsing when the system is busy, because parsing may now be running at a higher priority.

You could consider placing this parsing workload on the SLES 11 Tactical Tier, but be very careful in doing that.   If some of the parsing activity consumes more than 2 CPU seconds per node, the parsing task risks being demoted to a lower priority workload due to the automatic tactical exception that all tactical workloads come with.  If you wish parsing to take place at a tactical priority, consider increasing the CPU threshold for demotion in the parsing-only workload’s tactical exception definition, so that it is greater than any expected parser CPU times.

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.  As a result, you may want to only apply this parsing-only workload technique to just a critical set of queries, rather than all active queries.

Add a Comment

Your email address will not be published. Required fields are marked *