DBC.AMPUsage View - Analytics Database - Teradata Vantage

Database Administration
Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
ft:locale
en-US
ft:lastEdition
2024-10-04
dita:mapPath
pgf1628096104492.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
ujp1472240543947
lifecycle
latest
Product Category
Teradata Vantage™
The DBC.AMPUsage view provides information about the usage of each AMP for each user and account. It also tracks the activities of any console utilities. By user, account, or console utility session, DBC.AMPUsage stores information about:
  • CPU seconds consumed
  • Number of read/write (I/O) operations generated
AMPUsage reports logical I/Os explicitly requested by the database software, even if the requested segment is in cache and no physical I/O is performed.

DBC.AMPUsage uses the DBC.Acctg table to provide aggregated information by username, account ID, and AMP. Updates to the table are made periodically during each AMP step on each processor affected by the step. (This means if there are long-running steps, AMPUsage numbers show large increases periodically, instead of continuous incremental additions.) The data is collected and continually added to what is already in the table until you reset the counters to zero (see Resetting the Resource Usage Counters).

You can use the information provided by DBC.AMPUsage to do the following:
  • Bill an account for system resource use.
  • Determine what resources were used, by user and account string, after hours as well as during the day.
  • Summarize and archive the information, then zero it out on a per shift, per day, or per week basis.
  • Determine which session caused reduced performance (in-depth analysis can be performed with DBQL data).
  • Derive capacity needs to plan for expansion.

DBC.AmpUsage does not record the activity of parsing the query, or of processing on a query basis.

You can use query logging to capture query text, step information, and elapsed processing time, and to differentiate queries submitted by SQL-generating products that do not provide a variety of user IDs and account ids in the logon string. For instructions and a description of the data capture options, see Tracking Query Behavior with Database Query Logging: Operational DBAs.

Use Teradata Viewpoint for a look at up-to-the-moment activity in real-time.

Example: Totaling CPU Time and I/O by User

This SQL statement requests totals for CPU time and I/O for user DBA01.

The totals are aggregates of all resources.

SELECT UserName (FORMAT 'X (16)')
,AccountName (FORMAT 'X (12)')
,SUM (CpuTime)
,SUM (DiskIO)
FROM DBC.AMPUsage
WHERE UserName = 'DBA01'
GROUP BY 1, 2
ORDER BY 3 DESC ;

For this example, AMPUsage returns the following rows:

UserName     AccountName        SUM (CpuTime)     SUM (DiskIO)
--------     --------------     -------------     ------------
DBA01        $M2$ABCD&D&H       6,336.76          505,636
DBA01        $M2$EFGH&D&H       4,387.14          303,733
DBA01        $M2$IJKL&D&H       1.28              166

For detailed information on these and all the system views, see Teradata Vantage™ - Data Dictionary, B035-1092. For more information on how to use DBC.AMPUsage and other views to find problems and improve performance, see ASE Impact on PE and AMP Performance.