17.00 - Rows Generated by the THRESHOLD Option - Teradata Database

Teradata Vantage™ - Data Dictionary

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
created_date
June 2020
category
Administration
Programming Reference
featnum
B035-1092-170K

If a threshold value is used, the DBQLSummaryTbl will log queries based on the criterion of elapsed seconds, elapsed hundredths, normalized time, logical I/O count, or AMP CPU hundredths of a second. The ValueType column in DBQLSummaryTbl tells which criterion are used.

Queries with values less than or equal to the given limit will be counted in the DBQLSummaryTbl. Queries with more than the given limit will be logged in DBQLogTbl.

For example, if the criterion is elapsed seconds, for each session being logged, DBQL with the THRESHOLD option does the following:

  • Each time a query completes within the threshold time, increments the counters for the session
  • Every 10 minutes, writes the cumulative count for each session as a separate row in DBQLSummaryTbl
  • DBQL generates a default row in DBQLogTbl for queries that exceed the threshold time

For example, if you specified “LIMIT THRESHOLD=500 CPUTIME ON user1” you could find out how many queries user1 submitted that required more than 5 CPU seconds.

The following is an example of how to query the DBQLSummaryTbl table, using the QryLogSummary[V] view, to find the total I/O count and total CPU time:

SELECT collecttimestamp, sessionid, querycount, ampcputime, totaliocount
FROM QryLogSummaryV ORDER BY collecttimestamp;

CollectTimeStamp    SessionID    QueryCount  AMPCPUTime  TotalIOCount
------------------  ---------  ------------  ----------  ------------
2006-07-29 10:30:05     1,001             5        .031             4
2006-07-29 11:07:10     1,001             1        .015             0
2006-07-29 11:07:10     1,013             1        .000             0
2006-07-29 11:07:10     1,000             2        .047             0
2006-07-29 11:07:10     1,014            71        .907         2,427
2006-07-29 11:17:10     1,017            96       1.234         2,983
2006-07-29 11:17:10     1,014            26        .329           552
2006-07-29 11:17:10     1,031             1        .016             0
2006-07-29 11:17:10     1,023            94       1.093         2,483
2006-07-29 11:17:10     1,026            42        .578         1,196