16.20 - Using BEGIN QUERY LOGGING to Log Query Plan Information - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Teradata Database
Teradata Vantage NewSQL Engine
Release Number
March 2019
Content Type
Programming Reference
Publication ID
English (United States)

You can capture the Optimizer query plan for logged SQL DML requests in the DBQL table DBC.DBQLXMLTbl. To do this, specify XMLPLAN for logging_option to enable query plan capture for executing DML queries as XML text.

Query plan information in XML format is particularly useful for diagnosing performance and query plan issues.

You cannot capture query plans for the following statements and request modifier with the XMLPLAN option.


Also, Teradata Database does not log text definitions of objects referenced by SQL requests. Instead, the system logs a short identifier that can be used as a key to fetch the text definition from the data dictionary if it is needed.

XMLPLAN logging is not an alternative to the information captured by INSERT EXPLAIN requests because:
  • Apart from the respective content of the documents they produce, there is another important difference between XMLPLAN logging and INSERT EXPLAIN requests, as indicated by the following table.
    Logs query plans for executed queries in XML format. Captures query plans without executing the query.

    XMLPLAN logging is ideal when you want to record query plans for your executing workloads and have found that capturing query plans for the requests in those workloads using INSERT EXPLAIN requests is too slow for your needs.

    On the other hand, if you are only tuning a query and do not want to execute it, XMLPLAN logging is not as useful as capturing the query plan for a request using INSERT EXPLAIN requests.

    In this case, executing an INSERT EXPLAIN INTO QCD_name IN XML request or an EXPLAIN IN XML SQL_request is a more analogous alternative. You cannot capture query plans in XML format using DUMP EXPLAIN requests.

  • Runtime information from the traditional DBQL tables is also captured for a logged plan.
  • XMLPLAN logging is more an extension to query logging than an extension to the Query Capture Facility.

The EXPLAIN option does not log rows for cached requests.