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