MONITOR PHYSICAL RESOURCE Request | Application Programming Reference | Vantage - 17.10 - MONITOR PHYSICAL RESOURCE - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Application Programming Reference

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1090-171K
Language
English (United States)

Collects RSS data and returns node-specific data.

Input Data

Element Data Type Description
IndByte BYTE Indicator bits that specify which fields to treat as NULL if you are using 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 only.

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:

Usage Notes - MONITOR PHYSICAL RESOURCE

Because information is given on the detailed resource usage of each node, performance concerns can be isolated by node.

Use the MONITOR PHYSICAL RESOURCE request to:
  • Expand on the data reported by the MONITOR PHYSICAL SUMMARY request.

    In your initial problem analysis, a MONITOR PHYSICAL SUMMARY request may indicate a performance or system problem. MONITOR PHYSICAL RESOURCE allows you to collect RSS data on a node by node basis.

  • Continually monitor your system.
    Monitor your system 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 or a user complains that a job is slow (such as the last reading is significantly different from the normal baseline reading), this request can tell you:
    • If there is a parallel efficiency problem
    • A constraint to throughput
    • And which node is causing it.
  • Determine whether a new application can be added to the current system load without disruption.

    The node 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, the MONITOR PHYSICAL RESOURCE request provides information on congestion, memory allocations, BYNET outages, and system status.

The MONITOR PHYSICAL RESOURCE request provides the following information:
  • How the system is being used (for example, the percentage of CPU usage by node)
  • How system resource usage is spread across the nodes
  • How much physical disk Input/Output (I/O), BYNET traffic, or host reads and writes are occurring
  • Whether congestion or excessive swapping is a problem on any node or group of nodes
The MONITOR PHYSICAL RESOURCE request returns some of the same fields found in the resource usage tables. You can use both MONITOR PHYSICAL RESOURCE and resource usage data for problem detection. Unlike resource usage data, MONITOR PHYSICAL RESOURCE data is near real time, requires less overhead to produce, but is less comprehensive. MONITOR PHYSICAL RESOURCE data helps detect:
  • Poor Node CPU parallel efficiency
  • Poor disk parallel efficiency
  • A higher than expected disk read/write ratio
  • A high swap I/O rate

If the MONITOR PHYSICAL RESOURCE request does not provide enough detailed data for problem detection, run one or more of the resource usage macros. For more information, see Teradata Vantage™ - Resource Usage Macros and Tables, B035-1099.

For some of the MONITOR PHYSICAL RESOURCE and MonitorPhysicalResource fields, NULL returns if:
  • A node is down or offline
  • The ResMonitor is set to zero
    You must set the ResMonitor rate to a nonzero value to allow the MONITOR PHYSICAL RESOURCE request or MonitorPhysicalResource function to return meaningful data.
  • A request for data is made before completion of the first collection period following either a system outage or a change in the ResMonitor rate

    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 except NetAUp, NetBUp, SampleSec, ProcId, AMPCount, PECount, and Status, and may not be fully representative. The in-memory counters reset after a crash. Typically the contents of the counters are not well defined until a full collection period has elapsed. If you were logged on prior to the system outage, and you issue the first MONITOR PHYSICAL RESOURCE request or MonitorPhysicalResource function after the outage, you will receive a warning that the database system has been restarted.

CLIv2 Response Parcels

The MONITOR PHYSICAL RESOURCE request is treated internally as a two statement request with each statement generating a response. The database returns a two statement response containing 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 = 96 (PCLMONPRES)

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. It contains the duration of the collection period (in seconds), BYNET status, and the data and time of when the Physical Resource cache was last refreshed.
EndStatement 11 6 StatementNo = 2-byte integer
Success 8 18 to 273 StatementNo = 2

ActivityCount = Number of nodes

ActivityType = 96 (PCLMONITOPRES)

DataInfo 71 6 to 64100 Optional; this parcel is present if request was IndicReq 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 node is returned. Each record contains a description for each node, including BYNET status.
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 response to the first statement results in a Record parcel containing global data about the collection duration and BYNET status that is generated once for the whole system. The following table shows the order in which the data is returned.

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 is equivalent to the ResMonitor rate. See Data Collection and SET RESOURCE RATE for more information on ResMonitor.
CollectionDate DATE

NOT NULL

Date the Physical Resource cache was last refreshed.
CollectionTime FLOAT,

NOT NULL

Time the Physical Resource cache was last refreshed.

Statement 2

