DBC.AMPUsage View - Teradata Vantage

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 Purpose of System Accounting).

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.

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$ABCD24053016       6,336.76          505,636
DBA01        $M2$EFGH24053016       4,387.14          303,733
DBA01        $M2$IJKL24053016       1.28              166