15.10 - Analyzing Workloads Based On “What” Parameters - Teradata Workload Analyzer

Teradata Workload Analyzer User Guide

prodname
Teradata Workload Analyzer
vrm_release
15.10
category
User Guide
featnum
B035-2514-035K

Teradata uses equal-width and equal-height histograms in analyzing what parameters.

Equal-Width Versus Equal-Height Histograms

A histogram is a vertical bar chart in which the frequency corresponding to a class is represented by the area of a bar (or rectangle) whose base is the class width. The histogram differs from a bar chart in that it is the area of the bar that denotes the value, not the height. However, if the widths of the bars are uniform (that is, equal-width) then only the height need be considered.

Teradata WA uses both equal-width and equal-height histograms in analyzing the what parameter.

An equal-width histogram such as that shown in Figure 10, divides data into a fixed number of equal-width ranges. The corresponding height of each range represents the number of values falling into that range.

Figure 10: Equal-widths histogram

For example, suppose that the values in a single column of a 1000-row table range between 1 and 100, and you want to generate a 10-bucket equal-width histogram. (Ranges in histograms are often referred to as “buckets.”) The buckets would contain the values 1-10, 11-20, 21-30, and so on, where each bucket counts the number of rows falling into its range. For a list of supported analysis parameters, see Table 8 on page 61.

Equal-width histograms work well when the variation of the data distribution is small. They do not work so well, however, when such variation is large. For example, in Figure 10, 95% of the data falls into the first bucket, and the remaining 1% is scattered into 19 buckets, making it difficult to effectively analyze the data.

In this situation, an equal-height histogram is the solution, such as that shown in Figure 11. Such histograms work well when the variation in data distribution is large. Unlike equal-width histograms, they place the same number of values into each range, so the endpoints of each range are determined by the number of values it contains.

Figure 11: Equal-height histogram

The graph isn't as informative as the bin-widths identified on its x-axis. They reveal that not only are the vast majority of data points in the 0-8.41 range, but provide additional insight that the vast majority of queries (80%) consume less than 0.01 CPU secs.

This information is provided in a pop-up dialog box for each equal-height histogram. For instructions on viewing histograms and their data, see step 6 on page 74

Displaying a “What” Parameter Histogram

1 From the Candidate Workloads tree, expand the workload to be analyzed.

2 Select Analyze Workload.

The Analyze Workload window appears.

3 Select the appropriate “what” options in the Workload Distribution Parameters list.

Note: See the table following step 3 in “To analyze a workload based on “who” parameters” on page 62 for more information about Analyze Workload window options.

4 Click Perform Analysis to analyze the selected workload with chosen options.

By default, the Analyze Workload window creates an equal-width balanced histogram, dividing the total selected distribution parameter into the number of buckets specified in the Top N Value field. Each bucket contains equal amounts of the selected distribution parameter and the total query count lies in that bucket.

5 To view the corresponding equal-height histogram, click the Switch to Equal-Height Histogram button on the Analyze Workload tab.

You can also right-click the histogram itself and select Equal-Height Histogram from the menu that appears.

6 To view the Equal-Height Distribution report associated with the histogram, select View Equal-Height Table.

The table appears similar to the following.

Creating a New Workload From a Particular Bucket In the Histogram

For this procedure, Estimated Processing Time is used as an example.

1 From the Estimated Processing Time histogram, highlight the bucket to be analyzed, then right-click.

The shortcut menu appears. See “To add an existing classification to a new workload” on page 79 and see step 2 for more information.

2 Select the Split to New Workload option.

The Estimated Processing Time dialog box appears.

3 Enter the minimum and maximum processing time bucket parameters.

4 Click OK.

The Add Workload dialog box appears.

5 Enter the workload attribute details. See the table after step 4 of “To group unassigned requests into a workload” on page 56 for more information.

6 Click OK.

A new workload is created with Minimum and Maximum Estimated Processing Time along with the first level of classifications. The selected row/bucket is removed from the Analyze Workload report.

Zooming In On a Histogram Bucket For More Detail

For this procedure, Estimated Processing Time is used as an example. Zooming in on a bucket creates another Top N Values within the bucket, presenting a finer view of the distribution.

1 From the Estimated Processing Time histogram, right-click the bucket to zoom in on.

2 Select Zoom In from the short-cut menu.

The Zoom In dialog box appears.

3 Enter the minimum, maximum processing time, and the number of bucket parameters, and Click OK.

After processing, the bucket is displayed as a new histogram.

4 To reset the zoom to the previous level, right-click and select the Zoom Out option.

Rounding Up Equal-Width Buckets for More Intuitive Number Recognition

The widths of equal-width buckets are calculated by dividing the highest value by the number of buckets. While technically accurate, this method may not be intuitive to the user. Rounding up the calculation of the widths used in equal-width buckets may improve the usability of the buckets while making a small sacrifice in their accuracy.