The response to the second statement results in multiple Record parcels that consist of a one record of 32 fields for each node in the system. For example, if you have 50 nodes, 50 records are returned with specific information for each node. One record describes the collection period and BYNET status for the entire system.

The following table shows the order in which the Record parcel returns the data.

Field/Column Name Data Type Description
ProcId INTEGER

NOT NULL

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.

AmpCount SMALLINT

NOT NULL

Current number of AMPs currently executing on the associated node.
PECount SMALLINT

NOT NULL

Current number of active PEs on the associated node.
CPUUse FLOAT

range 0 - 100%

% of CPU usage not spent being idle. This value is computed from ResUsageSpma table data as:

PercntUser + PercntService

This value is NULL if certain conditions apply, see Usage Notes - MONITOR PHYSICAL RESOURCE.

PercntIOWait FLOAT % of CPU resources in idle and waiting for I/O completion. This value is computed from ResUsageSpma data as follows, where x is the number of CPUs:

CPUIOWAIT / (x * SampleSec)

This value is NULL if certain conditions apply, see Usage Notes - MONITOR PHYSICAL RESOURCE.

PercntService FLOAT % of CPU resources spent in PDE user service processing. The value is computed from the ResUsageSpma table data, where x represents the number of CPUs:

CPUUServ / (x * SampleSec)

This value is NULL if certain conditions apply, see Usage Notes - MONITOR PHYSICAL RESOURCE.

PercntUser FLOAT % of CPU resources spent in non-service user code processing. This value is computed from the ResUsageSpma table data, where x represents the number of CPUs:

CPUUExec / (x * SampleSec)

This value is NULL if certain conditions apply, see Usage Notes - MONITOR PHYSICAL RESOURCE.

Status VARCHAR (1)

NOT NULL

Status of the node associated with this record:
  • U = Up/online
  • D = Down/offline
  • S = Standby
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.

Standby (S) indicates the node is ready to join the configuration in place if another node goes down. When the node status is Standby, the SystemType, NetAUp, and NetBUp fields are not available and NULL or spaces will be returned.

NetAUse FLOAT % of BYNET A usage. This is the actual BYNET receiver usage. (The BYNET transmitter usage is maintained in resource usage separately and is typically lower than the receiver usage. This is caused by multicasts, where one transmitter sends a message to many receivers.) This value is computed from the ResUsageSpma table data as:

((NetSamples - NetTxIdle) / NetSamples)* 100.0

This value is NULL if certain conditions apply, see Usage Notes - MONITOR PHYSICAL RESOURCE.

DiskUse FLOAT % of disk usage per node.

This value is computed from ResUsageSldv table data as follows, assuming n is the number of ldv devices used by this node:

(LdvOutReqTime 1 + ... + LdvOutReqTime n) / (n*SampleSec)

This value is NULL if certain conditions apply, see Usage Notes - MONITOR PHYSICAL RESOURCE.

The DiskUse field does not take into account overlapping of operations among multiple storage devices, but it allows for the possibility of multiple requests for the same device.
DiskReads FLOAT Total number of physical disk reads per node during the collection period. This value is computed from ResUsageSldv table data as follows, assuming n is the number of ldv devices used by this node:

LdvReads 1 + ... + LdvReads n

This value is NULL if certain conditions apply, see Usage Notes - MONITOR PHYSICAL RESOURCE.

DiskWrites FLOAT Total number of physical disk writes per node during the collection period. This value is computed from ResUsageSldv table data as follows, assuming n is the number of ldv devices used by this node:

LdvWrites 1 + ... + LdvWrites n

This value is NULL if certain conditions apply, see Usage Notes - MONITOR PHYSICAL RESOURCE.

DiskOutReqAvg FLOAT Average number of outstanding disk requests per disks (averaged over all the disks on a node). This value can be used to monitor the load on the disks and indicate problems with the throughput if the level becomes too high.

This value is computed from ResUsageSldv table data as follows, assuming n is the number of ldv devices used by this node:

((LdvOutReqSum 1 / NULLIFZERO(LdvOutReqDiv 1)) + … + (LdvOutReqSum n / NULLIFZERO(LdvOutReqDiv n))) / n

The range of the value is typically 0 to 25.

This value is NULL if certain conditions apply, see Usage Notes - MONITOR PHYSICAL RESOURCE.

HostBlockReads FLOAT Number of message blocks (one or more messages sent in one physical group) received from all clients. This value is computed from ResUsageShst data, assuming n is the number of host channel and network connections on this node:

