16.10 - LIMIT Logging Options - 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)

The following options control how much and when the data gets logged into DBQLogTbl and DBQLSummaryTbl.

LIMIT SQLTEXT=n

This option determines how much SQL text to capture for queries logged to DBQLogTbl.

Use this option to capture less than or more than the automatic first 200 characters in the default row. To turn off text capture in DBQLogTbl completely, specify 0. Specify LIMIT SQLTEXT=n where n is some value greater than 0 to control the amount of SQL text to be recorded in DBC.DBQLogTbl. For example, to log 500 characters of the SQL, submit the following:

BEGIN QUERY LOGGING LIMIT SQLTEXT=500 ON USER2;

The maximum is 10,000 characters. If you specify LIMIT SQLTEXT but do not specify a value, up to 10,000 characters are logged in DBQLogTbl.

To store the complete statement regardless of length, specify the WITH SQL option; the system logs as many rows as needed to contain the full text in DBQLSQLTbl.

If you specify the WITH SQL option and log to the DBQLSQLTbl, you may define LIMIT SQLTEXT=0 to avoid redundant logging in both the default row and DBQLSQLTbl.

LIMIT SUMMARY=n1, n2, n3 (units)

Use this option to group queries by duration intervals or number of I/Os, count each group, and store the count results in DBQLSummaryTbl. You can specify up to 3 intervals. A fourth interval is created by default as anything longer than n3.

If you specify all three intervals, the intervals are:

  • 0-n1
  • n1-n2
  • n2-n3
  • >n3

SUMMARY is useful for tracking many short queries, such as for OLTP applications, because it does not log to DBQLogTbl. For an output example, see Summary Log Table: DBQLSummaryTbl.

The LIMIT SUMMARY option:

  • Flushes summary information at system-controlled intervals of 10 minutes.
  • Does not write rows if no data has been collected for a summary logging user/account in a 10-minute interval.
  • Cannot be used with LIMIT THRESHOLD.
  • Cannot be used with logging options.

You can define the intervals for LIMIT SUMMARY with one of the following:

Unit Summary Data Count Result
CPUTIME AMP CPU time, where n1, n2, n3 are specified in hundredths of a second.
CPUTIMENORM Normalized AMP CPU time, where n1, n2, n3 are specified in hundredths of a second.
Normalized columns, such as CPUTIMENORM, are for co-existence systems only.
ELAPSEDSEC Elapsed time in seconds. If you do not specify units, ELAPSEDSEC is the default.
ELAPSEDTIME Elapsed time, where n1, n2, n3 are specified in hundredths of a second.
IOCOUNT Count of total I/Os, where n1, n2, n3 are specified in number of I/Os.

If you do not specify units, the summary is based on ELAPSEDSEC.

For example, to group queries based on elapsed time such as 0-1 seconds, 1-2 seconds, 2-3 seconds, and log queries running greater than 3 seconds, submit:

BEGIN QUERY LOGGING LIMIT SUMMARY=1,2,3 ON ALL;

To summarize queries for 0.1, 0.5 and 1.0 seconds of CPU, (that is, four groups: 0-0.1, 0.1-0.5, 0.5-1.0, and greater than 1.0 CPU seconds) submit the following:

BEGIN QUERY LOGGING LIMIT SUMMARY=10,50,100 CPUTIME ON ALL;

To count queries using normalized CPU time rather than “raw” CPU time, use the CPUTIMENORM modifier.

Normalized columns, such as CPUTIMENORM, detect skew more accurately and are for co-existence systems only.

To group queries based on I/O, submit:

BEGIN QUERY LOGGING LIMIT SUMMARY=1000,5000,10000 IOCOUNT ON ALL;

For more information on the DBQL Summary table, see Summary Log Table: DBQLSummaryTbl.

LIMIT THRESHOLD = n [units]

Queries that run in n “units” or less are counted in the DBQLSummaryTbl. Queries that run more than n units are logged in the DBQLogTbl. If you do not specify n, the default is 5 units. Units can be one of the following:

Unit Summary Data Count Result
CPUTIME AMP CPU time, where nis specified in hundredths of a second.
CPUTIMENORM Normalized AMP CPU time, where n is specified in hundredths of a second.
Normalized columns, such as CPUTIMENORM, are for co-existence systems only.
ELAPSEDSEC Elapsed time in seconds. If you do not specify units, ELAPSEDSEC is the default.
ELAPSEDTIME Elapsed time, where n is specified in hundredths of a second.
IOCOUNT Count of total I/Os, where n is specified in number of I/Os.

LIMIT THRESHOLD can be used with these logging options:

  • OBJECTS
  • STEPINFO
  • SQL

The option ELAPSEDTIME is useful for specifying partial seconds.

For example, to log queries that use more than 8 CPU seconds in detail and tally queries that require less than 8 CPU seconds submit the following:

BEGIN QUERY LOGGING LIMIT THRESHOLD=800 CPUTIME ON ALL;

THRESHOLD is specified in hundredths of a second. If you set THRESHOLD=8, this would set the threshold at 0.08 CPU seconds.

The default is 5 hundredths CPU seconds. For example, if you submitted the following statement:

BEGIN QUERY LOGGING LIMIT THRESHOLD CPUTIME ON ALL;

without assigning a numeric value for THRESHOLD, the system defaults to summarizing queries that use less than 0.05 CPU seconds and gives details for all queries using more than 0.05 CPU seconds.

To count queries using normalized CPU time rather than “raw” CPU time, use the CPUTIMENORM modifier.

Normalized columns, like CPUTIMENORM, detect skew more accurately and are for co-existence systems only.

To base the threshold on the number of I/Os and log all queries with more than 5,000 I/Os, use the following:
BEGIN QUERY LOGGING LIMIT THRESHOLD=5000 IOCOUNT ON ALL;

You can even combine THRESHOLD with SQLTEXT to capture more than just the first 200 characters of a query that runs longer than THRESHOLD seconds (because the SQL text of short queries is not logged in DBQLogTbl).

Defining a threshold will determine whether to log a query or just count it, as follows:

Query Duration DBQL Action
Completes at or under the threshold elapsed time, CPU seconds, or I/O count.
  • Increments the query count and adds the elapsed time of the query, CPU time, and I/O counts to the summary row for this session in the current collection period.
  • Stores the final count for the session as a row in DBQLSummaryTbl.
Runs beyond the threshold value. Logs a default row for the query in DBQLSqlTbl, DBQLStepTbl, and DBQLogTbl so you can examine query execution time through time stamps and the number and level of processing steps.