MONITOR VIRTUAL RESOURCE Request | Application Programming Reference | Vantage - MONITOR VIRTUAL RESOURCE - Advanced SQL Engine

Collects performance information for each AMP, PE, or TVS vproc and returns:

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

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

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.

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.

Monitor Privileges

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:

Teradata Vantage™ - Database Administration, B035-1093
Teradata Vantage™ - Advanced SQL Engine Security Administration, B035-1100
Teradata JDBC Driver Reference, available at https://teradata-docs.s3.amazonaws.com/doc/connectivity/jdbc/reference/current/frameset.html

Usage Notes - MONITOR VIRTUAL RESOURCE

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 Teradata Vantage™ - Resource Usage Macros and Tables, B035-1099 for more information on problem detection and the resource usage macros.

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 exceptNetAUp, 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 database system has been restarted.

CLIv2 Response Parcels

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 the 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

Response

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, available at https://teradata-docs.s3.amazonaws.com/doc/connectivity/jdbc/reference/current/frameset.html.

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 and SET RESOURCE RATE 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	INTEGER	ID associated with a node. This value is computed as the module number within a cabinet plus the cabinet number times 10000. For example, a node #123 on cabinet #4 would return an INTEGER value of 40123.
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. 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 - MONITOR VIRTUAL RESOURCE.
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 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. 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 - MONITOR VIRTUAL RESOURCE.
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. This value is NULL if certain conditions apply, see Usage Notes - MONITOR VIRTUAL RESOURCE. 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 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 - MONITOR VIRTUAL RESOURCE.
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 - MONITOR VIRTUAL RESOURCE. 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 - MONITOR VIRTUAL RESOURCE.
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 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 - MONITOR VIRTUAL RESOURCE.
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 - MONITOR VIRTUAL RESOURCE. 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. 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 - MONITOR VIRTUAL RESOURCE.
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 - MONITOR VIRTUAL RESOURCE.
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 - MONITOR VIRTUAL RESOURCE.
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 Teradata Vantage™ - Resource Usage Macros and Tables, B035-1099. 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 - MONITOR VIRTUAL RESOURCE.
PercntParser	FLOAT range 0 - 100%	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) The PercntParser CPU time is included in the PercntDispatcher value. This value is NULL if certain conditions apply, see Usage Notes - MONITOR VIRTUAL RESOURCE. 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 - MONITOR VIRTUAL RESOURCE.
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 - MONITOR VIRTUAL RESOURCE.
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 Teradata Vantage™ - Resource Usage Macros and Tables, B035-1099. This field is not applicable to PE and TVS vprocs. This value is NULL if certain conditions apply, see Usage Notes - MONITOR VIRTUAL RESOURCE.

Sample Input - CLIv2 Request

This example shows how the parcels for a MONITOR VIRTUAL RESOURCE request, built by CLIv2, look when sent to the 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

Sample Input - Teradata JDBC Driver Request

For an example of how the PM/API request, built in Java, appears when sent to the database server, see Teradata JDBC Driver Reference, available at https://teradata-docs.s3.amazonaws.com/doc/connectivity/jdbc/reference/current/frameset.html.

Sample Output

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.

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.

Submitting request MONITOR VIRTUAL RESOURCE; ...

NetAUp:  U NetBUp:  U
SampleRate: 60
Collection Date/Time:   07/06/2016 13:48:00.00

VprocNo:      0   Vproctype: AMP   Status:  U
ProcId:    10001 (1-1) HostId/ClusterNo: 0

SessLogCount: 0   SessRunCount: 0

CPUUse:       24.7   PctService:  0.4
PctAMPWT:     24.3   DiskUse: 12.7
DiskReads:    5.00   DiskWrites: 1323.00   DiskOutReqAvg:    0.17

NetReads:    74.00   NetWrites:    69.00
NVMemAllocSegs:  265.00

---------------------------------------------------------
VprocNo:      1   Vproctype: AMP   Status:  U
ProcId:    10001 (1-1) HostId/ClusterNo: 1

SessLogCount: 0   SessRunCount: 0

CPUUse:       24.7   PctService:  0.4
PctAMPWT:     24.2   DiskUse: 12.9
DiskReads:    7.00   DiskWrites: 1345.00   DiskOutReqAvg:    0.17

NetReads:    55.00   NetWrites:    56.00
NVMemAllocSegs:  300.00

---------------------------------------------------------
VprocNo:      2   Vproctype: AMP   Status:  U
ProcId:    10001 (1-1) HostId/ClusterNo: 0

SessLogCount: 0   SessRunCount: 0

CPUUse:       24.8   PctService:  0.6
PctAMPWT:     24.2   DiskUse: 13.6
DiskReads:    6.00   DiskWrites: 1355.00   DiskOutReqAvg:    0.19

NetReads:    54.00   NetWrites:    57.00
NVMemAllocSegs:  302.00

---------------------------------------------------------
VprocNo:      3   Vproctype: AMP   Status:  U
ProcId:    10001 (1-1) HostId/ClusterNo: 1

SessLogCount: 0   SessRunCount: 0

CPUUse:       24.6   PctService:  0.4
PctAMPWT:     24.2   DiskUse: 13.4
DiskReads:   10.00   DiskWrites: 1355.00   DiskOutReqAvg:    0.19

NetReads:    55.00   NetWrites:    60.00
NVMemAllocSegs:  319.00

---------------------------------------------------------
VprocNo:  28670   Vproctype: TVS   Status:  U
ProcId:    10001 (1-1) HostId/ClusterNo: 0

SubPoolId: 1

CPUUse:        0.1   PctService:  0.0
CPUExecPart31:      0.1        DiskUse:  0.0
AllocatorMapIOsDone:   94.00   PendingAllocatorMapIOs:    0.00

NetReads:    99.00   NetWrites:   100.00
NVMemAllocSegs:    0.00

---------------------------------------------------------
VprocNo:  28671   Vproctype: TVS   Status:  U
ProcId:    10001 (1-1) HostId/ClusterNo: 0

SubPoolId: 0

CPUUse:        0.1   PctService:  0.0
CPUExecPart31:      0.1        DiskUse:  0.0
AllocatorMapIOsDone:   93.00   PendingAllocatorMapIOs:    0.00

NetReads:    98.00   NetWrites:    97.00
NVMemAllocSegs:    0.00

---------------------------------------------------------
VprocNo:  30718   Vproctype: PE    Status:  U
ProcId:    10001 (1-1) HostId/ClusterNo: 1025

SessLogCount: 0   SessRunCount: 0

CPUUse:        0.0   PctService:  0.0
PctParser:     0.0   PctDispatcher:  0.0   HstBlkRds:    0.00   HstBlkWrts:    
0.00

NetReads:   100.00   NetWrites:    99.00
NVMemAllocSegs:    0.00

---------------------------------------------------------
VprocNo:  30719   Vproctype: PE    Status:  U
ProcId:    10001 (1-1) 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:    18.00   NetWrites:    17.00
NVMemAllocSegs:    0.00

Warning and Error Messages

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.

For more detailed information on warning and error messages, see Teradata Vantage™ - Database Messages, B035-1096.

Relationship Between MONITOR VIRTUAL RESOURCE and ABORT SESSION

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.

Relationship Between MONITOR VIRTUAL RESOURCE and SET RESOURCE RATE

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.