16.20 - MonitorWD - Teradata Vantage NewSQL Engine

Teradata Vantage™ Application Programming Reference

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
created_date
March 2019
category
Programming Reference
featnum
B035-1090-162K

Purpose

Returns the data in the second statement of the MONITOR WD response through an SQL interface consisting of UDFs.

Definition

REPLACE FUNCTION SYSLIB.MonitorWD()
     RETURNS TABLE
     (WDId                   INTEGER,
      PPId                   SMALLINT,
      PGId                   SMALLINT,
      AGId                   SMALLINT,
      RelWgt                 SMALLINT,
      NumProcs               INTEGER,
      VprType                VARCHAR(4) CHARACTER SET LATIN,
      QWaitTime              FLOAT,
      QWaitTimeMax           FLOAT,
      CpuUserPct             FLOAT,
      WorkMsgMaxDelay        FLOAT,
      WorkTypeInuseMax       INTEGER,
      WorkTimeInuseAvg       FLOAT,
      IODelay                FLOAT,
      IODelayTime            FLOAT,
      PhysicalRead           FLOAT,
      PhysicalReadKB         FLOAT,
      PhysicalWrite          FLOAT,
      PhysicalWriteKB        FLOAT,
      LogicalRead            FLOAT,
      LogicalReadKB          FLOAT,
      LogicalWrite           FLOAT,
      LogicalWriteKB         FLOAT,
      ExtraField1            FLOAT,
      ExtraField2            FLOAT,
      ExtraField3            FLOAT,
      ExtraField4            FLOAT
      VPId                   SMALLINT,
      VPId                   SMALLINT,
      WaitIO                 FLOAT,
      WaitOther              FLOAT,
      CPURunDelay            FLOAT,
      IOCntSubmitted         FLOAT,
      IOKBSubmitted          FLOAT,
      IOCntCompleted         FLOAT,
      IOKBCompleted          FLOAT,
      IOCntCriticalSubmitted FLOAT,
      IOKBCriticalSubmitted  FLOAT,
      DecayLevel1IO          FLOAT,
      DecayLevel2IO          FLOAT,
      DecayLevel1CPU         FLOAT,
      DecayLevel2CPU         FLOAT,
      TacticalExceptionIO    FLOAT,
      TacticalExceptionCPU   FLOAT
    )
                    .
                    .
                    .
;

Usage Notes - MonitorWD

The MonitorWD function provides similar functionality to the PMPC MONITOR WD request. For information about this interface, see MONITOR WD.

For information on the resource usage tables and columns described in the field calculations below, see Teradata Vantage™ Resource Usage Macros and Tables, B035-1099.

Result Rows

Field/Column Name Description
PPId This field is obsolete and returns a value of zero.
PGId This field returns the pWDid value.
VprType Type of vproc:
  • AMP
  • PE
  • MISC
WDId WD ID. On SLES 11 or later systems, TASM Workloads rule is always enabled. On SUSE Linux Enterprise Server 11 or later systems, TASM Workloads rule is always enabled. For information on TASM rules, see Teradata® Viewpoint User Guide, B035-2206.
AGId This field is obsolete and returns a value of zero.
RelWgt This field is obsolete and returns a value of zero.
NumProcs Average number of tasks of online nodes.

The field is the result of:

NumProcs = SUM of (NumTasks-i) / N

where:
  • NumTasks-i is the number of tasks assigned to the WD at the end of the reporting period.
  • i varies from 1 to N, where N is the number of online nodes.
The NumProcs field is the NumTasks field in the PM/API MONITOR WD request.
QWaitTime Total wait time in milliseconds that work requests waited on an input queue before being serviced
QWaitTimeMax Maximum time in milliseconds that work requests waited on an input queue before being serviced.

The field is the result of:

QWaitTimeMax = MAX (QWaitTimeMax-i)

where:
  • QWaitTimeMax -i is QWaitTimeMax in each online node.
  • i varies from 1 to N, where N is the number of online nodes.
CPUUserPct Weighted average of CpuUserPct of each node.

This field is the result of:

CpuUserPct = Sum of (CpuUserPct-i* ScalingFactor-i) / Sum of (ScalingFactor-i)

where:
  • CpuUserPct-i is calculated as:

    (CPUUServAwt + CPUUServDisp + CPUUServMisc + CPUUExecAwt + CPUUExecDisp + CPUUExecMisc) * 100 /(NCPUs*Centisecs*10)

    NCPUs is the number of CPUs in the node.

  • i varies from 1 to N, where N is the number of online nodes.
  • ScalingFactor-i is the node CPU normalization factor in each node.
The CPU times are in milliseconds.

The Parser CPU times are included in the Dispatcher CPU times.

WorkMsgMaxDelay General indicator only. This field is result of the following calculation:

WorkMsgMaxDelay = MAX (WorkMsgMaxDelay-i)

where:
  • WorkMsgMaxDelay-iis calculated in each online node as:

    WorkMsgsendDelayMax + WorkMsgReceiveDelayMax

  • i varies from 1 to N, where N is the number of online nodes.
