DBQL behavior resulting from the SUMMARY option is unique in that:
- No default rows are generated to DBQLogTbl for summarized queries.
- You define the summary criteria and then DBQL summarizes queries into those buckets. Each bucket counts the number of queries for the session that fall into that bucket and also sums up their elapsed time, I/O count and various CPU times.
- DBQL maintains summary counters in cache. The contents are committed to the DBQLSummaryTbl table every 10 minutes, when the cache is flushed.
The following table describes the summary criteria that make up the buckets.
|Using the following modifier on the SUMMARY Option…||Means the system logs the values specified as…|
|CPUTIME||hundredths of a second of AMP CPU time. So for example:
BEGIN QUERY LOGGING LIMIT SUMMARY=5, 10, 15 CPUTIME ON user1;
means counting queries based on CPU time where 5 is 0.05 seconds of CPU time, 10 is 0.10 seconds and 15 is 0.15 seconds of CPU time.
|CPUTIMENORM||hundredths of a second of normalized AMP CPU time. This is similar to CPUTIME above, but uses the times from a co-existence system that are normalized.|
|ELAPSEDSEC||Without an modifier to the SUMMARY option:
BEGIN QUERY LOGGING LIMIT SUMMARY=5, 10, 15 ON user1;
elapsed seconds is used. You can explicitly set elapsed seconds as a criteria by using the following statement:
BEGIN QUERY LOGGING LIMIT SUMMARY=5, 10, 15 ELAPSEDSEC ON user1;
|ELAPSEDTIME||elapsed time where n is specified in hundredths of a second.|
|IOCOUNT||the number of logical I/Os used. For example:
BEGIN QUERY LOGGING LIMIT SUMMARY=1000,5000,10000 IOCOUNT ON ALL;
DBQL gathers information for the collection interval (the default is every 10 minutes). For every collection interval and every active session at the end of the interval, there is as many as four rows per session containing the query count, the number of seconds of elapsed time for those queries, the amount of CPU time and the number of logical I/Os. Each row represents one of four possible buckets of information.
In the default case, where elapsed time is used to qualify a query for one of the four buckets, the following buckets are possible:
- 0 to 5 seconds
- Longer than 5 seconds to 10 seconds
- Longer than 10 seconds to 15 seconds
- Longer than 15 seconds
For example, if during that next logging period for the user, two queries ran under 5 seconds, three queries ran for 7 seconds, and one query ran for over 15 seconds, the following rows would be written to DBQLSummaryTbl for the session:
COUNT SECONDS LOWHIST HIGHHIST ----- ------- ------- -------- 2 1 0 5 3 21 5 10 1 200 15 4294967295
For this example, there were:
- No queries between 10 and 15 seconds
- To determine the average time for each query counted, divide SECONDS by COUNT (for example, the two queries in the first row averaged 0.5 seconds each; the three queries in the second row averaged 7 seconds each).
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.