MonitorVirtualSummary Function | Application Programming Reference | Vantage - 17.10 - MonitorVirtualSummary - 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 global summary information on system utilization.

Syntax

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
  )
  ...
;

Syntax Elements

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.
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.
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.
HiCPUAMPUse
Highest CPUUse percentage currently associated with any online AMP.
This value is NULL if certain conditions apply, see usage notes.
HiDiskAMP
Highest DiskUse percentage currently associated with any online AMP.
This value is NULL if certain conditions apply, see usage notes.
HiDiskAMPIO
Highest DiskReads and DiskWrites value currently associated with any online AMP.
This value is NULL if certain conditions apply, see usage notes.
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.
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.
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.
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.
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.
LoDiskAMP
Lowest DiskUse percentage currently associated with any online AMP.
This value is NULL if certain conditions apply, see usage notes.
LoDiskAMPIO
Lowest DiskReads and DiskWrites number currently associated with any online AMP.
This value is NULL if certain conditions apply, see usage notes.
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.
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.
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.
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.
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.
HiCPUPEUse
Highest CPUUse percentage currently associated with any online PE.
This value is NULL if certain conditions apply, see usage notes.
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.
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.
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.
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.
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 database software (for example, 15.00.00.00).
This value is supplied by the database.
Version
Version number of the currently running database software (for example, 15.00.00.00).
This value is supplied by the database.

Usage Notes

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

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