DUMP EXPLAIN
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.
Syntax
where:
|
Syntax element … |
Specifies … |
|
INTO QCD_name |
the 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 |
an optional user-defined name under which the query plan information is to be stored. If no query_plan_name is specified, the query plan information is stored with a null name. Because each query plan is stored with a unique non-null Query ID (see SQL Request and Transaction Processing), there is no problem distinguishing among the various query plans within a given database. 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 |
to 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 |
to capture COLLECT STATISTICS recommendations for SQL_request in the StatsRecs QCD table (see SQL Request and Transaction Processing). |
|
SQL_request |
the 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: |
ANSI Compliance
DUMP EXPLAIN is a Teradata extension to the ANSI SQL:2011 standard.
Required Privileges
You must have INSERT privileges on all the tables in the specified query capture database.
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.
1 Runs an EXPLAIN on the SQL DML request specified by SQL_statement.
2 Captures the Optimizer plan output of that EXPLAIN.
3 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
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
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 1” 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
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';
For More Information
For more information about index analysis, see: