Types of Tasks
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.
A master resource collection system within the database collects performance monitoring data, which can be accessed in multiple ways, such as using PM/API requests or SQL interfaces.
Integrate performance monitoring tasks on your system to avoid potential conflicts.
- 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 may affect the cumulative data that other users see because all session usage data is stored in the same memory repository.
Because changes to the global or local rate can reset the starting point at which data is collected and may alter cumulative session usage data, 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 using 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 contain no data.
- All rates but the local session monitoring rate are saved on disk when altered, and are therefore "remembered" during restarts.
Related Information
| Topic | Reference |
|---|---|
| Information on global and local rates | |
| Information on resource monitoring and logging rates |
System-Level Monitoring
- Physical resources
- Nodes availability
- BYNET availability
- Virtual resources (vprocs)
- Access Module Processor (AMP) status, performance, and usage
- Parsing Engine (PE) status, performance, and usage
Resource usage data is collected and reported differently from session usage data. Whereas session usage data can be collected cumulatively, resource data is collected for a 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.
Data you do not examine within the 60 seconds is lost when 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
- 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
There is session-level usage data that 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 typically 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: Job Control Support Applications Using PM/APIs
- Resource Supervisor
- Idle Session Logoff
The examples 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 can be 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) may cause a product join, which consumes more resources than the user intended. Further, a user making an ill-formed SQL statement may request a join on two big tables, which unintentionally causes a Cartesian product join. The Resource Supervisor aborts transactions that exceed a certain resource usage threshold.
- Program the SET SESSION RATE request to set a reasonable session-level collection rate, for example, 10 minutes.
- 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).
- For each session returned to the client, program the client application to check 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.
- For sessions that are candidates for the Resource Supervisor, program the client application to look at the AMPCPUSec, PECPUSec, and AMPIO values to determine if a 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.
- 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 the resources are consumed as part of the current transaction or as part of the new request.
- If the Resource Supervisor determines that a 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.
- Program the SET SESSION RATE request to set a reasonable session-level collection rate, for example, 10 minutes.
- 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).
- For each session returned to the client, check 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.
- 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.
- 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.
| Functions | Usage |
|---|---|
|
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. |
|
Change the account string. |
|
Display the SQL of all active sessions that have run over 20 minutes. |
Select the blocked fields of the MonitorSession function to display the block information of all blocked sessions.
Functionality
The following table describes the System PMPC interfaces used to show how efficiently the database uses its resources, to identify problem sessions and users, and to stop sessions and users having a negative impact on system performance.
| Goal | SQL Interface to Use | CLIv2 or Teradata JDBC Driver Request to Use |
|---|---|---|
| Abort outstanding requests or transactions of one or more sessions | AbortSessions or |
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 run of the 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 being run 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 usage | 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 ResMonitor or 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 |