Entering a Query and Viewing Its Execution Plan - Visual Explain

Teradata Visual Explain User Guide

Product
Visual Explain
Release Number
15.00
Language
English (United States)
Last Update
2018-09-27
dita:id
B035-2504
lifecycle
previous
Product Category
Teradata Tools and Utilities

Entering a Query and Viewing Its Execution Plan

Enter a query from Teradata Visual Explain and display the resulting execution plan.

To enter a query and view its execution plan

1 Click .

The View Execution Plan(S) dialog box appears.

Figure 40: View Execution Plan(S) - for Queries

2 Use one of the following options:

  • The default option is Explain in XML, which generates an execution plan but does not store plan information in the database.
  • To insert execution plan information into the specified QCD, click the More >> button and select the Capture in QCD checkbox.
  • To submit INSERT EXPLAIN statements with XML SQL and capture XML plans in the QCD, select the Store as XML checkbox.
  • Note: This option is enabled only if the Capture in QCD checkbox is selected.

    Figure 41: View Execution Plan - Expanded

    3 [Optional] To place a limit on the size of the query and DDL text captured in the QCD:

    a Click the Options button to open the Options window.

    b Enter Limit Text in the Limit Text checkbox and click OK.

    If no value is entered, 0 is the default.

    Figure 42: Options window

    4 [Optional] Use the following steps to specify the number of times an SQL statement is typically performed within its identified workload:

    a Ensure that the Capture in QCD checkbox is selected in step 2.

    b Click the Options button to open the Options window.

    c Enter a value in the Frequency box in the Options window and click OK.

    This value is used to weight the respective benefits of each column analyzed for inclusion in the index recommendation computed by Teradata Index Wizard. Any positive integer up to 4 B is valid. If a value is not specified, frequency defaults to 1.

    5 In the QCD Name box, enter the name of the QCD database.

    Note: This field is enabled only when Capture in QCD is selected.

    6 In the Query Tag box, enter a name used to identify the query.

    7 Do one of the following:

  • To enter the statement text, type the text in the Query window.
  • To open a file containing statement text, click Browse to select a .sql or .txt file.
  • 8 [Optional] To collect statistics on tables involved in the plan:

    a Ensure that the Capture in QCD checkbox is selected in step 2.

    b Click the Options button to open the Options window.

    c Select the Collect Statistics checkbox in the Options window.

    Statistics are collected on columns that are index candidates and for all columns and indexes referenced explicitly with values in the SQL statement when the Collect Statistics checkbox is selected. A candidate index column is defined as a column for which the value or join range access frequencies stored in the Field table of the specified QCD are greater than 0.

    9 [Optional] To collect demographics on some tables involved in the plan:

    a Ensure that the Capture in QCD checkbox is selected in step 2.

    b Click the Options button to open the Options window.

    c Select the Demographics checkbox.

    d Click Select Tables.

    10 If not already done, click Select Tables to specify the set of tables for which data demographics and statistics details are either to be included or excluded for analysis.

    11 To emulate a target environment at various levels or scopes.

    Note: Only the DBC or SystemFE user can set costs at the IFP and SYSTEM levels. An error occurs when these conditions are not met.

    For more information, see “Setting or Resetting Random AMP Samples” on page 156 or “Setting or Resetting Cost Parameters” on page 157.

    12 Click Submit to create the execution plan.