Parsing time is often an afterthought. But some sites have a concern because parsing is taking longer than expected. But whether the time to accomplish parsing is becoming an issue for you or not, there are small benefits that can be enabled by creating a special workload to manage the parsing activity for all your queries. This blog posting explains why and how to set up a "parsing only" workload on your system.
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 typically 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:
Below are two examples that illustrate how this selection happens. Note that non-Who classification criteria that you would expect to be defined on a WD has been removed from these examples:
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.
All session 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 name a dummy table in the parsing workload’s secondary classification criteria, a table that you are confident no one accesses. The best way to do this is to name a table that does not exist in any database. (Viewpoint Workload Designer will not validate that the table you name exists.)
Here is an example of how using a dummy table would look:
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.