System PMPC API Features | Application Programming Reference | Teradata Vantage - System PMPC API Features - Advanced SQL Engine - Teradata Database

Application Programming Reference

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
hvk1593628831140.ditamap
dita:ditavalPath
hvk1593628831140.ditaval
dita:id
B035-1090
lifecycle
previous
Product Category
Teradata Vantage™

Types of Tasks

The following types of tasks are tracked in Resource Usage tables:

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 Teradata Vantage™ - Resource Usage Macros and Tables, B035-1099.

A single master resource collection system within the 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.

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.

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.

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.

Related Information

For ... See ...
information on global and local rates
information on resource monitoring and logging rates
comparative information on setting logging rates
  • Control GDO Editor (ctl) information in Teradata Vantage™ - Database Utilities, B035-1102.
  • Teradata Vantage™ - Resource Usage Macros and Tables, B035-1099.
  • Teradata® Viewpoint User Guide, B035-2206
comparative information on setting collection rates
  • Database Window (xdbw) information in Teradata Vantage™ - Database Utilities, B035-1102.
  • Teradata® Viewpoint User Guide, B035-2206.

System-Level Monitoring

Use System PMPC 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 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 System PMPC APIs.

Examples of Job Control Support Applications Using PM/APIs

This section explains two advanced examples of potential job control support applications that use PM/API requests:
  • Resource Supervisor
  • Idle Session Logoff

They are explained at a high level so that, by understanding the concepts, you can develop similar applications at a customer site for monitoring and controlling the use of database resources.

Resource Supervisor

A Resource Supervisor prevents runaway queries. Runaway queries are sometimes a problem at a site where end users can access the database to make ad hoc Teradata SQL requests. A badly formulated query (for example, one missing constraint on a WHERE clause) could inadvertently cause a product join, which consumes more resources than the user intended. Further, a user making an ill-formed SQL statement might request a join on two big tables, which unintentionally results in a Cartesian product join. The Resource Supervisor aborts transactions that exceed a certain resource usage threshold.

You can write a Resource Supervisor to use features available in the request as shown in the example below.

  1. Program the SET SESSION RATE request to set a reasonable session-level collection rate, for example, 10 minutes.
  2. Based on the session-level rate, program the client application to issue a MONITOR SESSION request for all sessions or for a subset of sessions (for example, if users from a specific client are the only ones to be governed).
  3. For each session returned to the client, program the client application to check some site-specific criteria to see if the session is a candidate for the Resource Supervisor.

    For example, interactive users are required to have INTERACTIVE as the first word of their account string. If only interactive users are to be monitored by the Resource Supervisor, all sessions that do not include INTERACTIVE as the first word of the UserAccount value returned by a MONITOR SESSION request are ignored.

  4. For those sessions that are candidates for the Resource Supervisor, program the client application to look at the AMPCPUSec, PECPUSec, and AMPIO values to determine if some site-specific maximum acceptable value has been exceeded.

    These session values are cumulative and may not be appropriate for use as a Governor limit because you are limiting the total resource usage of a session and not of a request.

  5. Program the client application to keep a history of all previous cumulative values of AMPCPUSec, PECPUSec, and AMPIO, plus current XactCount (transaction count) and ReqCount (request count) values for each session.

    The difference between the historical value and the current value tells you the resources used. The request count and transaction count values tell you if they are consumed as part of the current transaction or as part of the new request.

  6. If the Resource Supervisor determines that a particular session has exceeded site-specific limits, program the client application to issue an ABORT SESSION request for those session that have exceeded the limits.

    The client application can specify the logoff option for the ABORT SESSION request, depending on how severely the offending session is controlled.

Idle Session Logoff

An Idle Session Logoff application automatically logs off users whose sessions have been idle for a certain length of time. This job control support feature prevents users from walking away from a terminal and allowing unauthorized users access to sensitive information.

