Rows Generated by the SUMMARY Option

Rows Generated by the SUMMARY Option - Teradata Database

Teradata Database Administration

Product

Teradata Database

Release Number

15.10

Language

English (United States)

Last Update

2018-10-06

Product Category

Software

Rows Generated by the SUMMARY Option

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.

Row Values

The following table lists the fields available for population in DBQLSummaryTbl rows.

DBC.DBQLSummaryTbl Field	Description
AcctString	The unexpanded account name under which the query is submitted.
AMPCPUTime	Total AMP CPU seconds.
AMPCPUTimeNorm	Normalized AMP CPU seconds for co-existence systems only.
AppID	The application ID under which the query is submitted.
ClientAddr	The client IP address of the submitted query.
ClientID	The client ID under which the query is submitted.
CollectTimeStamp	Time that the row was inserted into the database. This field is also part of the multi-column NUPI (also see ProcID). For more information on CollectTimeStamp, see “Comparing CollectTimeStamp Value Between Tables” on page 380.
HighHist	Highest value for the SUMMARY option. Zero for the THRESHOLD option. If the modifiers CPUTIME or IOCOUNT are used, then this value is the highest CPU seconds or I/O counts set by the user. Use CPUTIMENORM for hundredths of a second of normalized CPU time. To measure subsecond queries, use ELAPSEDTIME. The default, elapsed time in seconds, can be explicitly specified with ELAPSEDSEC.
LogicalHostID	Unique identifier of the logon source for the logged query. A value of 0 indicates an internal session.
LowHist	Lowest value for the SUMMARY option. For the THRESHOLD option, this is the threshold time set by the user. The default time is 5. If the modifiers CPUTIME or IOCOUNT are used, then this value is the lowest CPU seconds or I/O counts set by the user. Use CPUTIMENORM for hundredths of a second of normalized CPU time. To measure subsecond queries, use ELAPSEDTIME. The default, elapsed time in seconds, can be explicitly specified with ELAPSEDSEC.
ParserCPUTime	CPU seconds to parse queries.
ParserCPUTimeNorm	Normalized CPU seconds to parse queries. Note: Normalized columns, such as ParserCPUTimeNorm, are for co-existence systems only.
ProcID	Unique processor ID of the Dispatcher and part of the multi-column NUPI (also see CollectTimeStamp).
ProfileID	The profile ID under which the user is submitting queries.
QueryCount	Number of queries run in the last 10 minutes.
QuerySeconds	Total run time of queries in this period for this histogram. Calculated to the nearest hundredth of a second.
SessionID	Identifies the session for which queries are being summarized (counted).
TotalIOCount	The total logical I/O count. Note: Logical I/O and CPU may not be accurate for aborted queries.
UserID	The unique ID of the user whose query is logged.
ValueType	The THRESHOLD criterion is as follows: C = AMP CPU seconds H = elapsed hundredths of a second I = I/O count N = Normalized AMP CPU seconds S = elapsed time in seconds.