Types of Tasks
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 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.
Care should be taken to integrate the various performance monitoring tasks on your system to avoid potential conflicts.
- The SET RESOURCE RATE and SET SESSION RATE requests.
- The SetResourceRate and SetSessionRate functions.
|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.
|For ...||See ...|
|information on global and local rates|
|information on resource monitoring and logging rates|
|comparative information on setting logging rates||
|comparative information on setting collection rates||
- 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.
- Identification of blocking users, sessions and locked databases or tables
- Session-level usage data on:
- 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.
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
- 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.
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 for Teradata Database to use features available in the request as shown in the example below.
- 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 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.
- 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.
- 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.
- 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.
- 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 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.
- 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.
|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.|
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||
|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:
||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|