16.20 - MonitorVirtualSummary - Teradata Vantage NewSQL Engine

Teradata Vantage™ Application Programming Reference

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
created_date
March 2019
category
Programming Reference
featnum
B035-1090-162K

Purpose

Collects global summary information on system utilization.

Definition

REPLACE FUNCTION SYSLIB.MonitorVirtualSummary()
 RETURNS TABLE
   (AMPAvgCPU FLOAT,
    AMPAvgDisk FLOAT, 
    AMPAvgDiskIO FLOAT, 
    HiCPUAMPUse FLOAT, 
    HiDiskAMP FLOAT,
    HiDiskAMPIO FLOAT, 
    HiCPUAMPNo SMALLINT, 
    HiDiskAMPNo SMALLINT, 
    HiDiskAMPIONo SMALLINT, 
    HiCPUAMPProc INTEGER, 
    HiDiskAMPProc INTEGER, 
    HiDiskAMPIOProc INTEGER,
    LoCPUAMPUse FLOAT,
    LoDiskAMP FLOAT,
    LoDiskAMPIO FLOAT, 
    LoCPUAMPNo SMALLINT, 
    LoDiskAMPNo SMALLINT,
    LoDiskAMPIONo SMALLINT,
    LoCPUAMPProc INTEGER, 
    LoDiskAMPProc INTEGER,
    LoDiskAMPIOProc INTEGER,
    PEAvgCPU FLOAT, 
    HiCPUPEUse FLOAT, 
    LoCPUPEUse FLOAT, 
    HiCPUPENo SMALLINT, 
    LoCPUPENo SMALLINT, 
    HiCPUPEProc INTEGER,
    LoCPUPEProc INTEGER, 
    SessionCnt FLOAT, 
    SesMonitorSys SMALLINT, 
    SesMonitorLoc SMALLINT, 
    ResLogging SMALLINT, 
    ResMonitor SMALLINT, 
    ReleaseNum CHAR(30) CHARACTER SET LATIN, 
    Version CHAR(32) CHARACTER SET LATIN
   )
          .
          .
          .
;

Usage Notes - MonitorVirtualSummary

The MonitorVirtualSummary function provides similar functionality to the PMPC MONITOR VIRTUAL SUMMARY request. For information about this interface, see MONITOR VIRTUAL SUMMARY.

Result Rows

Column Name Description
AMPAvgCPU Average % CPU usage (CPUUse) of all online AMPs in the configuration.

Assuming n is the number of online AMPs in the configuration, AMPAvgCPU is computed from CPUUse data as:

(CPUUse 1+ ... + CPUUse n ) / n

This value is NULL if certain conditions apply, see Usage Notes - MONITOR VIRTUAL SUMMARY.

AMPAvgDisk Average physical disk usage (DiskUse) of all online AMPs in the configuration.

Assuming n is the number of online AMPs in the configuration, AMPAvgDisk is computed from DiskUse data as:

(DiskUse 1+ ... + DiskUse n ) / n

This value is NULL if certain conditions apply, see Usage Notes - MONITOR VIRTUAL SUMMARY.

AMPAvgDiskIO Average physical disk DiskReads and DiskWrites of all online AMPs in the configuration.

Assuming n is the number of online AMPs in the configuration, AMPAvgDiskIO is computed from DiskReads and DiskWrites data as:

(DiskReads 1+ DiskWrites 1+ ... + DiskReads 1+ DiskWrites n ) / n

This value is NULL if certain conditions apply, see Usage Notes - MONITOR VIRTUAL SUMMARY.

HiCPUAMPUse Highest CPUUse percentage currently associated with any online AMP.

This value is NULL if certain conditions apply, see Usage Notes - MONITOR VIRTUAL SUMMARY.

HiDiskAMP Highest DiskUse percentage currently associated with any online AMP.

This value is NULL if certain conditions apply, see Usage Notes - MONITOR VIRTUAL SUMMARY.

HiDiskAMPIO Highest DiskReads and DiskWrites value currently associated with any online AMP.

This value is NULL if certain conditions apply, see Usage Notes - MONITOR VIRTUAL SUMMARY.

HiCPUAMPNo Vproc number (VprocNo) of an AMP with CPUUse equal to the value reported as HiCPUAMPUse.

This value is NULL if certain conditions apply, see Usage Notes - MONITOR VIRTUAL SUMMARY.

HiDiskAMPNo Number of an AMP with DiskUse equal to the value reported as HiDiskAMP.

This value is NULL if certain conditions apply, see Usage Notes - MONITOR VIRTUAL SUMMARY.

HiDiskAMPIONo Number of an AMP with the highest DiskReads and DiskWrites equal to the value reported as HiDiskAMPIO.

This value is NULL if certain conditions apply, see Usage Notes - MONITOR VIRTUAL SUMMARY.

HiCPUAMPProc ID of the node currently responsible for managing the AMP reported as HiCPUAMPNo.

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.

This value is NULL if certain conditions apply, see Usage Notes - MONITOR VIRTUAL SUMMARY.

HiDiskAMPProc ID of the node currently responsible for managing the AMP reported in HiDiskAMPNo.

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.

This value is NULL when HiDiskAMPNo is NULL.

HiDiskAMPIOProc ID of the node currently responsible for managing the AMP reported in HiDiskAMPIONo.

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.

This value is NULL when HiDiskAMPIONo is NULL.

LoCPUAMPUse Lowest CPUUse percentage currently associated with any online AMP.

This value is NULL if certain conditions apply, see Usage Notes - MONITOR VIRTUAL SUMMARY.

LoDiskAMP Lowest DiskUse percentage currently associated with any online AMP.

This value is NULL if certain conditions apply, see Usage Notes - MONITOR VIRTUAL SUMMARY.

