Using Deep Drill-Down and Refinement for Workload Analysis - Teradata Workload Analyzer

Teradata Workload Analyzer User Guide

Teradata Workload Analyzer
Release Number
English (United States)
Last Update
Product Category
Teradata Tools and Utilities

Using Deep Drill-Down and Refinement for Workload Analysis

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 49.

This section includes the following:

  • Overview of Deep Drill-Down and Refinement
  • How Does Deep Drill-Down Analysis Work?
  • Example 1: Deep Drill-Down Analysis
  • Example 2: Deep Drill-Down Analysis with Queryband Parameters
  • 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 3 on page 49 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 50) 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 9.

    Figure 9: Overview of deep drill-down analysis

    How Does Deep Drill-Down Analysis Work?

    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:

  • UserName (24)
  • Applications (1)
  • Account Name (1)
  • Client Addresses (2)
  • Queryband (3)
  • Function (3)
  • Urgency (1)
  • AggLevel (8)
  • Estimated Processing Time (zero to 1000 seconds)
  • AMP Count (zero to 1)
  • Note: See “Configuring Application Options” on page 15 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 10: 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 11:

    Figure 11: 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 12. 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 12: 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 121 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 67 for more information). See “Example 2: Deep Drill-Down Analysis with Queryband Parameters” on page 95 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 3 on page 49 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.00 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 13.

    Figure 13: 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.

    Example of deep drill-down analysis

    1 Do one of the following:

  • Click in the Workload Analyzer toolbar.
  • Select Analysis > New Workload Recommendations.
  • Press 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 38. 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:

  • Account String (2) - There are two Accounts classified
  • Estimated Processing Time (0 - 200 secs) - The workload is classified for Estimated Processing Time between zero to 200 seconds)
  • 7 Click View Classification to view detailed classification of the workload. Click View Exception to view the exception definitions.

    8 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.

    9 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.

    10 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.

    11 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).

    12 For this example, right-click over the remaining nine clusters in the Correlation Report.

    13 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.

    14 Save the rule set to the database for activation. See “Step 5: Creating the Workloads” on page 80 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.

    Example of deep drill-down analysis and query band parameters

    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 38.

    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:

  • QueryBand (5)
  • AggLevel (7)
  • Function (5)
  • Region (7)
  • TopTierApp (3)
  • Urgency (3)
  • 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. Click to load the query band names in the Queryband Filter drop-down list.

    7 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.

    8 From the report, right-click over the MIN row, and select Split to New Workload. 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.

    9 From the report, right-click over WD_ADW_Outliers and select Analyze Workload to perform further drill-down analysis. The Analyze Workload page displays.

    10 Select TopTierApp from the Queryband Filter list. Click Perform Analysis. The report and graph display.

    The longest running queries not only common to Function=MIN, but also QueryBand Name TopTierApp=BODSS.

    11 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).

    12 From the Analyze Workload page, select None from the Workload Correlation Parameter list. Select Estimated Processing Time from the Workload Distribution Parameter list.

    13 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.

    14 Right-click over Bucket 10, and select Add Estimated Processing Time clusters for deeper analysis. 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 159.

    Example 3: Workload Analysis for Utility Management

    This example describes cluster analyses on utility types for workload definition classifications.

    Example of workload analysis for utility management

    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 38.

    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.