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:
|
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:
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:
|
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:
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 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:
|
WorkTimeInuseAvg | Average number of AWTs used. This field is result of: WorkTimeInuseAvg = SUM of (WorkTimeInuse-i) / N where:
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