LoDiskAMPIO Lowest DiskReads and DiskWrites number currently associated with any online AMP.

This value is NULL if certain conditions apply, see Usage Notes - MONITOR VIRTUAL SUMMARY.

LoCPUAMPNo Vproc number (VprocNo) of an AMP with CPUUse equal to the value reported as LoCPUAMPUse.

This value is NULL if certain conditions apply, see Usage Notes - MONITOR VIRTUAL SUMMARY.

LoDiskAMPNo Number of an AMP with DiskUse equal to the value reported as LoDiskAMP.

This value is NULL if certain conditions apply, see Usage Notes - MONITOR VIRTUAL SUMMARY.

LoDiskAMPIONo ID of an AMP with lowest DiskReads and DiskWrites equal to the value reported as LoDiskAMPIO.

This value is NULL if certain conditions apply, see Usage Notes - MONITOR VIRTUAL SUMMARY.

LoCPUAMPProc ID of the node currently responsible for managing the AMP reported as LoCPUAMPNo.

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.

This value is NULL if certain conditions apply, see Usage Notes - MONITOR VIRTUAL SUMMARY.

LoDiskAMPProc ID of the node currently responsible for managing the AMP reported as LoDiskAMPNo.

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.

This value is NULL when LoDiskAMPNo is NULL.

LoDiskAMPIOProc ID of the node currently responsible for managing the AMP reported as LoDiskAMPIONo.

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.

This value is NULL when LoDiskAMPIONo is NULL.

PEAvgCPU Average CPUUse for all online PEs in the configuration.

This value is NULL if certain conditions apply, see Usage Notes - MONITOR VIRTUAL SUMMARY.

HiCPUPEUse Highest CPUUse percentage currently associated with any online PE.

This value is NULL if certain conditions apply, see Usage Notes - MONITOR VIRTUAL SUMMARY.

LoCPUPEUse Lowest CPUUse percentage currently associated with any online PE.

This value is NULL when LoCPUPEUse is NULL.

HiCPUPENo Vproc number (VProcNo) of a PE with CPUUse equal to the value reported as HiCPUPEUse.

This value is NULL if certain conditions apply, see Usage Notes - MONITOR VIRTUAL SUMMARY.

LoCPUPENo Vproc number (VProcNo) of a PE with CPUUse equal to the value reported as LoCPUPEUse.

This value is NULL if certain conditions apply, see Usage Notes - MONITOR VIRTUAL SUMMARY.

HiCPUPEProc ID of the node currently responsible for managing the PE reported in HiCPUPENo.

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.

This value is NULL when HiCPUPENo is NULL.

LoCPUPEProc ID of the node currently responsible for managing the PE reported as LoCPUPENo.

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.

This value is NULL if certain conditions apply, see Usage Notes - MONITOR VIRTUAL SUMMARY.

SessionCnt Total number of sessions currently logged onto the system. This value is usually equal to the sum of the SessLogCount values for all PEs.

This value is NULL if certain conditions apply, see Usage Notes - MONITOR VIRTUAL SUMMARY.

SesMonitorSys Sets the maximum acceptable age of collected session-level data in memory to the PM/API application or end user.

The global rate is the default collection rate for all MONITOR sessions. If the value is set to zero, the collection capability is disabled.

SesMonitorLoc Sets the maximum acceptable age of collected session-level data in memory for an individual Monitor partition session that submits a MONITOR SESSION request.

This rate is initiated within a MONITOR session and may update session-level data within the system. If the value is zero, this allows SesMonitorSys to override the current local rate for that session.

ResLogging Interval in seconds at which resource usage data is written to one or more active resource usage database tables.
ResMonitor Interval in seconds at which all resource usage data is collected in memory for reporting via the PM/API.
ReleaseNum Release number of the currently running Teradata Database software (for example, 15.00.00.00).

This value is supplied by Teradata Database.

Version Version number of the currently running Teradata Database software (for example, 15.00.00.00).

This value is supplied by Teradata Database.

Example: Using MonitorVirtualSummary

select * from table (monitorvirtualsummary()) as t1;

 *** Query completed. One row found. 35 columns returned.
 *** Total elapsed time was 2 seconds.

      AMPAvgCPU   2.47375437427095E 001
     AMPAvgDisk   8.77770371604733E 000
   AMPAvgDiskIO   1.25500000000000E 003
    HiCPUAMPUse   2.48291951341443E 001
      HiDiskAMP   9.28178636893851E 000
    HiDiskAMPIO   1.27000000000000E 003
     HiCPUAMPNo       1
    HiDiskAMPNo       1
  HiDiskAMPIONo       1
   HiCPUAMPProc        10001
  HiDiskAMPProc        10001
HiDiskAMPIOProc        10001
    LoCPUAMPUse   2.46625562406266E 001
      LoDiskAMP   8.34860856523913E 000
    LoDiskAMPIO   1.22300000000000E 003
     LoCPUAMPNo       3
    LoDiskAMPNo       3
  LoDiskAMPIONo       0
   LoCPUAMPProc        10001
  LoDiskAMPProc        10001
LoDiskAMPIOProc        10001
       PEAvgCPU   0.00000000000000E 000
     HiCPUPEUse   0.00000000000000E 000
     LoCPUPEUse   0.00000000000000E 000
      HiCPUPENo   30719
      LoCPUPENo   30719
    HiCPUPEProc        10001
    LoCPUPEProc        10001
     SessionCnt   1.00000000000000E 000
  SesMonitorSys       1
  SesMonitorLoc       0
   VprocLogging      60
   VprocMonitor      60
     ReleaseNum  16u.00.00.41
        Version  16u.00.00.41_dr182707n