RESOURCE USAGE DATA/TABLES IN TERADATA
In this article ,we will see RESOURCE USAGE DATA/TABLES IN TERADATA
Resource usage data is stored in system tables and views in the DBC database. Macros installed with Teradata Database generate reports that display the data.
To load the resource usage views and macros, you can run the Database Initialization Program (dip) script
As with other database data, you can access resource usage data using SQL if you have the proper privileges. You can also write your own queries or macros on resource usage data.
- Measuring system performance
- Assisting with on-site job scheduling
- Identifying potential performance impacts
- Planning installation, upgrade, and migration
- Analyzing performance degradation and improvement
- Identifying problems such as bottlenecks, parallel inefficiencies, down components, and congestion
Data is reported at the logging period. When a new logging period starts, the data is gathered in the Gather Buffer, then updated to the Log Buffer and logged to the database resource usage tables.
Resource Usage Macros
Resource usage macros produce reports from data logged to the resource usage tables. They can generate reports for a selected period of time and nodes.
You can use the reports to analyze key operational statistics and evaluate the performance of your system.
Like other macros, resource usage macros consist of one or more Teradata SQL statements stored in Teradata Database and executed by a single EXECUTE statement.
Resource Usage Table Settings
The default resource usage settings provide a good starting point for system monitoring.The default results in the ResUsageSpma (SPMA) table being logged every 10 minutes (600 seconds).
The ResUsageSpma table provides a high level summary of how the system is operating and contains summarized or key elements from most of the other tables. If you want to record detailed statistics covered by any of the resource usage tables, you should enable them for logging, along with specifying the largest logging period that will meet your needs. You should not log data that you do not have a planned need for since this does incur additional database system overhead and uses up additional database space.
The more tables you enable for logging and the shorter the logging period used, the more overhead the system will use.
ResUsageIpma : System-wide node information, intended primarily for Teradata engineers.
ResUsageIvpr : System-wide virtual processor information, intended primarily for Teradata engineers.
ResUsageSawt : Data specific to the AMP Worker Tasks.
When you want to monitor the utilization of the AMP Worker Task and determine if work is backing up because the AMP Worker Tasks are all being used.
ResUsageScpu : Statistics on the CPUs within the nodes.
When the performance analysis suggests that the overall performance is limited or to check if a program is spinning in an infinite loop on an individual processor.
For example, saturation of a particular CPU on each node or on a particular node while others are idle could indicate a task always uses that CPU.
Also, you should enable when the system is first brought online to verify the following:
- That all CPUs are functioning on all nodes
- There is a good load balance among the CPUs
ResUsageShst : Statistics on the host channels and LANs that communicate with Teradata Database.
To determine details about the traffic over the IBM Host channels and if there is a bottleneck.
ResUsageSldv : System-wide, logical device statistics collected from the storage driver.
To observe the balance of disk usage. The storage device statistics are often difficult to interpret with disk arrays attached due to multi-path access to disks.
ResUsageSpdsk : Statistics collected from the pdisk device.
To obtain detailed usage information about pdisks.
ResUsageSpma : System-wide node information provides a summary of overall system utilization incorporating the essential information from most of the other tables.
Use the columns in ResUsageSpma to view BYNET utilization.
To provide an overall history of the system operation.
ResUsageSps : Statistics based on the WD the work is being performed for
When you need to track utilization by the WD level.
ResUsageSvpr : Data specific to each virtual processor and its file system
To view details about the resources being used by each vproc on the system. This table is useful for looking for hot AMPS or PEs that may be CPU bound or throttled on other resources.