WorkMsgMaxDelay does not represent the subtotal of the same message on the send and receive side.
WorkTypeInuseMax Total of the AMP Worker Task (AWT) columns:

WorkTypeInuseMax = MAX (WorkTypeInuseMax-i)

where:
  • WorkTypeInuseMax-iis the sum of WorkTypeMax00 through WorkTypeMax15 in each node.
  • i varies from 1 to N, where N is the number of online nodes.
WorkTimeInuseAvg Average number of AWTs used. This field is result of:

WorkTimeInuseAvg = SUM of (WorkTimeInuse-i) / N

where:
  • WorkTimeInuse-i is calculated in each online node as:

    WorkTimeInuse/(Centisecs * 10 * NCPUs)

    NCPUs is the number of CPUs in the node.

  • i varies from 1 to N, where N is the number of online nodes.

This value is available in the ResSpsView view as AwtUsedAvg.

IODelay Number of I/Os that are delayed. This field is result of:

ProcBlksFsgRead + ProcBlksFsgWrite + ProcBlksFsgNIOs

IODelayTime Total time the I/O is delayed for. This field is the result of:

ProcWaitFsgRead + ProcWaitFsgWrite + ProcWaitFsgNIOs

PhysicalRead Number of physical reads performed for this period. This field is the result of:

FilePDbAcqReads + FilePDbPreReads + FilePCiAcqReads + FileSDbAcqReads + FileSCiAcqReads + FileTJtAcqReads + FileAPtAcqReads + FilePCiPreReads + FileSDbPreReads + FileSCiPreReads + FileTJtPreReads + FileAPtPreReads

PhysicalReadKB Number of physical reads in KB performed for this period. This field is result of:

FilePDbAcqReadKB + FilePDbPreReadKB + FilePCiAcqReadKB + FileSDbAcqReadKB + FileSCiAcqReadKB + FileTJtAcqReadKB + FileAPtAcqReadKB + FilePCiPreReadKB + FileSDbPreReadKB + FileSCiPreReadKB + FileTJtPreReadKB + FileAPtPreReadKB

PhysicalWrite Number of physical writes performed for this period. This field is result of:

FilePDbFWrites + FilePCiFWrites + FileSDbFWrites + FileSCiFWrites + FileTJtFWrites + FileAPtFWrites

PhysicalWriteKB Number of physical writers in KB performed for this period. This field is result of:

FilePDbFWriteKB + FilePCiFWriteKB + FileSDbFWriteKB + FileSCiFWriteKB + FileTJtFWriteKB + FileAPtFWriteKB

LogicalRead Number of logical reads performed for this period. This field is result of:

FilePDbAcqs + FilePDbPres + FilePCiAcqs + FileSDbAcqs + FileSCiAcqs + FileTJtAcqs + FileAPtAcqs + FilePCiPres + FileSDbPres + FileSCiPres + FileTJtPres + FileAPtPres

LogicalReadKB Number of logical reads in KB performed for this period. This field is result of:

FilePDbAcqKB + FilePDbPresKB + FilePCiAcqKB + FileSDbAcqKB + FileSCiAcqKB + FileTJtAcqKB + FileAPtAcqKB + FilePCiPresKB + FileSDbPresKB + FileSCiPresKB + FileTJtPresKB + FileAPtPresKB

LogicalWrite Number of logical writes performed for this period. This field is result of:

FilePDbDyRRels + FilePCiDyRRels + FileSDbDyRRels + FileSCiDyRRels + FileTJtDyRRels + FileAPtDyRRels

LogicalWriteKB Number of logical writes in KB performed for this period. This field is result of:

FilePDbDyRRelKB + FilePCiDyRRelKB + FileSDbDyRRelKB + FileSCiDyRRelKB + FileTJtDyRRelKB + FileAPtDyRRelKB

ExtraField1
This field is not currently used.
ExtraField2
This field is not currently used.
ExtraField3
This field is not currently used.
ExtraField4
This field is not currently used.
VPId Virtual partition ID.
WaitIO Number of milliseconds tasks in WD waited for I/O over the reporting period.

WaitIO is updated when the wait for I/O is completed.

WaitOther Number of milliseconds tasks in WD waited for reasons other than I/O over the reporting period (for example, a task waiting for a message).

WaitOther is updated when wait is completed.

CPURunDelay Number of milliseconds tasks in the WD sat in the CPU runqueue waiting to run over the reporting period.

This data can be used in determining demand for the virtual partition and Workload Share Percent. The Workload Share Percent* is a workload management method. If the CPU and I/O percentages for a virtual partition or WD are below their relative share values and the CPURunDelay values are low, there was insufficient demand to meet the share percentage. If the CPURunDelay values are high, higher tier SQL requests were allocated more resources so that there were insufficient resources remaining to allocate to SQL requests in this WD to meet its relative share.

