Using BEGIN QUERY LOGGING to Log Query Plan Information | Teradata Vantage - Using BEGIN QUERY LOGGING to Log Query Plan Information - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
jpx1556733107962.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™

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:
  • DUMP EXPLAIN
  • INSERT EXPLAIN
  • EXPLAIN

Also, the 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.
    BEGIN QUERY LOGGING … XMLPLAN INSERT EXPLAIN
    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.