HostBlockReads 1 + ... + HostBlockReads n

This value is NULL if certain conditions apply, see Usage Notes - MONITOR PHYSICAL RESOURCE.

HostBlockWrites FLOAT Number of message blocks (that is, one or more messages sent in one physical group) sent to all hosts. For node displays, this value is computed from ResUsageShst data, assuming n is the number of host channel and network connections on this node:

HostBlockWrites 1 + ... + HostBlockWrites n

This value is NULL if certain conditions apply, see Usage Notes - MONITOR PHYSICAL RESOURCE.

SwapReads FLOAT Number of pages/segments read into node memory from the disk during the collection period after a prior write/drop. This value is computed from the ResUsageSpma table data as MemCtxtPageReads.

For information on MemCtxtPageReads, see Teradata Vantage™ - Resource Usage Macros and Tables, B035-1099.

This value is NULL if certain conditions apply, see Usage Notes - MONITOR PHYSICAL RESOURCE.

SwapWrites FLOAT Number of pages/segments written to swap area from node memory during the collection period. This value is computed from the ResUsageSpma table data as MemCtxtPageWrites.

For information on MemCtxtPageReads, see Teradata Vantage™ - Resource Usage Macros and Tables, B035-1099.

This value is NULL if certain conditions apply, see Usage Notes - MONITOR PHYSICAL RESOURCE.

SwapDrops FLOAT Number of pages/segments dropped from node memory during the collection period due to swapping.

This field returns zero.

MemAllocates FLOAT
This field is obsolete and returns zero or NULL.
MemAllocateKB FLOAT Value represents the change in the node-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 ResUsageSpma column:

MemVprAllocKB

This value is NULL if certain conditions apply, see Usage Notes - MONITOR PHYSICAL RESOURCE.

MemFailures FLOAT
This field is obsolete and returns zero or NULL.
MemAgings FLOAT
This field is obsolete and returns zero or NULL.
NetReads FLOAT Number of Reads from the BYNET to the node. This value is computed from the ResUsageSpma table data as:

NetRxCircBrd + NetRxCircPtP

This value is NULL if certain conditions apply, see Usage Notes - MONITOR PHYSICAL RESOURCE.

NetWrites FLOAT Number of messages written from the node to the BYNET during the collection period. For node-level displays, the value is computed from the ResUsageSpma table data as:

NetTxCircBrd + NetTxCircPtP

This value is NULL if certain conditions apply, see Usage Notes - MONITOR PHYSICAL RESOURCE.

NetAUp VARCHAR (1)

NOT NULL

Status of the BYNETs (if there are more than two, the first two) on a system-wide basis:
  • U = Node BYNET is up/online.
  • D = Node BYNET is down/offline.
  • “” = A temporary condition where the BYNET data is not available.
This output parameter is available on monitor software version 9 or later only.
NetBUp VARCHAR (1)

NOT NULL

Status of the BYNETs (if there are more than two, the first two) on a system-wide basis:
  • U = Node BYNET is up/online.
  • D = Node BYNET is down/offline.
  • “” = A temporary condition where the BYNET data is not available.
This output parameter is available on monitor software version 9 or later only.

Sample Input - CLIv2 Request

The following example shows how the parcels, built by CLIv2, for a MONITOR PHYSICAL RESOURCE request look when sent to the database.

In this example, the size of the response buffer is set 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 Value
0001 Req 20 Request MONITOR PHYSICAL 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 request might return the following values in character text format (a record for each node). 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. Use monitor version 13 when running the following example:

Submitting request MONITOR PHYSICAL RESOURCE; ... 

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

ProcId:    10001 (1-1) AmpCount: 4   PECount: 2 

CPUUse:  100.0   PrcntKernel:   0.0   PrcntService:   2.2   PrcntUser: 97.8 

Status: U

NetAUse:      0.0   DiskUse:     10.7
DiskReads:   36.00   DiskWrites: 6500.00   DiskOutReqAvg:   0.19

HstBlkRds:   8.00   HstBlkWrts:    8.00

SwapReads:   0.00   SwapWrites:    0.00  SwapDrops:    0.00 

MemAllocates:        0.00  MemAllocateKB:   16.00 MemFailures:         0.00   
MemAgings:    0.00 

NetReads:     0.00   NetWrites:    0.00 

NetAUp:  U NetBUp:  U 

Relationship Between MONITOR PHYSICAL 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 PHYSICAL 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.