MonitorWD Function | Application Programming Reference | Teradata Vantage - 17.10 - MonitorWD - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Application Programming Reference

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1090-171K
Language
English (United States)

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

Syntax

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

Syntax Elements

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.
The 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
ExtraField2
ExtraField3
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 AgeOut Now data blocks not to keep in memory (fsgcache) and to be written to disk.
IOCompletedKB
KB of AgeOut Now data blocks not to keep in memory (fsgcache) and to be written to disk.
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 hit decay level 2 due to I/O.
DecayLevel1IO is used for Timeshare WDs** only.
DecayLevel1CPU
Number of times SQL requests in the WD hit decay level 1 due to CPU.
DecayLevel1IO is used for Timeshare WDs** only.
DecayLevel2CPU
Number of times SQL requests in the WD hit decay level 2 due to CPU.
DecayLevel1IO 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.

Usage Notes

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.

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