15.10 - MONITOR VIRTUAL SUMMARY - Teradata Database

Teradata Database Application Programming Reference

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
featnum
B035-1090-151K

Collects global summary information on system utilization.

 

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.

Note: 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” on page 162.

To use this request, you must have any one of the following monitor privileges as part of your default role or any of these privileges must be granted directly to you:

  • ABORTSESSION
  • MONRESOURCE
  • MONSESSION
  • SETRESRATE
  • SETSESSRATE
  • For more information on roles and privileges, see:

  • Database Administration
  • Security Administration
  • Teradata JDBC Driver User Guide
  • The MONITOR VIRTUAL SUMMARY request reports the following types of information:

  • CPU usage (average by vproc type online only)
  • Disk usage (average, high, and low by Vproc ID)
  • AMP usage (average, high, and low by AMP ID)
  • PE usage (average, high, and low by PE ID)
  • Number of logged on sessions
  • Rate information:
  • Vproc resource logging rate
  • Vproc resource monitoring rate
  • Session‑level system
  • Local monitoring rates
  • Current software release and version numbers
  • You must set the ResMonitor rate (the rate at which vproc resource usage data is updated in memory) to a nonzero value for the MONITOR VIRTUAL SUMMARY request to return meaningful data. If you set the ResMonitor rate to zero, NULL is returned for all columns related to vproc utilization.

    After a system outage or a change in the ResMonitor rate, do not request data again until after the completion of the first collection period requested after the outage or change in rate. If you ignore this, the data returned will contain NULL. This is because after an outage, the in‑memory counters are reset, and usually the contents of the counters are not well defined until a full collection period has elapsed. For example, if you were logged on prior to the system outage and you issue your first MONITOR VIRTUAL SUMMARY request after the outage, you will receive a warning that the Teradata Database server has been restarted.

    The response returned from the Teradata Database resembles a summary of the type of response returned by a MONITOR VIRTUAL RESOURCE request. The response is one row of 35 fields.

    The response returned from the Teradata Database contains the following sequence of parcel types.

     

    Parcel Sequence

    Parcel Flavor

    Length

    (Bytes)

    Comments/Key Parcel Body Fields

    Success

    8

    18 to 273

    ActivityCount =1

    ActivityType = 93 (PCLMONVSUMMARY)

    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. This contains the IndicData virtual summary information and the date and time the Virtual Resource cache was last refreshed.

    EndStatement

    11

    6

    StatementNo = 2-byte integer

    EndRequest

    12

    4

    None

    Note: The statement described below corresponds to a ResultSet returned by the Teradata JDBC Driver, and each of the fields correspond to a ResultSet column. For more information on ResultSets, see Teradata JDBC Driver Reference.

    The statement response results in a Record parcel containing resource usage information.

     

    Field/Column Name

    Data Type

    Description

    AMPAvgCPU

    FLOAT,
    range 0 to 100%

    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:

    (CPUUse1 + ... + CPUUsen) / n

    This value is NULL if certain conditions apply. See “Usage Notes” on page 185 for more information.

    AMPAvgDisk

    FLOAT

    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:

    (DiskUse1 + ... + DiskUsen) / n

    This value is NULL if certain conditions apply. See “Usage Notes” on page 185 for more information.

    AMPAvgDiskIO

    FLOAT

    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:

    (DiskReads1 + DiskWrites1 + ... + DiskReads1 + DiskWritesn) / n

    This value is NULL if certain conditions apply. See “Usage Notes” on page 185 for more information.

    HiCPUAMPUse

    FLOAT

    Highest CPUUse percentage currently associated with any online AMP.

    This value is NULL if certain conditions apply. See “Usage Notes” on page 185 for more information.

    HiCPUAMPNo

    SMALLINT

    VProcNo of an AMP with CPUUse equal to the value reported as HiCPUAMPUse.

    This value is NULL if certain conditions apply. See “Usage Notes” on page 185 for more information.

    HiCPUAMPProc

    SMALLINT

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

    This value is NULL if certain conditions apply. See “Usage Notes” on page 185 for more information.

    LoCPUAMPUse

    FLOAT

     

    Lowest CPUUse percentage currently associated with any online AMP.

    This value is NULL if certain conditions apply. See “Usage Notes” on page 185 for more information.

    LoCPUAMPNo

    SMALLINT

     

    VProcNo of an AMP with CPUUse equal to the value reported as LoCPUAMPUse.

    This value is NULL if certain conditions apply. See “Usage Notes” on page 185 for more information.

    LoCPUAMPProc

    SMALLINT

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

    This value is NULL if certain conditions apply. See “Usage Notes” on page 185 for more information.

    HiDiskAMP

    FLOAT

    Highest DiskUse percentage currently associated with any online AMP.

    This value is NULL if certain conditions apply. See “Usage Notes” on page 185 for more information.

    HiDiskAMPNo

    SMALLINT

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

    This value is NULL if certain conditions apply. See “Usage Notes” on page 185 for more information.

    HiDiskAMPProc

    SMALLINT

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

    This value is NULL when HiDiskAMPNo is NULL.

    LoDiskAMP

    FLOAT

    Lowest DiskUse percentage currently associated with any online AMP.

    This value is NULL if certain conditions apply. See “Usage Notes” on page 185 for more information.

    LoDiskAMPNo

    SMALLINT

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

    This value is NULL if certain conditions apply. See “Usage Notes” on page 185 for more information.

    LoDiskAMPProc

    SMALLINT

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

    This value is NULL when LoDiskAMPNo is NULL.

    HiDiskAMPIO

    FLOAT

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

    This value is NULL if certain conditions apply. See “Usage Notes” on page 185 for more information.

    HiDiskAMPIONo

    SMALLINT

    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” on page 185 for more information.

    HiDiskAMPIOProc

    SMALLINT

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

    This value is NULL when HiDiskAMPIONo is NULL.

    LoDiskAMPIO

    FLOAT

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

    This value is NULL if certain conditions apply. See “Usage Notes” on page 185 for more information.

    LoDiskAMPIONo

    SMALLINT

    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” on page 185 for more information.

    LoDiskAMPIOProc

    SMALLINT

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

    This value is NULL when LoDiskAMPIONo is NULL.

    PEAvgCPU

    FLOAT

    Average CPUUse for all online PEs in the configuration.

    This value is NULL if certain conditions apply. See “Usage Notes” on page 185 for more information.

    HiCPUPEUse

    FLOAT

    Highest CPUUse percentage currently associated with any online PE.

    This value is NULL if certain conditions apply. See “Usage Notes” on page 185 for more information.

    HiCPUPENo

    SMALLINT

    VProcNo of a PE with CPUUse equal to the value reported as HiCPUPEUse.

    This value is NULL if certain conditions apply. See “Usage Notes” on page 185 for more information.

    HiCPUPEProc

    SMALLINT

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

    This value is NULL when HiCPUPENo is NULL.

    LoCPUPEUse

    FLOAT

    Lowest CPUUse percentage currently associated with any online PE.

    This value is NULL if certain conditions apply. See “Usage Notes” on page 185 for more information.

    LoCPUPENo

    SMALLINT

    VProcNo of a PE with CPUUse equal to the value reported as LoCPUPEUse.

    This value is NULL when LoCPUPEUse is NULL.

    LoCPUPEProc

    SMALLINT

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

    This value is NULL if certain conditions apply. See “Usage Notes” on page 185 for more information.

    SessLogCount

    FLOAT

    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” on page 185 for more information.

    SesMonitorSys

    SMALLINT, NOT NULL, range 0-3600 seconds,

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

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

    This rate can be changed with the SET SESSION RATE request by specifying a system‑wide option and can be saved on disk (in the version record) when it is changed.

    SesMonitorLoc

    SMALLINT, NOT NULL, range 0-3600 seconds,

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

    A rate of zero allows SesMonitorSys to override the current local rate for that session.

    This rate can be changed with the SET SESSION RATE request by specifying the a LOCAL rate change option and can be saved on disk and may be lost during a system outage.

    ResLogging

    SMALLINT, NOT NULL,
    range 0- 3600 seconds

    Interval in seconds at which resource usage data is written to one or more active resource usage database tables.

    ResMonitor

    SMALLINT,
    NOT NULL

    Interval in seconds at which all resource usage data is collected in memory for reporting via the PM/API.

    Release

    VARCHAR (29),
    NOT NULL

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

    This value is supplied by Teradata Database.

    Version

    VARCHAR (32),
    NOT NULL

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

    This value is supplied by Teradata Database.

    CollectionDate

    DATE,
    NOT NULL

    Date the Virtual Resource cache was last refreshed.

    CollectionTime

    FLOAT,
    NOT NULL

    Time the Virtual Resource cache was last refreshed.

    This example shows how the parcels for a MONITOR VIRTUAL SUMMARY request, built by CLIv2, look when sent to the Teradata Database. The size of the response buffer in the example 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

    Length

     

    Field

    Value

    0001

    Req

    27

    Request

    MONITOR VIRTUAL SUMMARY

    0003

    Data

    6

    MonVerID

    2

    0004

    Resp

    6

    BufferSize

    64000

    For an example of how the PM/API request, built in Java, appears when sent to the Teradata Database server, see Teradata JDBC Driver Reference.

    This example shows the values returned in character text format for the MONITOR VIRTUAL SUMMARY request. Your application program may display returned values in a different format.

    Success parcel: 
    StatementNo=1,    ActivityCount=1,ActivityType=93, FieldCount=37
     
    AMPAvgCPU:      0.00   AMPAvgDisk:    30.39   AMPAvgDiskIO:  3444.25
     
    HiCPUAMPUse:    0.00   HiDiskAMP:     31.13   HiDiskAMPIO:   3448.00
    HiCPUAMPNo:        3   HiDiskAMPNo:       2   HiDiskAMPIONo:       3
    HiCPUAMPProc:     33   HiDiskAMPProc:    33   HiDiskAMPIOProc:    33
     
    LoCPUAMPUse:    0.00   LoDiskAMP:     29.74   LoDiskAMPIO:   3440.00
    LoCPUAMPNo:        0   LoDiskAMPNo:       0   LoDiskAMPIONo:       1
    LoCPUAMPProc:     33   LoDiskAMPProc:    33   LoDiskAMPIOProc:    33
     
    PEAvgCPU:       0.00
    HiCPUPEUse:     0.00   LoCPUPEUse:     0.00
    HiCPUPENo:     16383   LoCPUPENo:     16383
    HiCPUPEProc:      33   LoCPUPEProc:      33
     
    SessionCnt:     2.00
     
    SesMonitorSys:     1   SesMonitorLoc:     0
    VprocLogging:    600   VprocMonitor:     30
     
    Release: 14f.00.00.249 Version: 14f.00.00.249
     
    Collection Date/Time:  06/15/2011 18:30:31.00

    All users who are logged on and issue a MONITOR VIRTUAL SUMMARY request after a system restart or after the last rate change can expect to receive one of the warnings. Typically, the types of situations that can produce warning messages are:

  • 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 SUMMARY 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 SUMMARY request, many of the values returned are NULL.

  • If the resource monitoring rate (ResMonitor) is not enabled, that is, if rate is set to zero. When the user issues the next MONITOR VIRTUAL SUMMARY request, many of the values returned are NULL.
  • The SET RESOURCE RATE request sets the ResMonitor and ResLogging rates, which are among the responses returned by the MONITOR PHYSICAL SUMMARY or MONITOR VIRTUAL SUMMARY request. Any change to either the ResMonitor or ResLogging rate results in changes in the corresponding response returned by the MONITOR VIRTUAL SUMMARY or MONITOR PHYSICAL SUMMARY request.

    You must set ResMonitor to a nonzero rate for MONITOR PHYSICAL SUMMARY or MONITOR VIRTUAL SUMMARY to return meaningful resource utilization data. A zero ResMonitor rate returns NULL for resource utilization information.

    Changes to the session‑level rates (global and local) specified by SET SESSION RATE are reported in the data returned by MONITOR PHYSICAL SUMMARY or MONITOR VIRTUAL SUMMARY.

    Note: The local rate reported is your own local rate. If the local rate is not set, the local rate is reported as zero.

    As more session‑level monitoring is done (by setting a faster SET SESSION RATE), the resulting overhead may increase the level of CPU usage (reported in MONITOR PHYSICAL SUMMARY or MONITOR VIRTUAL SUMMARY data) by your system. However, this may depend on the size of the rate change and the type of work done by other sessions.

    Use MONITOR VIRTUAL SUMMARY with the MONITOR VIRTUAL CONFIG request for an overall system status. These are low overhead requests.

  • Execute the MONITOR VIRTUAL SUMMARY request every 5 or 10 minutes for a low-cost, continuous monitoring of your system.
  • Execute the MONITOR VIRTUAL CONFIG request to get a picture of your system configuration at defined times, such as at the beginning of a day, various times during the day, or when the system is down.
  • For information on this PMPC PM/API request relationship, see “Relationship Between MONITOR VIRTUAL CONFIG and MONITOR VIRTUAL SUMMARY” on page 170.