15.10 - MonitorWD - Teradata Database

Teradata Database Application Programming Reference

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
featnum
B035-1090-151K

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

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

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

For information on the resource usage tables and columns described in the field calculations below, see Resource Usage Macros and Tables.

 

Field/Column Name

Description

PPId

  • On SLES 10 or earlier systems, this field returns the Performance Period ID.
  • On SLES 11 or later systems, this field is obsolete and returns a value of zero.
  • PGId

  • On SLES 10 or earlier systems, this field returns the performance group ID.
  • On SLES 11 or later systems, this field returns the pWDid value.
  • VprType

    Type of vproc:

  • AMP
  • PE
  • MISC
  • WDId

    WD ID.

    Note:  

  • On SLES 10 or earlier systems, if TASM Workloads are not enabled, the WDId fields in all rows return zeros.
  • 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.
  • AGId

  • On SLES 10 or earlier systems, this field returns the Allocation Group ID.
  • On SLES 11 or later systems, this field is obsolete and returns a value of zero.
  • RelWgt

  • On SLES 10 or earlier systems, this field returns the Active Relative Weight. That is, the dynamically assigned relative weight that considers, in its calculation, the activity of all other Allocation Groups present on the system. The RelWgt field constantly changes, unlike the relative weight assignment the Database Administrator assigns in the Teradata Viewpoint Workload Designer portlet.
  • RelWgt is the average relative weight of active online nodes (that is, divide the sum of the non-zero RelWgt by the count of online nodes with the non-zero RelWgt).

  • On SLES 11 or later systems, 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:
  • On SLES 10 or earlier systems, assigned to the PG at the end of the reporting period.
  • On SLES 11 or later systems, 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.
  • Note: 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)

    Note: 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.
  • Note: 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-i is calculated in each online node as:
  • WorkMsgsendDelayMax + WorkMsgReceiveDelayMax

  • i varies from 1 to N, where N is the number of online nodes.
  • Note: 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-i is 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)

    Note: 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

    Note: This field is not currently used.

    ExtraField2

    Note: This field is not currently used.

    ExtraField3

    Note: This field is not currently used.

    ExtraField4

    Note: 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 (see “Glossary” for a description). 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.

    Note: 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.

    Note: DecayLevel1IO is used for Timeshare WDs only (see “Glossary” for a description of this workload management method).

    DecayLevel2IO

    Number of times SQL requests in the WD decay level 2 due to I/O.

    Note: DecayLevel2IO is used for Timeshare WDs only (see “Glossary” for a description of this workload management method).

    DecayLevel1CPU

    Number of times SQL requests in the WD hit decay level 1 due to CPU.

    Note: DecayLevel1CPU is used for Timeshare WDs only (see “Glossary” for a description of this workload management method).

    DecayLevel2CPU

    Number of times SQL requests in the WD hit decay level 2 due to CPU.

    Note: DecayLevel2CPU is used for Timeshare WDs only (see “Glossary” for a description of this workload management method).

    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 (see “Glossary” for a description of this workload management method).

    TacticalExceptionCPU

    Number of times SQL requests in the WD hit a tactical per-node exception due to CPU.

    Note: TacticalExceptionCPU is used for Tactical WDs only. For a description of this workload management method, see “Glossary”.

    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