15.00 - Logging Options for BEGIN QUERY LOGGING - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

Logging Options for BEGIN QUERY LOGGING

Logging options that you set with BEGIN QUERY LOGGING are recorded in the DBQLOptions field of DBQLRuleTbl.

 

Logging Option

Mask Value

Indicator Bit Position

EXPLAIN

2

2

SQL

4

3

OBJECTS

8

4

STEPINFO

16

5

XMLPLAN

32

6

SUMMARY

64

7

THRESHOLD

128

8

STATSUSAGE

256

9

LOCK

512

10

VERBOSE

1024

11

DETAILED STATSUSAGE

2048

12

USECOUNT

4096

13

UTILITYINFO

8192

14

PARAMINFO

16384

15

 

        Option

                                                        Definition

ALL

Log query object information, step information, EXPLAIN text, and all SQL requests for all logged on users. This is equivalent to specifying the EXPLAIN, OBJECTS, SQL, and STEPINFO options individually.

The ALL option does not include LOCK, PARAMINFO, STATSUSAGE, XMLPLAN, or UTILITYINFO.

This option does not invoke either the SUMMARY or THRESHOLD limit options.

You cannot specify the ALL logging option if you also specify the THRESHOLD limit option.

If you specify ALL, then you should also specify a value of 0 for SQLTEXT to ensure that the text for the SQL request is not logged in both the DBC.DBQLogTbl and DBC.DBQLSqlTbl tables.

If you specify ALL, then you cannot specify other logging options.

EXPLAIN

Log the unformatted EXPLAIN text for the request.

You cannot specify the EXPLAIN logging option if you also specify the THRESHOLD limit option.

This option generates and logs the unformatted EXPLAIN text for each request. It does not generate EXPLAIN text for requests preceded by the EXPLAIN request modifier.

For example, the system does not log EXPLAIN text for the following request.

     EXPLAIN SELECT * 
             FROM table_a ; /* EXPLAIN text logging
                               does not occur     */

If you perform the same query without the preceding EXPLAIN modifier, however, the system does log unformatted EXPLAIN text.

     SELECT *
     FROM table_a;  /* EXPLAIN text logging
                       does occur          */

You should be cautious about specifying this option because the performance cost of generating the EXPLAIN text for a query can be expensive.

LOCK=n

Log any lock contention that exceeds n hundredths of a second in XML format in DBQLXMLLockTbl.

OBJECTS

Log information about databases, tables, columns, and indexes accessed by SQL requests for the specified user set.

You can specify the OBJECTS logging option if you also specify the THRESHOLD limit option.

This option does not log activity for macros or views, nor does it log activity for dictionary tables and columns. For example, CREATE TABLE does not log any objects in the DBQL system because it only generates dictionary objects. Similarly, a query like the following does not log any objects because it only accesses dictionary tables.

     SELECT *
     FROM DBC.TVFields;

PARAMINFO

that parameter values and metadata are logged in DBQLParamTbl.

SQL

Log the full text of all SQL requests performed by the specified user set in system table DBC.DBQLSqlTbl.

You can specify the SQL logging option if you also specify the THRESHOLD limit option.

This option does not log SQL requests within SQL procedures, macros, views, or triggers.

STATSUSAGE

Log DML requests that require cardinality estimation in their query optimization as an XML document in system table DBC.DBQLXMLTbl.

If you specify STATSUSAGE with XMLPLAN, Teradata Database logs the collected data in a single integrated document.

Data logged with this option is independent of the THRESHOLD or SUMMARY option limit options.

STATSUSAGE does not log activity for explained requests or for the DUMP EXPLAIN and INSERT EXPLAIN statements (see SQL Data Manipulation Language for further information about these statements and the EXPLAIN request modifier.

The ALL option does not include STATSUSAGE.

STEPINFO

Log AMP step-level information for all SQL requests performed by the specified user set in system table DBC.DBQLStepTbl.

You can specify the STEPINFO logging option if you also specify the THRESHOLD limit option.

USECOUNT

Log use count collection for a database or user.

If you specify USECOUNT for a user, you can specify any of the other logging options.

If you specify USECOUNT for a database, you cannot specify any other logging options. Otherwise, the system returns an error to the requestor.

When you submit a BEGIN QUERY LOGGING request, the use counts and timestamps for the database or user are reset.

UTILITYINFO

Utility information is logged in DBC.DBQLUtilityTbl.

XMLPLAN

Logs the query plan generated by the Optimizer for SQL DML requests as an XML document in system table DBC.DBQLXMLTbl.

The recommended best practice for logging problematic queries is to specify XMLPLAN logging whenever you submit such queries. The XMLPLAN option captures the query plan in a form that enables Teradata support tools to optimally diagnose its performance issues.

If you specify XMLPLAN with STATSUSAGE, Teradata Database logs the collected data in a single integrated document.

You cannot specify the XMLPLAN logging option if you also specify the THRESHOLD limit option.

The XMLPLAN option does not log query plans for EXPLAIN request modifiers, or for INSERT EXPLAIN and DUMP EXPLAIN requests.

Logs basic information, such as StatementType and the corresponding StepNames, for DDL statements. In addition, detailed information is logged for:

  • COLLECT STATISTICS
  • CREATE INDEX
  • CREATE TABLE
  • DROP INDEX
  • DROP TABLE
  • XMLPLAN also logs detailed information for FastLoad and MultiLoad jobs.

    Because the XML document includes the query and step text, you generally do not need to specify the EXPLAIN and SQL options if you specify XMLPLAN. You should also specify a value of 0 for SQLTEXT to avoid redundant logging when you specify XMLPLAN.

    The ALL option does not include XMLPLAN.

    The XML schema for the log this option produces is maintained at http://schemas.teradata.com/queryplan/queryplan.xsd.