15.10 - MonitorVirtualResource - Teradata Database

Teradata Database Application Programming Reference

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

Collects performance information for each AMP, PE, or TVS vproc.

REPLACE FUNCTION SYSLIB.MonitorVirtualResource()
  RETURNS TABLE
   (VprocNo SMALLINT,
    VprocType VARCHAR(3) CHARACTER SET LATIN,
    Status CHAR CHARACTER SET LATIN,
    ProcId SMALLINT,
    ClusterNo SMALLINT,
    SessLogCount SMALLINT,   
    SessRunCount SMALLINT,
    CPUUse FLOAT,
    PctService FLOAT,
    PctAMPWT FLOAT,
    DiskUse FLOAT,
    DiskReads FLOAT,
    DiskWrites FLOAT,
    DiskOutReqAvg FLOAT,
    PctParser FLOAT,
    PctDispatcher FLOAT,
    HstBlkRds FLOAT,
    HstBlkWrts FLOAT,
    NetReads FLOAT,
    NetWrites FLOAT,
    NVMemAllocSegs FLOAT
   )
          .
          .
          .
;

The MonitorVirtualResource function provides similar functionality to the PMPC MONITOR VIRTUAL RESOURCE request. For information about this interface, see “MONITOR VIRTUAL RESOURCE” on page 172.

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

 

Column Name

Description

VprocNo

ID of an AMP (that is, a set of disks and the associated tasks or processes that, in combination, make up the AMP), PE or TVS vproc.

VprocType

