Example 3: Workload Analysis for Utility Management - Teradata Workload Analyzer

Teradata Workload Analyzer User Guide

Product
Teradata Workload Analyzer
Release Number
17.00
Published
September 2019
Language
English (United States)
Restricted Access
TTU-17.00-EAP
Last Update
2019-06-07
dita:mapPath
vjd1544831946946.ditamap
dita:ditavalPath
ft:empty
dita:id
B035-2514
Product Category
Teradata Tools and Utilities
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. (See Defining DBQL Inputs.)
  3. In the Category box, select Users, and click OK. The Unassigned requests report appears.
  4. Right-click each workload and select Auto-Generate Workloads. The Candidate Workload Report appears.
  5. Right-click 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-Vantage utilities are grouped in the NON TD UTILITY cluster. For workload analysis of non-Vantage utilities, the following is true:
    • A DBA can create a workload definition from the NON TD UTILITY cluster or combine this cluster with other Vantage utilities.
    • Workload definition classification for Non-TD utilities should be one of the following:
      • Non-TD FastLoad
      • Non-TD Multiload
      • Non-TD FastExport = 0x200000 | 0x400000 | 0x800000
    For workload analysis using Teradata Database utilities, the following is true:
    • A DBA can create a workload definition by selecting one or more utilities from a correlation report.
    • The following bitmap values represent the supported utility types for Release 16.20:
      • 0x000002 = Include if standalone FastLoad
      • 0x000004 = Include if standalone Multiload
      • 0x000008 = Include if standalone FastExport
      • 0x000010 = Include if ARC
      • 0x000020 = Include if TPT FastLoad (load operator)
      • 0x000040 = Include if TPT Multiload (update operator)
      • 0x000080 = Include if TPT FastExport (export operator)
      • 0x000100 = Include if CSP Save Dump FastLoad
      • 0x000200 = Include if JDBC FastLoad
      • 0x000400 = Include if JDBC Multiload
      • 0x000800 = Include if JDBC FastExport
      • 0x020000 = Include if generic 3rd party FastLoad
      • 0x040000 = Include if generic 3rd party Multiload
      • 0x080000 = Include if generic 3rd party FastExport
      • 0x200000 = Include if non-TD FastLoad
      • 0x400000 = Include if non-TD Multiload
      • 0x800000 = Include if non-TD FastExport
      • 0x1000000 = Include if BAR


    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.