Defining Exception Criteria - Teradata Workload Analyzer

Teradata Workload Analyzer User Guide

Product
Teradata Workload Analyzer
Release Number
16.20
Published
October 2018
Language
English (United States)
Last Update
2018-10-12
dita:mapPath
sef1527114222310.ditamap
dita:ditavalPath
Audience_PDF_include.ditaval
dita:id
B035-2514
lifecycle
previous
Product Category
Teradata Tools and Utilities
  1. Select the exception directive you want to define in the Exceptions tab.
  2. Fill in the fields/controls as follows.
    Field/Control Action/Comment
    Maximum Rows
    1. Select the control.
    2. Enter the per step maximum rows in a spool file.
    IO Count
    1. Select the control.
    2. Enter the maximum number of disk I/Os performed on behalf of the query.
    Spool Size
    1. Select the control.
    2. Enter the maximum size of a spool file (per step).
    3. Choose whether the size is in:
      • Bytes
      • Thousand Bytes
      • Million Bytes
      • Billion Bytes
    Number of Amps
    1. Select the control.
    2. Enter the number of AMPs that participate in the query.
    Blocked Time
    1. Select the control.
    2. Enter the length of time the query is blocked by another query..
    Elapsed Time
    1. Select the control.
    2. Enter the length of time the query has been running (that is, response time).

      This time is stored in the Teradata Database as centiseconds.

    Sum Over All Nodes
    1. Select the control.
    2. Enter the total amount of CPU time consumed by the query over all nodes.
    Tactical CPU Usage Threshold (per node)
    1. Select the control.
    2. Enter a positive value.

      Note the following:

      • Specify a value less than the value specified for Sum Over All Nodes.
      • Specify a value less than 3 seconds to optimize performance.
      • Specify Change Workload and another WD mapped to an AG in the same RP as one of the exception actions.

      For more information on setting this control, see Teradata Viewpoint User Guide (B035-2206).

    Note the following:
    • This parameter is enabled only if the WD is Tactical in the Workload Attributes tab and there is a positive value for CPU Sum Over All Nodes.
    • All exceptions in the same operating environment with Tactical CPU Usage Threshold (per node) must use the same value and have the same WD in a Change Workload exception action.
    Qualification CPU Time
    1. Select the control.

      The Qualification Time box becomes active when a skew control is checked.

    2. Enter the length of time the following exception conditions must persist before the following criteria are satisfied (in CPU seconds):
      • CPU millisec per IO
      • IO Skew
      • CPU Skew
      • IO Skew Percent
      • CPU Skew Percent

    You must select one of these criteria to activate Qualification Time. Note that if you do not enter a Qualification Time value, Teradata WA uses the Exception Interval.

    Qualification Time must be an integer multiple of the global Exception Interval and greater than zero. For more information on these options, see Teradata Viewpoint User Guide (B035-2206).

    IO Skew

    CPU Skew

    1. Select the control.
    2. Enter a value:
      • IO Skew – Maximum difference in disk I/O counts between the busiest AMP and the average of all involved AMPs during the last exception interval.
      • CPU Skew – Maximum difference in CPU consumption between the busiest AMP and the average of all involved AMPs during the last exception interval.

        A value of zero means there is no skew. A value greater than zero indicates skew that accumulates to a larger and larger value as long as the skew continues, up until the skew exceeds the Qualification Time.

    For more information on these options, see Teradata Viewpoint User Guide (B035-2206).

    Note the following:
    • The skew must exceed the Qualification Time before Teradata WA performs the action you specify.
    • The skew must persist for a specifiable length of time, in CPU seconds, that is greater than one global Exception Interval, to qualify as an exception.
    CPU millisec per IO
    1. Select the control.
    2. Enter the maximum ratio of CPU consumption to disk I/O during the last exception interval.

      You can use this control to detect queries that have an unusually high ratio of CPU processing relative to logical I/Os incurred (for example, an accidental unconstrained product join performed on a very large table). Because of their very high CPU usage, these queries can steal CPU resources from other higher priority workloads, impacting the ability of the Priority Scheduler to favor higher priority requests.

      It is recommended that you initially set this control to 5 or greater.

    You must select this control to activate Qualification Time.

    Note the following:
    • The ratio must exceed the Qualification Time before Teradata WA performs the action you specify.
    • The ratio must persist for a specifiable length of time, in CPU seconds, that is greater than one global Exception Interval, to qualify as an exception.
    IO Skew Percent

    CPU Skew Percent

    1. Select the control.
    2. Enter a value:
      • IO Skew Percent – Maximum percentage difference in disk I/O counts between the busiest AMP and the average of all involved AMPs during the last exception interval.
      • CPU Skew Percent – Maximum percentage difference in CPU consumption between the busiest AMP and the average of all involved AMPs during the last exception interval.

        A value of 0% means there is no skew. A value greater than 0% indicates skew. The larger the percentage, the worse the skew is. The impact of that skew grows exponentially.

    The skew must exceed the Qualification Time before Teradata WA performs the action you specify.

    For more information about these options, see Teradata Viewpoint User Guide (B035-2206).

  3. Select Accept to accept your changes.
  4. Next, specify the exception actions Teradata WA performs when the exception criteria are exceeded.