This section describes the use of deep drill-down analysis and refinement of workloads. Deep drill-down and refinement of workloads can be executed during the second level of analysis.
For information about basic second level workload analysis, see “Step 2: Refining and Analyzing Recommended Workloads” on page 61.
This section includes the following:
Overview of Deep Drill-Down and Refinement
After one or more workloads are defined in the initial phase using first level of parameters (Account, Applications, User and Profiles), the Candidate Workloads Tree on the left pane is refreshed with the initial set of workloads.
Teradata WA provides the capability to continuously drill-down on a workload with various “Who”, “What”, “Where” and “Exceptions” parameters (see Table 8 on page 61 for a list of parameters). It can visualize distinct clusters of requests within the workload, each with distinct service time patterns and other characteristics.
For example, an initial workload defined on just the Account parameter may include distinct users who execute tactical requests requiring higher priority, while the remaining users do not. Teradata WA helps the DBA identify these clusters by providing reports, correlation graphs and distribution graphs (with parameters such as CPU, Response Time, Estimated Processing Time, for example) on different dimensions.
Analysis of an individual workload is initiated by selecting the Analyze Workload option on the Workload Report shortcut menu (see the table following step 1 on page 62) or by clicking Analyze under the workload to be analyzed in the Candidate Workloads Tree. To further refine the initial set of workloads into one or more additional workloads, Teradata WA uses DBQL data for workload analysis.
Theoretically, a workload can be subclassified further into multiple workloads through additional classification criterion. Subclassification on any and all possible classification criterion may be confusing and result in many unnecessary workloads. Considering the operational performance points, there is a maximum limit on the number of workloads for a database.
Teradata WA guides the DBA towards the appropriate classification criterion. At any given point in the analysis, the DBA is allowed to choose correlation and distribution parameters in the drop-down list, and analyze the associated usage patterns. The DBA could drill deeper in analysis within a chosen cluster, or re-analyze by choosing different correlation and distribution parameters. Through trial-and-error and visualization, the DBA decides which parameters identify the ideal request group to isolate the most effectively. This trial-and-error process is streamlined by providing the DBA with distinct count and distribution range insight without having to click Analyze Workload. This time saving process eliminates unproductive visualizations on a single user, or a tight distribution, for example.
The overall flow of workload analysis with deeper drill-down and refinement is represented in Figure 14.
Figure 14: Overview of deep drill-down analysis
Deep drill-down analysis is a recursive process for deeper analysis on correlation and distribution parameters. If current analysis parameters do not satisfy the DBA, more appropriate parameters can be selected by reviewing the distinct values and ranges for other parameters.
For example, with respect to the distinct value counts, one particular workload could display the following characteristics:
Note: See “Configuring Application Options” on page 29 for information on viewing distinct value counts in workloads.
In this example, the DBA now knows that there is only one distinct Application and one distinct Account, and that they both run at the same urgency. Attempting to identify a correlation against a different Application, Account or urgency values is a wasted effort. However, the opportunity for correlation does exist with User Name, Function and AggLevel. The DBA could pursue those correlation options. For the distribution parameter ranges, an Estimated Processing Time range from zero to 1000 seconds suggests that a large variation of requests are included in this workload. The opportunity for identifying clusters is higher with this range, than if the Estimated Processing Time range was just zero to one second.
The DBA may add clusters to the current workload for deeper analysis, or clusters may be split off into a new workload. The DBA may repeat this process until good set of workloads are defined, or all unassigned clusters are assigned to workloads.
Teradata WA uses an assigned and unassigned cluster concept. Each cluster (for example, Accounts, Users, QueryBands) found during analysis are initially unassigned. Selected clusters are assigned after adding clusters to the current workload for deeper analysis, or after splitting out into a new workload. The unassigned clusters remain available for subsequent action by the DBA, if wanted. Teradata WA brings back all unassigned clusters if the same analysis parameter is clicked again, after displaying an informational message.
If unassigned clusters are not used by the DBA, the associated requests are relegated to a different workload after the ruleset changes are saved. For example, consider the following set of six workloads that were generated after the first level of analysis on Accounts, where Workload A is defined for classification Account=A:
Figure 15: Workload A with classification Account =A
The DBA decides to analyze workload A, which consumes 35% of the CPU. Based on some criteria (for example, Client User), it is determined that one element should be isolated, and treated differently than the other elements. The DBA can either split the existing workload, or add classification to existing the workload.
If the DBA splits the particular element, the result is a new workload, A2, with classification Account=A and Client User = xyz. Workload A2 automatically has a higher evaluation order than the original workload A to assure client users of xyz execute within workload A2, and all other client users execute within workload A. The CPU distribution divided between the old workload (A) and new workload (A2) workload is shown in Figure 16:
Figure 16: Workload A and Workload A2 with CPU distribution division
Alternatively, if the DBA chooses to instead add classification to an existing workload, (so that the workload classification of workload A is now Account=A, and Client User = xyz), the unselected elements are designated “unassigned,” as depicted in Figure 17. If not further acted upon, the unassigned elements end up executing within WD-Default, because no other workload exists that would capture requests with classification Account=A and NOT client user = xyz.
Figure 17: Unassigned elements after classification is added to existing workload
To avoid accidental relegation of unassigned clusters to WD-Default, or some other unexpected WD, drill-down probes should begin the first analysis step using the Split Workloads option (see “Splitting and Merging Workloads for Analysis” on page 132 for more information). Additional refinements are done using the Add (selected parameter) as classification to workload option against that new workload, so that unassigned requests are relegated back to the original workload. See “Adding Existing Classifications to New Workloads” on page 79 for more information). See “Example 2: Deep Drill-Down Analysis with Queryband Parameters” on page 107 for a demonstration on this particular technique.
The DBA selects correlation parameters (“Who” and “Where”) and the distribution parameters (“What” and “Exception”) at each depth of analysis (see Table 8 on page 61 for the list of supported parameters). The DBA can also review the workload by viewing the classification list after each level of analysis and click Undo Classification (if needed). The Undo operation is used to reverse any previous analysis performed. The operation deletes assigned clusters from a workload classification and bring them back as unassigned clusters for new add/split operations.
Note: After PSA migration, when a CPU distribution pie chart is generated for 250 or more workloads, Teradata WA 15.10 displays the distribution of the top 10 workloads as determined by the percentage of CPU processing required for each workload. The remaining workloads are grouped into a segment labeled SUM of CPU % as shown in Figure 18.
Figure 18: Distribution of top 10 workloads represented as a percentage of CPU processing when more than 250 workloads are represented after PSA migration.
Example 1: Deep Drill-Down Analysis
Follow this example to learn more about deeper drill-down analysis in Teradata WA.
1 Do one of the following:
in the Workload Analyzer toolbar.
Ctrl+W.
The Define DBQL Inputs dialog box appears.
2 Complete the Define DBQL Inputs options.
For more information, see “Defining DBQL Inputs” on page 50. In the Category box, select Account String.
3 Click OK. Teradata WA queries the DBQL.
The query process may take several minutes.
The Candidate Workload Report window appears with the Unassigned requests report.
4 Select all the unassigned requests in the Unassigned requests report, and right-click to display the shortcut menu.
5 Select Add To > New Workload.
The Add Workload dialog box appears. For this example, the workload is named WD_ABC. The new workload displays in the Candidate Workloads Report.
At this point, the CPU distribution of all workloads (1) is 100 percent.
6 Right-click over WD-ABC for cluster analysis, and select Analyze Workload.
The workload is analyzed and the Analyze Workload window appears.
The Current Workload Classification list displays a summarization of the classification. The numbers adjacent to the current classification criteria type show the total number of elements for correlation, and minimum and maximum values for distribution classification parameters. For example:
7 Click View Classification to view detailed classification of the workload.
8 Click View Exception to view the exception definitions.
9 From the Analyze Workload page, select the appropriate correlations from the Workload Correlation Parameter list and the Workload Distribution Parameter list.
Note that there are 10 Applications found for the WD-ABC workload, making this a good candidate to analyze deeper.
10 Click Perform Analysis.
The Correlation Report and graph display.
Note: The workload WD-ABC must be split first from the original workload before selecting ‘TWA application for deeper analysis.’)
Notice in the Correlation Report that the Application ‘TWA’ has a significantly higher CPU, IO, and Avg Estimated ProcTime values than other Applications in the report. TWA is selected for deeper analysis.
11 Right-click over TWA, from the shortcut menu, select Add Application clusters for deeper analysis.
The Correlation Report and graph update with the remaining unassigned clusters, while Application ‘TWA’ is assigned to the current workload for deeper analysis.
12 Click the Data Filters tab at the bottom of the page to view the classification for the current workload.
The Data Filters page displays.
The CPU distribution of all workloads now breaks down to 94% for WD-ABC, and 6% Unassigned.
Note: If unassigned clusters are not added to current or new split workloads before saving the ruleset to the database, then all queries arrived for unassigned clusters are executed as part of the default workload (WD-Default).
If the Application parameter is selected again, all nine unassigned clusters are brought back for subsequent operations (one cluster is assigned to the current workload).
13 For this example, right-click over the remaining nine clusters in the Correlation Report.
14 Select the Split to New Workload option or the Add clusters for deeper analysis option.
For the purpose of this example, Split to New Workload is selected and the new workload is named WD-Others.
The CPU distribution is now 7% for WD_Others. There are no unassigned requests.
15 Save the rule set to the database for activation.
See “Step 5: Creating the Workloads” on page 92 for detailed instructions.
Example 2: Deep Drill-Down Analysis with Queryband Parameters
This example describes deep drill-down analysis of several query band parameters to help identify and isolate various request clusters, or provide additional granularity on request clusters.
In this example, one initial workload consumes the majority of the resources. A more granular breakdown of that workload is investigated. Long running outliers are noted in the analysis. The goal is to have these outliers classified into their own workload so that different workload management techniques are applied.
1 Select Analysis>New Workload Recommendations.
The Define DBQL Inputs dialog box appears.
2 Complete the Define DBQL Inputs options.
For more information, see “Defining DBQL Inputs” on page 50.
3 In the Category box, select Account String, and click OK.
The Unassigned requests report displays.
4 Right-click over each workload and select Auto-Generate Workloads.
The Candidate Workloads Report displays.
The CPU distribution of all workloads is 8% ADW-TACT and 92% ADW-DS. Workload WD-ADW-DS is selected for further analysis because it is consuming 92% of the total CPU.
5 Right-click over WD-ADW-DS, and select Analyze Workload.
The Analyze Workload page appears.
There are a total of five distinct query band names. The query bands can be viewed only if Queryband is selected from the Workload Correlation Parameter list. The five distinct query band names in this example are:
There are several suitable analysis candidates available in this query band list, as denoted by the distinct value counts.
6 For this example, select QueryBand as the correlation parameter.
7 Click to load the query band names in the Queryband Filter drop-down list.
8 Select Function, then click Perform Analysis.
The Correlation Report and graph display.
A total of five query band values display for the Queryband=Name function. Notice a possible distinction with Function=MIN, which included queries with greater time spent than any of the other queries.
9 From the report, right-click over the MIN row, and select Split to New Workload.
10 Name the new workload WD_ADW_Outliers.
This step is done to insure that unassigned clusters fall back into the original WD-ADW-DS workload classification.
The QueryBand Function=MIN is split to the new workload, ADW-Outliers. The remaining four functions are unassigned, falling back to ADW-DS if no other action is taken on them.
The Correlation/Distribution Reports and graph refresh with the remaining four unassigned query band values for the next add or split operation.
11 From the report, right-click over WD_ADW_Outliers and select Analyze Workload to perform further drill-down analysis.
The Analyze Workload page displays.
12 Select TopTierApp from the Queryband Filter list, and click Perform Analysis.
The report and graph display.
The longest running queries not only common to Function=MIN, but also QueryBand Name TopTierApp=BODSS.
13 From the report, right-click over the BODSS row, and select Add ‘QueryBand’ clusters for deeper Analysis.
The BODSS value is added to the current analyzed workload, WD_ADW_Outliers.
The CPU distribution now has two unassigned TopTierApps relegated to the original WD_ADW_DS workload, rather than being part of the WD_ADW_Outliers workload.
Analyzing further on ADW_Outliers, notice that the distinct count for all the correlation parameters displays one (only one distinct value).
This means that all requests in this workload are coming from the same combination of ‘Who’ parameters (Account ADW_DS, QueryBand Name Function=MIN, and Queryband Name TopTier App=BODSS).
Only distribution parameters may be used deeper drill-down analysis. The Estimated Processing Time may be used as a distribution parameter, since the range for the current workload is wide (0.00 -0158.00 seconds).
14 From the Analyze Workload page, select None from the Workload Correlation Parameter list.
15 Select Estimated Processing Time from the Workload Distribution Parameter list.
16 Click Perform Analysis.
The report and graph display.
The eight queries lie in the last bucket (bucket 10 with a range of 142.20 - 158.00) are long queries. However, another eight queries in the first bucket (bucket 1 with a range of 0.00-15.80) are short running queries, with a wide gap show in bucket 2 through bucket 9.
The goal from the start of this process is to isolate long running requests found within the ADW-DS workload, and apply different workload management to them. By adding the last bucket to the ADW_Outliers workload, the necessary workload definition is achieved to apply those different workload management techniques.
17 Right-click over Bucket 10, and select Add Estimated Processing Time clusters for deeper analysis.
18 Override the Min Estimated Processing Time to 30 seconds, and override the Max Estimated Processing Time to 999999 seconds (basically unlimited).
The very short running requests relegated back to the original WD-ADW-DS workload.
In summary, the workload classifications within this example result in WD-ADW-DS remain in its original state. To view, click Attributes under the WD-ADW-DS workload in the Candidate Workloads Tree, then click on the Classification tab.
The workload WD-ADW-Outliers is created with a higher evaluation order than workload WD-ADW-DS. To view, click Attributes for WD-ADW-Outliers, then click the Classification tab.
For more information about evaluation order, see “Viewing Evaluation Order of Candidate Workloads” on page 168.
Example 3: Workload Analysis for Utility Management
This example describes cluster analyses on utility types for workload definition classifications.
1 Select Analysis>New Workload Recommendations.
The Define DBQL Inputs dialog box appears.
2 Complete the Define DBQL Inputs options.
For more information, see “Defining DBQL Inputs” on page 50.
3 In the Category box, select Users, and click OK.
The Unassigned requests report displays.
4 Right-click over each workload and select Auto-Generate Workloads.
The Candidate Workload Report displays.
5 Right-click over WD-PriorityUsers, and select Analyze.
The Analyze Workload window appears.
The numbers adjacent to each option in the Workload Correlation Parameter and Workload Distribution Parameter lists of the Refine Workload Classification pane show the total numbers of elements used for correlation. (Utility types for distribution classification parameters include min and max values.)
For example, Utility Type (5) indicates that there are five utility types available in DBQL for analysis.
Cluster analysis on utility types collects all utilities that ran during the DBQL data collection time interval.
The results of many queries executed during the query session are grouped under “ALL OTHERS” clusters in the Analyze Workload tab for reporting purposes. (Workload definitions, however, cannot be created from “ALL OTHERS” clusters.)
All non-Teradata Database utilities are grouped in the “Non-TD utilities” cluster. For workload analysis of non-Teradata Database utilities:
For workload analysis using Teradata Database utilities:
If the DBA selects utility types StandaloneFastLoad, StandaloneMultiLoad and StandaloneFastExport clusters to split into new workloads, then OR bit operations for their bitmap values are saved in the TDWM database for classification as follows:
Classify Criteria WD_LoadUtilities: FastLoad OR MultiLoad OR FastExport = 0x000002 | 0x000004 | 0x000008
6 Click Attributes under WD-PriorityUsers on the workload tree.
7 From the Classification tab, select Load Utility Type from the Criteria #2 drop-down list.
8 Click Choose from the AND pane.
The Classify By Utility Types dialog box appears showing the types of utilities to be used for classification.
9 Click OK to continue.
The Analyze Workload tab refreshes, displaying the results of the query.
The DBA can generate distribution analysis reports based on WHAT parameters and then drill down the distribution report using the Zoom-in/Zoom-out features. The DBA cannot, however, create workload definitions from distribution analysis reports. The query estimate criteria (WHAT) cannot be used for utility type workload classifications.
Exclusion criteria is not allowed for workload definitions created using utility type classifications, and the Exclusion page is read-only for all workloads defined using utility types. Workloads created from utility types can be drilled down using WHO and WHERE (database, table, and view only) parameters, and the result can be split into new workloads. The following additional classification criteria can be used for utility type workloads: accounts, user IDs, client addresses, client IDs, query bands and database objects, including databases, tables, views, and so on.