Influence of Planned Environments on SessionWDID Selection
This posting discusses an unanticipated behavior within TASM/TIWM when it comes to selecting the workload to do the parsing engine (PE) execution activities. When a session logs on to the database it is matched to a workload that will manage all of the session’s PE work, including parsing and optimizing and including stored procedure executions. The workload (and its priority) that manages PE work while a query is executing will be reported in DBQLogTbl in a field labeled SessionWDID.
How SessionWDID is Selected
The basics of how SessionWDID is selected is discussed in a previous posting on my blog. To briefly restate, only the query’s session logon information and only a workload’s "Who" classification criteria are considered when a workload is selected for SessionWDID. The Who criteria (which includes User, Profile, Account String, IP address) is referred to as “Request Source” criteria in Viewpoint Workload Designer. Any other criteria, such as objects touched by the query or its estimated processing time are ignored during SessionWDID classification.
Mapping a session to a workload includes these conventions:
Workload Evaluation Order
Workload evaluation order is defined within Viewpoint Workload Designer, and shows both the workload name as well as the workload’s classification criteria. The user-defined workloads shown in the screen shot below (#1 through #4) happen to come with workload classification that is only made up of Who criteria, specifically they use User or Account string. They could have included additional classification criteria representing objects touched by the query or query characteristics, as is often the case.
The ALWAYS Planned Environment
Consider the second convention mentioned above: When the workload with the highest priority is selected, only the priority definitions from the ALWAYS planned environment are considered. Even if a planned environment other than ALWAYS is active at the time a session logs on, the priorities established in the ALWAYS planned environment, and only that planned environment, will determine SessionWDID.
Consider the four user-defined workloads shown above in the evaluation order. Suppose they were prioritized like this in the ALWAYS planned environment:
Assume further that they were prioritized like this in planned environment created by the user called DAYTIME:
Following this scenario, imagine that a session logs onto the system during business hours when the DAYTIME planned environment is active. This user who logged on matches to “*_Level1Batch” and the Account String of that User happens to match to *PROD*. When this session submits a query, what workload will show up in DBQL as the query’s SessionWDID?
To answer that question you would have to look at the classification criteria of all workloads and look for a match between each workload's Who criteria and the session’s logon information. Looking at the evaluation order information that displays each workload’s classification criteria, you can see that there are two candidate workloads for the SessionWDID (General-Batch by Account String and Level1_Batch by User). To determine which will become the SessionWDID go to the ALWAYS planned environment and see which of the two workloads has the higher priority.
In the ALWAYS planned environment General_Batch has the higher priority, so that becomes the SessionWDID. This is the case even though Level1_Batch workload had a higher priority in DAYTIME, the planned environment that was active at the time of the logon.
Any Action to Take?
Because it is the default, many sites use ALWAYS as their only planned environment or in conjunction with a few other planned environments. However, there are some sites that do not include the ALWAYS in their normal periodic processing window changes. Even if ALWAYS never becomes active at your site, you cannot delete this default planned environment. ALWAYS is consistently present and each workload you define will be placed in ALWAYS , just as it will be placed in a default position in all other planned environments.
If ALWAYS is never active at your site, then there is an opportunity to arrange the various workload priorities in ALWAYS in such as a way as to favor the workloads that are desired as SessionWDIDs. In our example, if you never want General_Batch to be used for a SessionWDID, then move it to Timeshare Low in ALWAYS, effectively lowering its priority to the greatest degree possible. At the same time you could move some other workloads to Timeshare Top to ensure they get chosen for SessionWDID should there be more than one candidate for SessionWDID.
Even if you are using ALWAYS as one of your regular planned environments, be aware of its role in influencing SessionWDID.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.