The rounding up of bucket widths is based on calculating the maximum value and then rounding it up so that the last bucket covers the maximum value. For example, if you have a large range of numbers with the highest value = 3876.77, and want to split the values into 10 buckets, then the bucket size is calculated as 3876.77 / 10 = 387.677. If this is rounded up to 400, then the revised buckets would have the following values: 0- 400, 401-800, 801-1200, ..., 3201-3600, and 3601-4000. These new buckets would then be reflected in the distribution report shown in Figure 12.

Figure 12: Distribution report after rounding up the widths of buckets for data with a large range of values

For data with a small range of values where the highest value = 3.15, split into 10 buckets, bucket width is calculated as 3.15 / 10 = 0.31, rounded to 0.40. The revised buckets are then split into these values: 0-0.40, 0.41-0.60, 0.61-1.0, ..., 3.21-3.6, and 3.61 - 4.00 as reflected in the distribution report in Figure 13.

Figure 13: Distribution report after rounding up the widths of buckets for data with a small range of values

Displaying Columns in the “what” classification of the Analyze Workload Report

  • Select Distribution Parameter.
  • Table 10 describes the displayed columns in the “what” classification Analyze Workload report.

     

    Table 10: Columns in the Analyze Workload report - “what” classification 

    Column Name

    Description

    Estimated Processing Time

    Min, Max, Query Count

    The minimum and maximum estimated processing time and the number of queries that completed during this collection interval for this bucket

    Percent of Total CPU

    Percentage of the total CPU time (in seconds) used on all AMPs for this bucket

    Percent of Total I/O

    Percentage of the total number of logical input/output (reads and writes) issued across all AMPs for this bucket

    Average Est Processing Time

    The average estimated processing time for each query

    Normalized CPU per Query (Seconds)

    Min, Avg, StDev, 95th Percentile, Max

    The minimum, average, maximum, standard deviation, 95th percentile and maximum expected CPU time for queries in this bucket.

    UnNormalized CPU per Query (Seconds)

    Min, Avg, StDev, 95th Percentile, Max

    The minimum, average, maximum, standard deviation, 95th percentile and maximum expected UnNormalized CPU time for queries in this bucket.

    Response Time (Seconds)

    Min, Avg, StDev, Max

    The minimum, average, standard deviation, and maximum response time for queries in this bucket

    Result Row Count

    Min, Avg, StDev, Max

    The minimum, average, standard deviation, and maximum result rows returned for this bucket

    Disk I/O Per Query

    Min, Avg, StDev, Max

    The minimum, average, standard deviation, and maximum disk I/O’s per query for this bucket

    CPU To Disk Ratio

    Min, Avg, StDev, Max

    The minimum, average, standard deviation, and maximum CPU/Disk ratio for this bucket

    Active AMPS

    Min, Avg, StDev, Max

    The minimum, average, standard deviation, and maximum number of active AMPs for this bucket

    Spool Usage (Bytes)

    Min, Avg, StDev, Max

    The minimum, average, standard deviation, and maximum spool usage across all VProcs for this bucket

    CPU Skew (Percent)

    Min, Avg, StDev, Max

    The minimum, average, standard deviation, and maximum AMP CPU skew for this bucket

    I/O Skew (Percent)

    Min, Avg, StDev, Max

    The minimum, average, standard deviation, and maximum of AMP I/O skew for this bucket

    Adding Existing Classifications to New Workloads

    1 From the Analyze Workload window displaying the previously analyzed workload, click the Distribution Parameter option in the View By box. The Distribution Report displays with the selected distribution parameter by correlation parameter.

    2 Highlight the bucket to be analyzed, then right-click.

    The shortcut menu appears. See the following table for descriptions.

     

    Option

    Description

    Split to New Workload with (selected Distribution Parameter) as Classification

    Select this option to add a new workload with the selected distribution parameter as classification.

    This shortcut menu option displays when Result Rows, AMP Count, or Estimated Processing Time is selected from the Distribution Parameter list on the Analyze Workload tab.

    Add (selected Distribution Parameter) as classification to workload

    Select this option to add the selected distribution parameter as an additional criterion to the workload.

    This shortcut menu option displays when Result Rows, AMP Count or Estimated Processing Time is selected from the Distribution Parameter list on the Analyze Workload tab.

    Zoom In

    Select this option to change the granularity of the graph view.

    Zoom Out

    Select this option to change the granularity of the graph view.

    Save Report As

    Saves the workloads report to a file (in either .xml, txt, or html formats).

    3 Select Split to New Workload with (selected Distribution Parameter) as Classification. The Add Workload dialog box appears. See table to complete the options.

    4 Click OK. A new workload is created from the selected workload. The new workload contains the parent workload’s classifications, along with the new criteria.

    Analyzing Workloads Based on Exceptions

    Teradata WA supports the following exception parameters:

  • Unnormalized CPU Time
  • Normalized CPU Time
  • IO Count
  • CPU Time
  • Spool Size
  • Response Time
  • 1 In the Candidate Workload Report window, right-click over the workload to be analyzed. The Workloads Report shortcut menu appears.

    2 Select Analyze Workload. The Analyze Workload window appears.

    Note: Alternatively, expand the node next to the workload to be analyzed under the Candidate Workloads tree, and then select Analyze.

    3 Click on the Distribution Parameter drop-down, and select the exceptions and actions.

    4 Enter the number of Distribution Buckets. The default number of buckets is 10.

    5 Click Perform Analysis to analyze the selected workload with the chosen exception parameters.

    A same-width histogram displays for the selected workload and correlation parameter.

    1 From the Analyze Workload window with the previously analyzed workload with exceptions, click the Distribution Parameter option in the View By box.

    2 Right-click over the bucket row to analyzed. The Workloads Report shortcut menu for the Distribution Parameter view appears. See the following table for descriptions.

     

    Option

    Description

    Split to New Workload with (selected Distribution Parameter) as Exception

    Select this option to add a new workload with the selected distribution parameter as the Exception.

    This shortcut menu option displays when CPU Time, Response Time, IO Count, or Spool Usage (bytes) is selected from the Distribution Parameter list on the Analyze Workloads tab.

    Add (selected Distribution Parameter) as Exception to workload

    Select this option to add the selected distribution parameter as a user-defined exception criterion for the highlighted workload.

    This shortcut menu option displays when CPU Time, Response Time, IO Count, or Spool Usage (bytes) is selected from the Distribution Parameter list on the Analyze Workloads tab.

    Zoom In

    Select this option to change the granularity of the graph view.

    Zoom Out

    Select this option to change the granularity of the graph view.

    Save Report As

    Saves the workloads report to a file (in either .xml, txt, or html formats).

    3 Select Split to New Workload with (selected Distribution Parameter) as Exception. The Add New workload With Exception dialog box displays.

    4 Select the options as described in the following table:

     

    Option

    Definition

    Workload Name

    Name of the workload.

    Preview Classification

    Click to view the classification of the workload to be created. A read-only Classification window displays with the classification criterion of the workload.

    Workload Description

    A description of the workload.

    Exception Type

    Displays the Exception parameter to be used for the exception.

    Exception Name

    Name of the exception parameter.

    WD Enforcement Priority

    Tactical – short queries with a fixed response-time requirement.

    Priority – important queries that should get extra resources.

    Normal – normal queries; this is the default.

    Background – low-priority queries with no response time requirement.

    Exception Actions

    No Exception Monitoring – Only log the exception.

    Continue and Log – Logs the exception and choose another action.

    Abort and Log – Log the exception and abort the request.

    Abort On Select and Log - Log the exception and abort the request only if it was a select that did not modify tables.

    Change Workload – Log the exception and move the request to the specified workload.

    Raise Alert – Log the exception and raise the specified alert through Teradata Viewpoint Alert Setup. For more information, see Teradata Viewpoint User Guide (B035‑2206).

    Note: Teradata Viewpoint Data Collection Service (DCS) monitors database tables and sends alerts to Teradata Viewpoint Alert Viewer for processing. Teradata Viewpoint Alert Viewer allows users to view and manage alerts.

    Run Program – Log the exception and run the specified program using Viewpoint Workload Monitor.

    For more information, see the Teradata Viewpoint User Guide (B035‑2206).

    5 Click OK. The newly created workload with exception appears in the Candidate Workloads tree.

    1 From the Analyze Workload window with the previously analyzed workload with exceptions, click the Distribution Parameter option in the View By box.

    2 Right-click over the bucket row to analyzed.

    The Workloads Report shortcut menu for the Distribution Parameter view appears.

    3 Select Add (selected Distribution Parameter) as Exception to workload.

    The Add Exception to Existing WD appears.

    4 Select the options as described in the following table:

     

    Option

    Description

    Exception Name

    Name of the exception parameter

    Exception Type

    Displays the Exception parameter to be used for the exception.

    Exception Max Value

    The maximum value of the selected exception parameter.

    Exception Actions

    No Exception Monitoring – Only log the exception.

    Continue and Log – Logs the exception and choose another action.

    Abort and Log – Log the exception and abort the request.

    Abort On Select and Log - Log the exception and abort the request only if it was a select that did not modify tables.

    Change Workload – Log the exception and move the request to the specified workload.

    Raise Alert – Log the exception and raise the specified alert through Teradata Viewpoint Alert Setup.

    Note: Teradata Viewpoint Data Collection Service (DCS) monitors database tables and sends alerts to Teradata Viewpoint Alert Viewer for processing. Teradata Viewpoint Alert Viewer allows users to view and manage alerts.

    Run Program – Log the exception and run the specified program using Viewpoint Workload Monitor.

    For more information, see the Teradata Viewpoint User Guide (B035‑2206).

    5 Click OK. A message appears confirming the addition of the exception to the selected bucket row.

    6 Click OK. The message dialog box closes, and the selected bucket row for the exception is removed from the Distribution Report.