15.00 - Using BEGIN QUERY LOGGING to Log Query Plan Information - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

Using BEGIN QUERY LOGGING to Log Query Plan Information

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
  • Furthermore, 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 for the reasons listed in the following bullets.

  • 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.

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