16.10 - Rows Generated by the THRESHOLD Option - Teradata Database

Teradata Database Administration

Product
Teradata Database
Release Number
16.10
Release Date
April 2018
Content Type
Administration
Publication ID
B035-1093-161K
Language
English (United States)

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