Using BEGIN QUERY LOGGING to Log Query Plan Information | Teradata Vantage - Using BEGIN QUERY LOGGING to Log Query Plan Information - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-10-04
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
jbg1472252759029
lifecycle
latest
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.