Limit Options for BEGIN QUERY LOGGING - Teradata Vantage

Teradata® VantageCloud Lake
Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
ft:locale
en-US
ft:lastEdition
2024-12-11
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905

The following table documents the SQLTEXT option for BEGIN QUERY LOGGING.

Option Definition
SQLTEXT[=n] Sets the maximum number of SQL text characters to log in the default row. The default value is 200.

The value specified cannot be negative. If you specify 0, then no characters are logged.

If you specify SQLTEXT without specifying a numeric value to limit the number of characters logged, then the entire SQL request is logged up to a maximum of 10,000 characters in DBC.DBQLogTbl. If the request exceeds 10,000 characters, then the excessive characters are not logged.

If you specify either ALL or SQL, SQLTEXT is logged redundantly in both DBC.DBQLogTbl and DBC.DBQLSqlTbl.

Do not specify both ALL and SQLTEXT. If you specify ALL, then set the value for SQLTEXT to 0. Otherwise, the SQL text is redundantly logged in both the DBC.DBQLogTbl and DBC.DBQLSqlTbl tables.

The following tables document the SUMMARY option for BEGIN QUERY LOGGING.

Option Definition
SUMMARY=n1,n2,n3 Designed for use with short, OLTP-like, queries.

You can specify SUMMARY to reduce collection overhead for short running, high-volume queries. You can further specify the units of ELAPSEDTIME, CPUUSAGE, or IOCOUNT.

Counts the number of requests for a session that fall into each of four time intervals or count ranges. Interval values can be specified in CPU time, normalized CPU time, elapsed seconds, elapsed hundredths of seconds, or I/O counts.

If you do not specify a summary unit for the intervals, the default is expressed in elapsed seconds.

If you specify SUMMARY, then you cannot specify any other options.

You must specify the first three intervals explicitly. The fourth interval is created by default.

  • The range for the first interval is from 0 to n1 seconds.
  • The range for the second interval is from n1 to n2 seconds.
  • The range for the third interval is from n2 to n3 seconds.
  • The range for the fourth interval is > n3 seconds.

Counts for each interval are stored in the QueryCount column of four separate rows in DBC.DBQLSummaryTbl.

SUMMARY is the only option whose cache is flushed at regular, system-defined, intervals.

The following table documents the valid SUMMARY option modifiers.

SUMMARY Option Modifier Description
CPUTIME Use this option to set ranges and to summarize counts of the number of requests that fall into a CPU time interval.

The SUMMARY value is expressed in units of 0.01 second.

For example, if you specify 500 for one of the intervals, then the value used to make the determination is 5 CPU seconds.
CPUTIMENORM Use this option to set ranges and to summarize counts of the number of requests that fall into a normalized CPU time interval.

Designed for use with coexistence systems to aid in managing mixed nodes more efficiently, but can be used with any system.

The SUMMARY value is expressed in units of 0.01 second.
ELAPSEDSEC Use this option to set ranges and to summarize counts of the number of requests that fall into an elapsed time interval.

The SUMMARY value is expressed in units of 1.00 seconds.

This is the default.
ELAPSEDTIME Use this option to set ranges and to summarize counts of the number of requests that fall into an elapsed time interval.

The SUMMARY value is expressed in units of 0.01 second, providing finer granularity for elapsed time than ELAPSEDSEC.
IOCOUNT Use this option to set ranges and to summarize counts of the number of requests that fall into an I/O interval.

The following tables document the THRESHOLD option for BEGIN QUERY LOGGING.

Option Definition
THRESHOLD[=n] Sets a threshold (the optional value n specifies the number of units of either seconds or I/O counts for the threshold) that determines whether a query is to be logged fully or only counted. Queries that exceed the threshold are logged, while those that do not meet the threshold are not logged.

You can specify THRESHOLD to reduce collection overhead for short running, high-volume queries. You can further specify the units of ELAPSEDTIME, CPUUSAGE, or IOCOUNT.

You can specify the THRESHOLD limit option with the SQL, STEPINFO, and OBJECTS logging options.

You cannot specify the THRESHOLD limit option with the ALL, EXPLAIN, STATSUSAGE, or XMLPLAN logging options.

If you do not specify a threshold option modifier to specify the logging units, the default is expressed in elapsed seconds.

If you do not specify a THRESHOLD limit or SUMMARY, then all queries are logged fully.

The following restrictions apply to time thresholds only.

  • If a query completes earlier than or equal to the defined threshold value, it is logged only as a count in DBC.DBQLSummaryTbl. The Threshold row in DBC.DBQLSummaryTbl is identified by a HighHist column value of 0.
  • If a query completes later than the defined threshold value, a full entry is logged for it in DBC.DBQLogTbl with values for all columns of the row, as is information for the SQL, STEPINFO, and OBJECTS options if you specify them.
  • If you specify THRESHOLD without also specifying a value for n, then the value 5 seconds is assigned by default.
  • The maximum value for n is 4,294,967,295 seconds (approximately 1,193,046 hours).

The following restriction applies to I/O count thresholds only.

  • The maximum value for n is 32,767 I/O counts.

See the list of valid THRESHOLD option modifiers and their descriptions on the next page.

The following table lists the valid THRESHOLD option modifiers.

THRESHOLD Option Modifier Description
CPUTIME If you do not specify a threshold value for n, then the system uses the default CPUTIME value of 0.05 CPU seconds.

The THRESHOLD value is expressed in units of 0.01 second.

For example, if you specify 500, then the value used to make the determination is 5 CPU seconds.
CPUTIMENORM This option is designed for use with coexistence systems to aid in managing mixed nodes more efficiently.

The THRESHOLD value is expressed in units of 0.01 second.
ELAPSEDSEC The THRESHOLD value is expressed in units of 1.00 seconds.

This is the default.
ELAPSEDTIME The THRESHOLD value is expressed in units of 0.01 second, providing finer granularity for elapsed time than ELAPSEDSEC.
IOCOUNT If you do not specify a THRESHOLD value for n, then the system uses the default IOCOUNT value of 5.