15.10 - System PMPC API Features - Teradata Database

Teradata Database Application Programming Reference

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

Data Collection

System PMPC requests, except MONITOR VERSION and MONITOR SQL, are based on periodic data collection. The Resource Sampling Subsystem (RSS) rates at which the resource data is gathered (collection rate) and written to the resource usage tables (logging rate) are set separately.

You can control the session collection rate, the resource collection rate, and the resource logging rate. You can set the resource collection rate for any interval between 0 and 3600 seconds. You can set the session collection rate for any interval between 1 and 3600 seconds. For other rules governing logging rates, see Resource Usage Macros and Tables.

A single master resource collection system within the Teradata Database collects all performance monitoring data. It can be accessed in a number of ways, such as using PM/API requests or SQL interfaces. Collection rates that are set this way can be reset by using the Database Window utility or Teradata Viewpoint.

Note: You cannot set the session rate to zero in SET SESSION RATE PM/API or SetSessionRate Open API; however, it can be set to zero in the Supervisor Window.

Care should be taken to integrate the various performance monitoring tasks on your system to avoid potential conflicts.

Note: Because resource usage data is collected in different memory repositories than session-level data, changes in the resource collection rate have no impact on session-level usage data, and vice versa.

The following table describes the various types of monitoring rates that are set using the following APIs:

  • The SET RESOURCE RATE and SET SESSION RATE requests.
  • The SetResourceRate and SetSessionRate functions.
  •  

    Rate

    Description

    Global session (SesMonitorSys) monitoring

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

    This rate is returned as SesMonitorSys value in a MONITOR VIRTUAL SUMMARY request.

    The global session rate impacts all MONITOR SESSION requests unless local session rate is set.

    Local session (SesMonitorLoc) monitoring

    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 returned as SesMonitorLoc value in a MONITOR VIRTUAL SUMMARY request.

    By default the local session rate is the same as the global session rate.

    Note: A change to the local collection rate could affect the cumulative data that other users see because all session usage data is stored in the same memory repository.

    Because changes to either the global or local rate can reset the starting point at which data is collected and may alter cumulative session usage data, it is important to restrict the granting of session monitoring privilege to users trained in the use of system monitoring tools, for example, the system or database administrator or certain application programmers.

    This rate is not saved on disk and is lost during a system outage.

    Resource monitoring (ResMonitor)

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

    The resource monitoring rate is returned as a ResMonitor data value in a MONITOR PHYSICAL SUMMARY or MONITOR VIRTUAL SUMMARY request.

    You can use the SampleSec field of MONITOR PHYSICAL RESOURCE to view the current rate. This field is equivalent to the ResMonitor field.

    Resource logging (ResLogging)

    Sets the interval in seconds at which resource usage data is written to the resource usage tables.

    The resource logging rate is returned as a ResLogging data value in a MONITOR PHYSICAL SUMMARY or MONITOR VIRTUAL SUMMARY request.

  • Data collection rates must be set to a nonzero value for all data fields called by a PM/API request or SQL interface or the fields will not contain any data.
  • Because all rates, except for the local session monitoring rate, are saved on disk every time they are altered, they are “remembered” during restarts.
  •  

    For ...

    See ...

    information on global and local rates

  • “SET SESSION RATE” on page 214.
  • “MONITOR VIRTUAL SUMMARY” on page 185.
  • information on resource monitoring and logging rates

  • “SET RESOURCE RATE” on page 205.
  • “MONITOR PHYSICAL SUMMARY” on page 103.
  • “MONITOR VIRTUAL SUMMARY” on page 185.
  • comparative information on setting logging rates

  • “Control GDO Editor (ctl)” in Utilities.
  • Resource Usage Macros and Tables.
  • Teradata Viewpoint User Guide
  • comparative information on setting collection rates

  • “Database Window (xdbw)” in Utilities.
  • Teradata Viewpoint User Guide.
  • System-Level Monitoring

    System PMPC allows you to perform two types of system monitoring:

  • Physical resources
  • Nodes availability
  • BYNET availability
  • Virtual resources (vprocs)
  • Access Module Processor (AMP) status, performance and utilization
  • Parsing Engine (PE) status, performance and utilization
  • Resource utilization data is collected and reported differently from session utilization data. Whereas some of the session usage data is collected cumulatively, resource data is collected for a particular collection period. The resource data reported is based on the activity that occurred during that collection period and does not include any cumulative data over collection periods. For example, if you set the resource usage collection interval to 60 seconds and issue a MONITOR VIRTUAL RESOURCE request (or a MonitorVirtualResource function) or MONITOR PHYSICAL RESOURCE request (or MonitorPhysicalResource function), a report is issued for that specific 60-second interval.

    Any data you do not examine within the 60 seconds is lost when it is overwritten by data collected during the next 60-second collection interval.

    Resource usage data and session-level usage data are deposited in separate global data collection areas. The data in the repository is updated once each collection period. All users share the data, which is used to generate responses.

    Session-Level Monitoring

    Session-level monitoring tasks return the following information:

  • Identification of blocking users, sessions and locked databases or tables
  • Session-level usage data on:
  • AMPs
  • CPUs
  • Identification of problem SQL requests, including:
  • Current session
  • Current step
  • SQL text EXPLAIN data
  • Some of the session-level utilization data is collected cumulatively. The session rate is used to limit the frequency at which cumulative data is updated. For example, if you set the session rate to 60 seconds and issue a MONITOR SESSION request every 60 seconds, session-level usage data and request-level usage data is cumulatively totaled and updated every 60 seconds. Cumulative type session-level or request-level data reported includes data from the beginning of the session or request.

    Monitor Locks

    Locks may occur when sessions, utilities, and applications being run by specific users block access to databases or tables normally available from the Teradata Database. Interfaces to System PMPC can help you monitor locks.

    To help determine the user causing a block and the locked database or table, you can use the MONITOR SESSION request or the MonitorSession function. Then, to get more specific information about the blocking session and the object being blocked, you can use the IDENTIFY request or IdentifySession, IdentifyUser or IdentifyTable functions.

    To learn more about the interfaces used to perform these functions, see Chapter 4: “System PMPC APIs.”