Type of vproc:

  • AMP
  • PE
  • MISC
  • Status

    Status of the node, AMP, PE, or TVS vproc associated with this record:

  • U = Up/online.
  • D = Down/offline.
  • A node is up (U) when it is:

  • Configured into the system
  • Online
  • Capable of actively performing tasks associated with normal Teradata Database activity
  • Down (D) represents all other potential states.

    ProcId

    ID associated with a node. This value is computed as the module number within a cabinet plus 100 times the cabinet number. Usually formatted for display as zz9-99. However, this display format can be changed by the user.

    ClusterNo

    Number that identifies the cluster to which this AMP is assigned.

    Note: ClusterNo is not applicable to TVS vproc and returns NULL.

    SessLogCount

    Number of current sessions logged to this PE. A logged on session is either a session whose logon request was delivered to this PE, or a session that was switched to this PE following its logon.

    Note: The SessLogCount field contains the SubPoolId if the vproc type is TVS.

    SubpoolId identifies the subpool associated with the allocator vproc. A subpool defines a set of storage and allocator vprocs assigned to that storage.

    This value is NULL if certain conditions apply, see “Usage Notes” on page 172 for more information.

    SessRunCount

    Number of current sessions whose Initiate Requests (TSR messages) are addressed to this vproc. For example:

  • PEs have a SessRunCount that includes all the Teradata SQL and MONITOR sessions logged on to that PE.
  • AMPs may have a nonzero SessRunCount, since AMPs receive TSR messages from FastLoad or MultiLoad logons.
  • Note: Sessions involving Archive/Recovery jobs are not included in this SessRunCount because HUT sessions do not deliver TSR messages to a fixed AMP or PE.

    This value is NULL if certain conditions apply, see “Usage Notes” on page 172 for more information.

    Note: This field is not applicable to TVS vprocs.

    CPUUse

    % of CPU usage not spent being idle.

    This value is computed from ResUsageSvpr table data, where NCPUs is the number of CPUs in the node:

    100.00 * (CPUUExecPart00 + CPUUExecPart01 + ... + CPUUExecPart47 + CPUUServPart00 + CPUUServPart01 + ... + CPUUServPart47) / (NCPUs * SampleSec * 100)

    This value is NULL if certain conditions apply, see “Usage Notes” on page 172 for more information.

    PctService

    % of CPU resources spent in PDE user service processing.

    This value is computed from the ResUsageSvpr table data, where x represents the number of CPUs:

    (CPUUServPart00 + CPUUServPart01 + ... + CPUUServPart47) /(x * SampleSec)

    This value is NULL if certain conditions apply, see “Usage Notes” on page 172 for more information.

    PctAMPWT

    % of CPU resources used by either the AMP Worker Task (Partition 11) or by the TVS Task (Partition 31) depending on the type of vproc this record represents.

    This value depends on the number of CPUs in the node but does not exceed 100%. It is computed from the ResUsageSvpr table data, where x represents the number of CPUs on a node:

    For AMP vprocs:

    (CPUUExecPart11) / (x * SampleSec)

    For TVS vprocs:

    (CPUUExecPart31) / (x * SampleSec)

    This value is NULL if certain conditions apply, see “Usage Notes” on page 172 for more information.

    DiskUse

    % of disk usage per AMP. This value is computed from the ResUsageSvdsk table data:

       OutReqTime / SampleSec

    Note: DiskUse does not take into account overlapping of operations among multiple storage controllers, but it allows for the possibility of multiple requests for the same controller.

    This value is NULL if certain conditions apply, see “Usage Notes” on page 172 for more information.

    DiskReads

    Total number of physical disk reads per AMP during the collection period.

    This value is computed from the ResUsageSvpr table data as:

    FilePCiAcqReads + FilePDbAcqReads + FileSciAcqReads + FileSDbAcqReads + FileTjtAcqReads + FileAPtAcqReads

    This value is NULL if certain conditions apply, see “Usage Notes” on page 172 for more information.

    Note: This field is not applicable to TVS and PE vprocs.

    DiskWrites

    Total number of physical disk writes per AMP during the collection period.

    For PE and AMP-level displays, this value is computed from ResUsageSvpr table data as:

    FilePCiFWrites + FilePDbFWrites + FileSCiFWrites + FilesDbFWrites + FileTjtFWrites + FileAPtFWrites + FilePCiDyaWrites + FilePDbDyaWrites + FileSciDyaWrites + FilesDbDyaWrites + FileTjtDyaWrites + FileAptDyaWrites

    For TVS vproc displays, this value is computed from ResUsageSvpr table data as:

    AllocatorMapIOsDone

    This value is NULL if certain conditions apply, see “Usage Notes” on page 172 for more information.

    DiskOutReqAvg

    Average number of outstanding disk requests.

    For AMP-level displays, this value is computed from ResUsageSvdsk table data, assuming n is the number of storage devices used by this vproc:

    (ReadRespTot 1 + WriteRespTot 1 + ... + ReadRespTot n + WriteRespTot n) / CentiSecs

    Note: This field is not applicable to PE vprocs.

    For TVS vproc-level displays, this value is computed from ResUsageSvpr table data as:

    AllocatorMapIOsStarted - AllocatorMapIOsDone

    This value is NULL if certain conditions apply, see “Usage Notes” on page 172 for more information.

    PctParser

    Note: This field is obsolete and returns zero or NULL.

    PctDispatcher

    % of CPU resources spent in PE Dispatcher processing.

    This value depends on the number of CPUs in the node but does not exceed 100%. This value is computed from the ResUsageSvpr table data, where x represents the number of CPUs on a node:

    (CPUUExecPart13 + CPUUServPart13) / (x * SampleSec)

    Note: The PercntParser CPU time is included in the PercntDispatcher value.

    This value is NULL if certain conditions apply, see “Usage Notes” on page 172 for more information.

    Note: This field is not applicable to AMP and TVS vprocs.

    HstBlkRds

    Number of message blocks (one or more messages sent in one physical group) received from all clients.

    This value corresponds to the column totals in the ResUsageShst table supplying HostBlockReads for this vproc.

    This value is NULL if certain conditions apply, see “Usage Notes” on page 172 for more information.

    Note: This field is not applicable to AMP or TVS vprocs.

    HstBlkWrts

    Number of message blocks (that is, one or more messages sent in one physical group) sent to all hosts.

    This value corresponds to the column totals in the HstBlkWrts column of the ResUsageShst table.

    This value is NULL if certain conditions apply, see “Usage Notes” on page 172 for more information.

    Note: This field is not applicable to AMP or TVS vprocs.

    NetReads

    Number of Reads from the BYNET to the vproc.

    The value is computed from the ResUsageSvpr table data as:

    NetBrdReads + NetPtPReads

    This value is NULL if certain conditions apply, see “Usage Notes” on page 172 for more information.

    NetWrites

    Number of messages written from the AMP, PE, or vproc to the BYNET during the collection period.

    The value is computed from the ResUsageSvpr table data as:

    NetBrdWrites + NetPtPWrites

    This value is NULL if certain conditions apply, see “Usage Notes” on page 172 for more information.

    NVMemAllocSegs

    Value is computed from ResUsageSvpr table data using the IoRespMax field. The IoRespMax is the maximum I/O response time in milliseconds. That is, the number of operations for each AMP vproc on that node.

    Note: This field is not applicable to PE and TVS vprocs.

    This value is NULL if certain conditions apply, see “Usage Notes” on page 172 for more information.

    Note: The NVMemAllocSegs field of the MonitorVirtualResource function corresponds to the MaxIOResp field of the MONITOR VIRTUAL RESOURCE request.

    SELECT VprocNo, VprocType, Status, PRocId, ClusterNo  FROM
    TABLE (MonitorVirtualResource()) AS t2;
     
     *** Query completed. 9 rows found. 5 columns returned.
     *** Total elapsed time was 1 second.
     
        VprocNo  VprocType  Status       ProcId    ClusterNo
    -----------  ---------  ------  -----------  -----------
              0  AMP        U             16384            0
              1  AMP        U             16384            1
              2  AMP        U             16384            2
              3  AMP        U             16384            3
              4  AMP        U             16384            0
              5  AMP        U             16384            1
              6  AMP        U             16384            2
              7  AMP        U             16384            3
          10237  TVS        U             16384            0
          10238  TVS        U             16384            0
          1638   PE         U             16384         1025