MonitorPhysicalResource Function | Application Programming Reference | Vantage - MonitorPhysicalResource - Advanced SQL Engine - Teradata Database

Application Programming Reference

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
cpn1571792172880.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1090
lifecycle
previous
Product Category
Teradata Vantage™

Purpose

Collects RSS data and returns node-specific data.

Definition

REPLACE FUNCTION SYSLIB.MonitorPhysicalResource()
  RETURNS TABLE
   (ProcId INTEGER,
    Status CHAR CHARACTER SET LATIN,
    AmpCount SMALLINT,
    PECount SMALLINT,
    CPUUse FLOAT,
    PrcntKernel FLOAT,
    PrcntService FLOAT,
    PrcntUser FLOAT,
    DiskUse FLOAT,
    DiskReads FLOAT,
    DiskWrites FLOAT,
    DiskOutReqAvg FLOAT,
    NetAUse FLOAT,
    NetReads FLOAT,
    NetWrites FLOAT,
    HstBlkRd FLOAT,
    HstBlkWrts FLOAT,
    MemAllocates FLOAT,
    MemAllocateKB FLOAT,
    MemFailures FLOAT,
    MemAgings FLOAT,
    NetAUp CHAR(1) CHARACTER SET LATIN,
    NetBUp CHAR(1) CHARACTER SET LATIN
   )
          .
          .
          .
;

Usage Notes - MonitorPhysicalResource

The MonitorPhysicalResource function returns the detailed resource usage information for each node. Because the function reports on each node, you can isolate performance concerns by node.

The MonitorPhysicalResource function provides similar functionality to the PMPC MONITOR PHYSICAL RESOURCE request. For information about this interface, see MONITOR PHYSICAL RESOURCE.

Result Rows

Column Name Description
ProcId 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.

Status Status of the node 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.

AmpCount Current number of AMPs currently executing on the associated node.
PECount Current number of active PEs on the associated node.
CPUUse % of CPU usage not spent being idle. The node-level display is computed from ResUsageSpma table data as:

PercntUser + PercntService

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

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

((CPUIOWAIT/100) / (NCPUs * SampleSec)) * 100

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

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

DiskUse % of disk usage per node.

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

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

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.

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

DiskReads 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 Total number of physical disk writes per node during the collection period. This value is computed from ResUsageSldv table data as:

LdvWrites 1 + ... + LdvWrites n

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

DiskOutReqAvg Average number of outstanding disk requests.

The value is computed from ResUsageSldv table data as follows, assuming n is the number of ldv controllers 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.

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

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

NetReads 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 Number of messages written from the node to the BYNET during the collection period.

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

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

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.

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

MemVprAllocKB

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

MemFailures
This column is obsolete and returns zero or NULL.
MemAgings
This column is obsolete and returns zero or NULL.
NetAUp 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 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.

Example: Using MonitorPhysicalResource

SELECT t2.* from table (MonitorPhysicalResource()) as t2;

 *** Query completed. One row found. 28 columns returned.
 *** Total elapsed time was 1 second.

        ProcId   10001
        Status  U
      AmpCount       4
       PECount       2
        CPUUse   1.00000000000000E 002
   PrcntKernel   0.00000000000000E 000
  PrcntService   2.34804329725229E 000
     PrcntUser   9.76519567027477E 001
       DiskUse   9.08909242298085E 000
     DiskReads   8.48000000000000E 002
    DiskWrites   6.56500000000000E 003
 DiskOutReqAvg   1.58467943380516E-001
       NetAUse   0.00000000000000E 000
       NetBUse   0.00000000000000E 000
      NetReads   0.00000000000000E 000
     NetWrites   0.00000000000000E 000
        CICUse   0.00000000000000E 000
     HstBlkRds   2.00000000000000E 000
    HstBlkWrts   1.00000000000000E 000
  MemAllocates   0.00000000000000E 000
 MemAllocateKB   1.28000000000000E 002
   MemFailures   0.00000000000000E 000
     MemAgings   0.00000000000000E 000
 NVMemAllocate   0.00000000000000E 000
NVMemAllocSegs   0.00000000000000E 000
   NVMemAgings   0.00000000000000E 000
        NetAUp  U
        NetBUp  U