A virtual partition divides a system so that a percentage of resources are allocated to a collection of workloads. A virtual partition can consist of WDs from all management methods.
IOSubmitted Number of I/Os submitted on behalf of this WD.
IOSubmittedKB KB of I/O submitted on behalf of this WD.
IOCompleted Number of I/Os completed on behalf of this WD.
IOCompletedKB KB of I/O completed on behalf of this WD.
IOCriticalSubmitted Number of I/Os submitted with critical status. These I/Os execute at top priority instead of being based on the I/O priority of the SQL request.
IOCriticalSubmittedKB KB of I/O submitted with critical status. These I/Os execute at top priority instead of being based on the I/O priority of the SQL request.
DecayLevel1IO Number of times SQL requests in the WD hit decay level 1 due to I/O.
DecayLevel1IO is used for Timeshare WDs** only.
DecayLevel2IO Number of times SQL requests in the WD decay level 2 due to I/O.
DecayLevel2IO is used for Timeshare WDs** only.
DecayLevel1CPU Number of times SQL requests in the WD hit decay level 1 due to CPU.
DecayLevel1CPU is used for Timeshare WDs** only.
DecayLevel2CPU Number of times SQL requests in the WD hit decay level 2 due to CPU.
DecayLevel2CPU is used for Timeshare WDs** only.
TacticalExceptionIO Number of times SQL requests in the WD hit a tactical per-node exception due to I/O.

An exception, used only for Tactical WDs, is created for each Tactical WD***.

TacticalExceptionCPU Number of times SQL requests in the WD hit a tactical per-node exception due to CPU.
TacticalExceptionCPU is used for Tactical WDs*** only.

* The Workload Share Percent Management Method workload is assigned a proportion of the resources that are available after allocations have been made for tactical workloads. The percentage of resources is divided equally between all requests running in the WD. For example, if the Workload Share Percent is 5% and there are five SQL requests, each SQL request will get 1% of the share resources. For more information, see Teradata® Viewpoint User Guide, B035-2206.

** The Timeshare Workload Management Method workload can be assigned to one of four stepped access levels, Top, High, Medium, or Low. The higher access levels are given larger access rates than the lower levels. For example, an SQL request assigned to a Timeshare WD with a Top access level, which has an access rate of 8, would receive eight times the amount of resources than an SQL request assigned to a Low access level.

Timeshare workloads are assigned resources remaining after all allocations have been made for tactical and Workload Share Percent workloads. For more information, see Teradata® Viewpoint User Guide, B035-2206.

*** The Tactical Workload Management Method workload yields the fastest available response time and executes at the highest tier, preempting all resource needs of other tiers. This method is well suited for critical, short-running queries that require fast response times. For more information, see Teradata® Viewpoint User Guide, B035-2206.

Example: Using MonitorWD

SELECT * FROM TABLE (MonitorWd()) AS t2 where vpid=102 and vprtype='amp';
 *** Query completed. One row found. 43 columns returned.
 *** Total elapsed time was 1 second.
                 WDId            0
                 PPId       0
                 PGId     254
                 AGId       0
               RelWgt       0
             NumProcs          454
              VprType  AMP
            QWaitTime   0.00000000000000E 000
         QWaitTimeMax   0.00000000000000E 000
           CpuUserPct   0.00000000000000E 000
      WorkMsgMaxDelay   0.00000000000000E 000
     WorkTypeInUseMax            0
     WorkTimeInUseAvg   0.00000000000000E 000
              IODelay   1.60000000000000E 001
          IODelayTime   1.19968000000000E 005
         PhysicalRead   0.00000000000000E 000
       PhysicalReadKB   0.00000000000000E 000
        PhysicalWrite   1.60000000000000E 001
      PhysicalWriteKB   6.56000000000000E 002
          LogicalRead   8.00000000000000E 000
        LogicalReadKB   2.56000000000000E 002
         LogicalWrite   8.00000000000000E 000
       LogicalWriteKB   2.56000000000000E 002
          ExtraField1   0.00000000000000E 000
          ExtraField2   0.00000000000000E 000
          ExtraField3   0.00000000000000E 000
          ExtraField4   0.00000000000000E 000
                 VPId     102
               WaitIO   5.80000000000000E 001
            WaitOther   7.78500000000000E 005
          CPURunDelay   2.98000000000000E 002
          IOSubmitted   1.60000000000000E 001
        IOSubmittedKB   1.48000000000000E 002
          IOCompleted   1.60000000000000E 001
        IOCompletedKB   1.48000000000000E 002
  IOCriticalSubmitted   0.00000000000000E 000
IOCriticalSubmittedKB   0.00000000000000E 000
        DecayLevel1IO   0.00000000000000E 000
        DecayLevel2IO   0.00000000000000E 000
       DecayLevel1CPU   0.00000000000000E 000
       DecayLevel2CPU   0.00000000000000E 000
  TacticalExceptionIO   0.00000000000000E 000
 TacticalExceptionCPU   0.00000000000000E 000