DUMP EXPLAIN - Teradata Database

Purpose

Captures the Optimizer plan information for a query and returns it to the requestor in the form of a script containing a series of INSERT requests.

For more information about index analysis, see:

“BEGIN QUERY LOGGING” in SQL Data Definition Language
“REPLACE QUERY LOGGING” in SQL Data Definition Language
“COLLECT DEMOGRAPHICS”
“COLLECT STATISTICS (QCD Form)”
“INITIATE INDEX ANALYSIS”
INSERT EXPLAIN
“RESTART INDEX ANALYSIS”
SQL Request and Transaction Processing
Database Design
Teradata Index Wizard User Guide
Teradata Viewpoint User Guide, chapter on Stats Manager
Teradata Visual Explain User Guide
Teradata System Emulation Tool User Guide

Required Privileges

You must have INSERT privileges on all the tables in the specified query capture database.

Syntax

Syntax Elements

INTO QCD_name

Name of a user-defined query capture database.

The database named QCD_name need not exist on the target system. However, a database named QCD_name must exist on the test system on which the generated script is performed.

Use the Control Center feature of the Visual Explain tool to create your QCD databases.

AS query_plan_name

Optional user-defined name under which the query plan information is to be stored. For information on object naming, see SQL Fundamentals .

If you do not specify a query_plan_name, the query plan information is stored with a null name. Each query plan is stored with a unique non-null Query ID, enabling you to distinguish among the various query plans in a particular database. See SQL Request and Transaction Processing . Note that Query IDs are not unique across databases.

query_plan_name is not constrained to be unique.

You can store query_plan_name as query_plan_name if you enclose the pad character-separated words in APOSTROPHE (u+0027) characters as “query plan name”.

LIMIT

LIMIT SQL

LIMIT SQL=n

Place a limit on the size of the query, DDL, view text, and predicate text captured for the QCD tables Query, Relation, ViewText, and Predicate, respectively.

If you do not specify this clause, then the system captures complete text.

The value for n indicates the upper limit on the amount of text to capture.

If you specify either LIMIT by itself or LIMIT SQL without a value, then the clause is equivalent to LIMIT SQL = 0, and no text is captured.

CHECK STATISTICS

Capture COLLECT STATISTICS recommendations for SQL_request in the StatsRecs QCD table. See SQL Request and Transaction Processing .

SQL_request

DML statement whose Optimizer plan information is to be captured and returned to the requestor as a series of INSERT requests.

SQL_request is limited to the following statements:

DELETE
EXEC (Macro Form)
INSERT
MERGE
SELECT
UPDATE

ANSI Compliance

DUMP EXPLAIN is a Teradata extension to the ANSI SQL:2011 standard.

Invocation

Normally invoked using client-based database query analysis tools.

DUMP EXPLAIN Actions Without An Existing QCD

Ensure that there is an existing QCD_name database on the test system before running the script produced by DUMP EXPLAIN for the first time. You can use the Control Center feature of the Visual Explain tool to create your QCD databases.

If a QCD_name database or any of its tables does not exist, or if you do not have INSERT privileges on one or more QCD_name database tables when you perform the script produced by DUMP EXPLAIN, then the system displays an appropriate error message and terminates performance of the script.

Actions Performed by DUMP EXPLAIN

DUMP EXPLAIN performs the following actions in the order indicated.

Runs an EXPLAIN on the SQL DML request specified by SQL_statement.
Captures the Optimizer plan output of that EXPLAIN.
Returns the output to the requestor as a script containing a series of INSERT requests designed to be used to update the appropriate tables in the user-specified query capture database.

You might want to use DUMP EXPLAIN rather than INSERT EXPLAIN if you are collecting information from several different machines and you want to ensure that only the selected QCD on the appropriate machine is updated with the results or if you do not want to update the QCD during heavy workload periods. In this case, you could submit the DUMP requests as part of a batch job during a less burdened workload period.

DUMP EXPLAIN Is Not Valid Within a Multistatement Request

You cannot submit an DUMP EXPLAIN request as part of a multistatement request, though you can submit an DUMP EXPLAIN request for a multistatement request.

If you attempt to submit a multistatement request that contains an DUMP EXPLAIN request, the multistatement request aborts and returns an error.

Note that while the DUMP EXPLAIN request in the following example superficially appears to be a valid statement in a multistatement request, it actually captures the query plan for a multistatement request that follows it, and is not itself part of that multistatement request. As a result, the request is treated like any other DUMP EXPLAIN and completes successfully.

     DUMP EXPLAIN INTO qcd SELECT * FROM d1.t1
     ;SELECT * FROM d1.t1;

       *** Insert completed. One row added.
       *** Total elapsed time was 1 second.

DUMP EXPLAIN Does Not Support Capturing Output in XML

Unlike INSERT EXPLAIN and the EXPLAIN request modifier, the DUMP EXPLAIN statement does not support the capture of query plan data as an XML document.

Effects of Request Cache Peeking on DUMP EXPLAIN Outcomes

When a data parcel is submitted with an DUMP EXPLAIN request, the plan might be generated with peeked USING and CURRENT_DATE or DATE values, or both. If any of these values are peeked, then the query plan shows them.

If no data parcel is submitted with an DUMP EXPLAIN request, the resulting plan is generated without peeking at USING or CURRENT_DATE, or DATE values, so it is a generic plan by definition. Note that the Visual Explain, Teradata System Emulation Tool, and Teradata Index Wizard do not accept USING data as input while capturing query plans using DUMP EXPLAIN requests unless those requests are submitted using BTEQ or Teradata SQL Assistant.

The Teradata Index Wizard internally generates plans for workload queries in order to estimate workload costs, which are used to determine optimal index recommendations. When queries in the workloads specify USING request modifiers, the plan is generated without peeking at USING, CURRENT_DATE, or DATE values. Because of these factors, Request Cache peeking has no impact on the resulting index. Given that workload analyses should be independent of USING values, this behavior is correct.

Examples

The following example set uses the same SELECT request throughout to illustrate how the different syntax options of DUMP EXPLAIN produce different results.

Each DUMP EXPLAIN request generates a set of INSERT requests for the query plan produced by the Optimizer for the SELECT request that it modifies.

Example: DUMP EXPLAIN Request

The output of this DUMP EXPLAIN request is referenced under the query plan name EmployeeSmithQuery in the TLE_queries database.

     DUMP EXPLAIN INTO TLE_queries AS EmployeeSmithQuery
     SELECT emp_id, emp_address
     FROM employee
     WHERE emp_name = 'Smith';

Example: DUMP EXPLAIN Request With Null Query Plan Name

The output of this DUMP EXPLAIN request has a null query plan name in the TLE_queries database. The difference between this result and that of Example: DUMP EXPLAIN Request is that the query plan for this example has no name.

     DUMP EXPLAIN INTO TLE_queries
     SELECT emp_id, emp_address
     FROM employee
     WHERE emp_name = 'Smith';

Example: DUMP EXPLAIN Request With Query Plan Name

The output of this DUMP EXPLAIN request is referenced under the query plan name Employee Smith Query in the TLE_queries database.

     DUMP EXPLAIN INTO TLE_queries AS "Employee Smith Query"
     SELECT emp_id, emp_address
     FROM employee
     WHERE emp_name = 'Smith';