LIMIT Logging Options - Teradata Database

Parameter

Logging Behavior

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:

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” on page 329.

The LIMIT SUMMARY option:

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

LIMIT SUMMARY
=n1, n2, n3 [units]
(continued)

Use the unit of…

To specify summary data as based on…

CPUTIME

AMP CPU time, where n1, n2, n3 are specified in hundredths of a second.

Note: Parser CPU Time is reported, but it is not used to place query data into summary buckets.

CPUTIMENORM

normalized AMP CPU time, where n1, n2, n3 are specified in hundredths of a second.

Note: 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.

Note: 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” on page 329.

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:

Use the unit of…

To specify threshold data as based on…

CPUTIME

AMP CPU time, where n is specified in hundredths of a second.

Note: Parser CPU Time is reported, but it is not used to place query data into summary buckets.

CPUTIMENORM

normalized AMP CPU time, where n is specified in hundredths of a second.

Note: 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 (continued)

LIMIT THRESHOLD can be used with these logging options:

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.

Note: 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:

IF a query …

THEN DBQL...

completes at or under the threshold elapsed time, CPU seconds, or I/O count

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.