Rows Generated by the THRESHOLD Option - Teradata Database

Rows Generated by the THRESHOLD Option

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