You can write an Idle Session Logoff application program using the requests described below.

  1. Program the SET SESSION RATE request to set a reasonable session-level collection rate, for example, 10 minutes.
  2. Based on the session-level rate, program the client application to issue a MONITOR SESSION request for all sessions or for a subset of sessions (for example, if users from a specific client are the only ones to be monitored for idle sessions).
  3. For each session returned to the client, check some site-specific criteria to see if the session is a candidate for Idle Session Logoff.

    For example, interactive users are required to have INTERACTIVE as the first word of their account string. If only interactive users are to be monitored by the Resource Supervisor, all sessions that did not have INTERACTIVE as the first word of the UserAccount value returned by a MONITOR SESSION request are ignored. Those sessions with the INTERACTIVE label proceed through the next step.

  4. For sessions that are candidates for Idle Session Logoff, program the client application to verify the following conditions to determine whether the session has been inactive for the duration of the collection period:
    • AMPState and PEState are idle.
    • The session was idle during the last MONITOR SESSION request.
    • XactCount and ReqCount values did not change during the last MONITOR SESSION request.

    If all these conditions are met, the session has been inactive for the duration of the collection interval and is a candidate for automatic logoff.

  5. Program the client application to issue an ABORT SESSION request with the logoff option for the sessions that are candidates for automatic logoff.

Examples Using Open APIs

The following table describes the different uses of the System PMPC open APIs.

You can ... To ...
execute the MonitorSession and AbortSessions functions create a query that aborts queries submitted by a set of users that have been running longer than 10 minutes and have been skewed by more than 30% for 20 minutes.
execute the MonitorSession and SetSessionAccount functions change the account string.
execute the MonitorSession or MonitorSQLText functions display the SQL of all active sessions that have run over 20 minutes.
select the blocked fields of the MonitorSession function display the block information of all blocked sessions.

Functionality

The following table describes the System PMPC interfaces that are used to show how efficiently the database is using its resources, to identify problem sessions and users, and to abort sessions and users having a negative impact on system performance.

If you want to ... Use the following SQL interface... OR use the following CLIv2 or Teradata JDBC Driver request ...
abort any outstanding requests or transactions of one or more sessions AbortSessions

or

AbortListSessions

ABORT SESSION
return the name of a user, by session, who is causing a block IdentifySession IDENTIFY
return the name of the specified table ID IdentifyTable IDENTIFY
return the name of the specified user ID who is causing a block IdentifyUser IDENTIFY
collect statistics on AMPs based on the in-use AMP Worker Tasks (AWTs) MonitorAMPLoad or MonitorAWTResource MONITOR AWT RESOURCE
collect session information for the current user on the current host MonitorMySessions
collect overall information on node availability MonitorPhysicalConfig MONITOR PHYSICAL CONFIG
collect RSS data and returns node-specific data MonitorPhysicalResource MONITOR PHYSICAL RESOURCE
collect global summary information MonitorPhysicalSummary MONITOR PHYSICAL SUMMARY
return session or request resource usage statistics MonitorSession MONITOR SESSION
return session rate MonitorSessionRate MONITOR SESSION
return data about the step being executed of the currently running request MonitorSQLCurrentStep MONITOR SQL
return the step information of the current or running request MonitorSQLSteps MONITOR SQL
return the SQL text of the request currently being executed for the specified host, session, and vproc MonitorSQLText MONITOR SQL
return BYNET status and system type values that are generated once for the entire system or collect overall information on node availability MonitorSystemPhysicalConfig MONITOR PHYSICAL CONFIG
collect information on virtual processor (vproc) availability MonitorVirtualConfig MONITOR VIRTUAL CONFIG
collect performance information for each AMP, PE, or TVS vproc MonitorVirtualResource MONITOR VIRTUAL RESOURCE
collect global summary information on system utilization MonitorVirtualSummary MONITOR VIRTUAL SUMMARY
return ResUsageSps data from the RSS SPS memory buffer MonitorWD MONITOR WD
return a subset of the RSS ResUsageSps data or return the collection rate, number of nodes with at least one online AMP, and number of nodes with at least one online PE MonitorWDRate MONITOR WD
set either the:
  • ResMonitor rate
  • ResLogging rate
SetResourceRate SET RESOURCE RATE
change the account string for the session or for the request. SetSessionAccount SET SESSION ACCOUNT
set the global and local rates for updating session-level statistics in memory SetSessionRate SET SESSION RATE