LIMIT Logging Options - Advanced SQL Engine - Teradata Database

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.

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:

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.

To group queries based on I/O, submit:

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

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:

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.

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: