15.10 - MONITOR VIRTUAL RESOURCE - 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 and returns:

  • System-wide (identical for all vprocs) data
  • vproc-specific data
  •  

    Element

    Data Type

    Description

    IndByte

    BYTE

    Indicator bits that specify which fields to treat as NULL if you are using the indicator mode.

    Each bit in the byte corresponds to one field in the input data.

    If data is supplied for that field, set the bit to zero.

    If the data for that field is NULL (that is, there is no data supplied for that field), set the bit to 1.

    Note: The IndByte field is only required if the CLIv2 request is submitted in indicator mode.

    mon_ver_id

    SMALLINT,
    NOT NULL

    MONITOR software version ID. This can be version 2 or later.

    For a general explanation of monitor version choices, see “MONITOR VERSION” on page 162.

    To use this request, you must have the MONRESOURCE privilege as part of your default role or this privilege must be granted directly to you.

    For more information on roles and privileges, see:

  • Database Administration
  • Security Administration
  • Teradata JDBC Driver User Guide
  • You can use the MONITOR VIRTUAL RESOURCE request to:

  • Expand on the data reported by the MONITOR VIRTUAL SUMMARY request.
  • In your initial problem analysis, a MONITOR VIRTUAL SUMMARY request may indicate a performance or system problem. MONITOR VIRTUAL RESOURCE allows you to collect RSS data on a vproc by vproc basis.

  • Continually monitor your system.
  • Monitor your system continually on a periodic basis, for example, every 10 minutes. Use this request to build a normal baseline profile for your system. When you notice something abnormal, such as the last reading is significantly different from the normal baseline reading, or when a user complains that a job is slow, this request can tell you if there is a parallel efficiency problem or a constraint to throughput, and which vproc is causing it. For example, if one PE shows a much higher usage than the other PEs, that PE might be overloaded. Also, if one AMP is loaded more heavily than the other AMPs, you may have problems with a skewed index.

  • Determine whether a new application can be added to the current system load without disruption.
  • The vproc usage information collected by this request can help you evaluate the impact of adding new applications to an already heavily utilized system and help you plan potential system upgrades.

  • Help resolve problems that session-level usage information cannot resolve.
  • When the MONITOR SESSION request does not show any cause for the problem, this request can supply information regarding congestion, memory allocations, BYNET outages, and system status.

    The MONITOR VIRTUAL RESOURCE request can provide information about:

  • How the system is being used (for example, the number of sessions associated with each vproc and the percentage of CPU usage by vproc).
  • How system resource usage is spread across the vprocs (for example, whether the vprocs are used evenly).
  • How much physical disk I/O, BYNET traffic, or host reads and writes are occurring.
  • Whether congestion or excessive swapping is a problem on any vproc or group of vprocs.
  • The MONITOR VIRTUAL RESOURCE request returns some of the same fields found in the resource usage tables. You can use both MONITOR VIRTUAL RESOURCE and resource usage data for problem detection. Unlike resource usage data, MONITOR VIRTUAL RESOURCE data is near real time, and requires less overhead to produce, but is less comprehensive. MONITOR VIRTUAL RESOURCE data can help detect:

  • Poor AMP CPU parallel efficiency
  • Poor disk parallel efficiency
  • A higher than expected disk read/write ratio
  • A high swap I/O rate
  • If MONITOR VIRTUAL RESOURCE does not give you all the detailed data you need for problem detection, run one or more of the resource usage macros for the AMP, PE, or TVS vprocs. See Resource Usage Macros and Tables for more information on problem detection and the resource usage macros.

    Note: You must set the rate by which vproc resource usage data is updated in memory (ResMonitor rate) to nonzero for the MONITOR VIRTUAL RESOURCE request to return meaningful data. If you set the ResMonitor rate to zero, NULL is returned for all vproc usage data.

    Also note that if the TVS vproc is configured on a node without any AMPs, all fields will return a value of zero.

    After a system outage or a change in the ResMonitor rate, do not request data again until after completion of the first collection period requested after the crash or change in rate. Otherwise, the data returned will contain NULL for all columns except NetAUp, NetBUp, SampleSec, CollectionDate, CollectionTime, VProcType, ProcId, VProcNo, HostId/ClusterNo, and Status, and may not be fully representative. This is because after a system failure, the in‑memory counters are reset, and typically the contents of the counters are not well defined until a full collection period has elapsed. In fact, if you were logged on prior to the system outage and you issue your first MONITOR VIRTUAL RESOURCE request after the outage, you will receive a warning that the Teradata Database system has been restarted.

    The MONITOR VIRTUAL RESOURCE request is treated internally as a two statement request, with each statement generating a response. The two statement response returned from Teradata Database contains the following sequence of parcel types:

     

    Parcel Sequence

    Parcel Flavor

    Length

    (Bytes)

    Comments/Key Parcel Body Fields

    Success

    8

    18 to 273

    StatementNo = 1

    ActivityCount = 1

    ActivityType = 95 (PCLMONVRES)

    DataInfo

    71

    6 to 64100

    Optional; this parcel is present if request was IndicData parcel.

    Record

    10

  • 5 to 64100 (record mode)
  • 6 to 64100 (indicator mode)
  • Depending on request (Data or IndicData), data is in record or indicator mode. One record is returned that contains the duration of the collection period (in seconds), BYNET status, and the date and time the Virtual Resource cache was last refreshed.

    EndStatement

    11

    6

    StatementNo = 2‑byte integer

    Success

    8

    18 to 273

    StatementNo = 2

    ActivityCount = Number of vprocs

    ActivityType = 95 (PCLMONVRES)

    DataInfo

    71

    6 to 64100

    Optional; this parcel is present if request was IndicData parcel.

    Record

    10

  • 5 to 64100 (record mode)
  • 6 to 64100 (indicator mode)
  • Depending on request (Data or IndicData), data is in record or indicator mode. One record per vproc is returned that contains a description for each vproc in the system.

    EndStatement

    11

    6

    StatementNo = 2‑byte integer

    EndRequest

    12

    4

    None

    Note: Each of the statement types described below correspond to a ResultSet returned by the Teradata JDBC Driver, and each statement type field corresponds to a ResultSet column. For more information on ResultSets, see Teradata JDBC Driver Reference.

    Statement 1

    The Record parcel in the first statement of the MONITOR VIRTUAL RESOURCE response returns global data about collection duration and BYNET status in the order listed below.

     

    Field/Column Name

    Data Type

    Description

    NetAUp

    VARCHAR (1),
    NOT NULL

    Status of the BYNETs (if there are more than two, the first two) on a system-wide basis:

  • U = All node BYNETs are up/online.
  • D = One or more node BYNETs is down/offline.
  • “” = A temporary condition where the BYNET data is not available.
  • NetBUp

    VARCHAR (1),
    NOT NULL

    Status of the BYNETs (if there are more than two, the first two) on a system-wide basis:

  • U = All node BYNETs are up/online.
  • D = One or more node BYNETs is down/offline.
  • “” = A temporary condition where the BYNET data is not available.
  • SampleSec

    SMALLINT, NOT NULL

    Duration of the collection period in seconds. This field returns the ResMonitor rate. See “Data Collection” on page 39 and “SET RESOURCE RATE” on page 205 for more information on ResMonitor.

    CollectionDate

    DATE,
    NOT NULL

    Date the Virtual Resource cache was last refreshed.

    CollectionTime

    FLOAT,
    NOT NULL

    Time the Virtual Resource cache was last refreshed.

    Statement 2

    The response to the second statement results in multiple Record parcels that consist of a record for each vproc in the system. The Record parcels in the second statement of the MONITOR VIRTUAL RESOURCE response can return multiple records, specifically, one record of 32 fields for each vproc in the system. For example, if you have 50 vprocs, 50 records are returned with specific information for each vproc, one record describing the collection period, and BYNET status for the entire system. Records are sorted by VProcType and VProcNo.

    The following table shows the order in which the data is returned from the Record parcel.

     

    Column Name

    Data Type

    Description

    VprocType

    VARCHAR (3),
    NOT NULL

    Type of vproc:

  • AMP
  • PE
  • MISC
  • ProcId

    SMALLINT

    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.

    VprocNo

    SMALLINT,
    NOT NULL

    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.

    HostId/ClusterNo

    SMALLINT

    For a PE vproc, this field, HostId, identifies one of the hosts or LANs associated with the described PE.

    For an AMP vproc, this field, ClusterNo, identifies the cluster to which this AMP is assigned.

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

    CPUUse

    FLOAT, range 0 - 100%

    % of CPU usage not spent being idle.

    The value is computed from ResUsageSvpr table data as, 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.

    Status

    VARCHAR (1),
    NOT NULL

    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.

    SessLogCount

    SMALLINT

    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

    SMALLINT

    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.

    DiskUse

    FLOAT

    % 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

    FLOAT

    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

    FLOAT

    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

    FLOAT

    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.

    HostBlockReads

    FLOAT

    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.

    HostBlockWrites

    FLOAT

    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.

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

    MemAllocates

    FLOAT

    Number of segments allocated to memory resources.

    This value is calculated from the following ResUsageSvpr table column:

    MemCtxtAllocs

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

    MemAllocateKB

    FLOAT

    Value represents the change in vproc-level memory. MemAllocateKB represents a delta from the previous reporting period. It reports negative values as less memory is used.

    This value is calculated from the following ResUsageSvpr column:

    MemCtxAllocs * FIXEDPAGESIZE

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

    PercentService

    FLOAT

    % 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.

    PercntAMPWT

    FLOAT,
    range 0 - 100%

    % 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. For information on partition assignments, see Resource Usage Macros and Tables.

    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.

    PercntParser

    FLOAT,
    range 0 - 100%

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

    PercntDispatcher

    FLOAT,
    range 0 - 100%

    % 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: PercntDispatcher is not applicable to AMP and TVS vprocs.

    NetReads

    FLOAT

    Number of Reads from the BYNET to the vproc.

    This value is computed from the ResUsageSvpr table data as follows:

    NetBrdReads + NetPtPReads

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

    NetWrites

    FLOAT

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

    This value is computed from the ResUsageSvpr table data as follows:

    NetBrdWrites + NetPtPWrites

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

    MaxIOResp

    FLOAT

    Value is computed from ResUsageSvpr table data using the IoRespMax field. IoRespMax is the maximum I/O response time in milliseconds on an AMP. For details, see Resource Usage Macros and Tables.

    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.

    This example shows how the parcels for a MONITOR VIRTUAL RESOURCE request, built by CLIv2, look when sent to the Teradata Database server. The size of the response buffer is set in the example at the maximum (64,000 bytes), although you can set it to any size. However, a minimum response size is 32,000 bytes.

     

    Flavor

    Length

    Body

    Num

    Name

    Bytes

    Field

    Char/Decimal

    0001

    Req

    28

    Request

    MONITOR VIRTUAL RESOURCE

    0003

    Data

    6

    MonVerID

    9

    0004

    Resp

    6

    BufferSize

    64000

    For an example of how the PM/API request, built in Java, appears when sent to the Teradata Database server, see Teradata JDBC Driver Reference.

    This example shows the values returned in character text format (a record for each vproc) for the MONITOR VIRTUAL RESOURCE request. Your application program may display returned values in a different format.

    Note: You can rename the SampleSec field in your application. In the output below, the SampleRate value is the SampleSec value.

    Pay attention to SampleRate when interpreting the results of this request.

    Success parcel: 
    StatementNo=1,    ActivityCount=1,
    ActivityType=95,  FieldCount=5
     
    NetAUp:  U NetBUp:  U
    SampleRate: 30
    Collection Date/Time:  06/15/2011 18:29:31.00
     
     
    Success parcel: StatementNo=2,ActivityCount=8,ActivityType=95, FieldCount=23
     
    VprocNo:      0   Vproctype: AMP   Status:  U
    ProcId:      33   HostId/ClusterNo: 0
     
    SessLogCount: 0   SessRunCount: 0
     
    CPUUse:        0.0   PctService:  0.0
    PctAMPWT:      0.0   DiskUse: 32.2
    DiskReads:    0.00   DiskWrites: 3445.00   DiskOutReqAvg:    1.18
     
    NetReads:    51.00   NetWrites:    48.00
    NVMemAllocSegs:  283.00
     
    ---------------------------------------------------------
    VprocNo:      1   Vproctype: AMP   Status:  U
    ProcId:      33   HostId/ClusterNo: 0
     
    SessLogCount: 0   SessRunCount: 0
     
    CPUUse:        0.0   PctService:  0.0
    PctAMPWT:      0.0   DiskUse: 32.4
    DiskReads:    0.00   DiskWrites: 3440.00   DiskOutReqAvg:    1.52
     
    NetReads:    38.00   NetWrites:    39.00
    NVMemAllocSegs:  307.00
     
    ---------------------------------------------------------
    VprocNo:      2   Vproctype: AMP   Status:  U
    ProcId:      33   HostId/ClusterNo: 1
     
    SessLogCount: 0   SessRunCount: 0
     
    CPUUse:        0.0   PctService:  0.0
    PctAMPWT:      0.0   DiskUse: 32.6
    DiskReads:    0.00   DiskWrites: 3441.00   DiskOutReqAvg:    1.48
     
    NetReads:    38.00   NetWrites:    39.00
    NVMemAllocSegs:  277.00
     
    ---------------------------------------------------------
    VprocNo:      3   Vproctype: AMP   Status:  U
    ProcId:      33   HostId/ClusterNo: 1
     
    SessLogCount: 0   SessRunCount: 0
     
    CPUUse:        0.0   PctService:  0.0
    PctAMPWT:      0.0   DiskUse: 32.3
    DiskReads:    0.00   DiskWrites: 3357.00   DiskOutReqAvg:    1.06
     
    NetReads:    37.00   NetWrites:    38.00
    NVMemAllocSegs:  258.00
     
    ---------------------------------------------------------
    VprocNo:  10237   Vproctype: TVS   Status:  U
    ProcId:      33   HostId/ClusterNo: 0
     
    SubPoolId: 1
     
    CPUUse:        0.0   PctService:  0.0
    CPUExecPart31:      0.0        DiskUse:  0.0
    AllocatorMapIOsDone:   75.00   PendingAllocatorMapIOs:    0.00
     
    NetReads:    76.00   NetWrites:    77.00
    NVMemAllocSegs:    0.00
     
    ---------------------------------------------------------
    VprocNo:  10238   Vproctype: TVS   Status:  U
    ProcId:      33   HostId/ClusterNo: 0
     
    SubPoolId: 0
     
    CPUUse:        0.0   PctService:  0.0
    CPUExecPart31:      0.0        DiskUse:  0.0
    AllocatorMapIOsDone:   76.00   PendingAllocatorMapIOs:    0.00
     
    NetReads:    77.00   NetWrites:    76.00
    NVMemAllocSegs:    0.00
     
    ---------------------------------------------------------
    VprocNo:  16382   Vproctype: PE    Status:  U
    ProcId:      33   HostId/ClusterNo: 1025
     
    SessLogCount: 1   SessRunCount: 0
     
    CPUUse:        0.0   PctService:  0.0
    PctParser:     0.0   PctDispatcher:  0.0   HstBlkRds:    0.00   HstBlkWrts:    0
    .00
     
    NetReads:     7.00   NetWrites:     7.00
    NVMemAllocSegs:    0.00
     
    ---------------------------------------------------------
    VprocNo:  16383   Vproctype: PE    Status:  U
    ProcId:      33   HostId/ClusterNo: 1025
     
    SessLogCount: 1   SessRunCount: 0
     
    CPUUse:        0.0   PctService:  0.0
    PctParser:     0.0   PctDispatcher:  0.0   HstBlkRds:    0.00   HstBlkWrts:    0
    .00
     
    NetReads:     0.00   NetWrites:     1.00
    NVMemAllocSegs:    0.00
    EndStatement.
    EndRequest.

    All users who are logged on and issue a MONITOR VIRTUAL RESOURCE request after a system restart, or after the last rate change can expect to receive a warning message. Generally, two types of situations can produce warning messages:

  • After a system restart, before and after a collection period has expired.
  • If the collection period has not expired and the user issues the next MONITOR VIRTUAL RESOURCE request, many of the values returned are NULL.

  • After the last rate change, before and after a collection period has expired.
  • If the collection period has not expired and the user issues the next MONITOR VIRTUAL RESOURCE request, many of the values returned are NULL.

    For a discussion of general warning and error messages that may be returned by MONITOR VIRTUAL RESOURCE and other requests, see “Common Warning and Error Messages” on page 54.

    For more detailed information on warning and error messages, see Messages.

    If you executed an ABORT SESSION request, data returned in a MONITOR PHYSICAL RESOURCE or MONITOR VIRTUAL RESOURCE request may be altered. Whether you notice the change in data depends on the scope of the ABORT SESSION request. For example, if you execute an ABORT SESSION and log off all of the sessions associated with a specific host (or client), the PEs associated with that client will report a large decrease in resource consumption. However, if the ABORT SESSION request only aborts one transaction from one session, you may not notice a change in AMP or PE resource use.

    You must execute the SET RESOURCE RATE request to activate resource data collection before you execute a MONITOR VIRTUAL RESOURCE or MONITOR PHYSICAL RESOURCE request. This means that you must set the resource monitoring rate (ResMonitor) to nonzero. If the ResMonitor rate is set to zero, you will receive an error message.

    A change in the resource collection rate by User A, for example, may affect the data reported by MONITOR VIRTUAL RESOURCE or MONITOR PHYSICAL RESOURCE request made by User B. If the ResMonitor rate is altered, User B receives a warning message when executing a subsequent MONITOR VIRTUAL RESOURCE or MONITOR PHYSICAL RESOURCE request.