Entering a Query and Viewing Its Execution Plan - Visual Explain

Teradata Visual Explain User Guide
Product
Visual Explain
Release Number
16.20
16.10
Published
May 2017
Language
English (United States)
Last Update
2018-05-04
dita:mapPath
pvi1488824663157.ditamap
dita:ditavalPath
Audience_PDF_include.ditaval
dita:id
B035-2504
Product Category
Teradata Tools and Utilities

As stated, a query can be run using the INSERT EXPLAIN statement, then located, and the results displayed. Alternatively, Teradata Visual Explain can be used to enter the statement text and immediately view the execution plan for that query.

  1. Click Tools > View Execution Plan(s). The View Execution Plan(s) dialog box appears.
    View Execution Plan(s) Dialog Box

  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.
    • Click the More button.
    View Execution Plan - Expanded

    The expanded view of the View Execution Plan(s) dialog box opens.
  3. Select one of the following items:
    • Select the QCD Plans checkbox to insert execution plan information into the specified QCD.
    • Select the Store as XML checkbox to capture the execution plan in XML form in QCD (i.e to submit INSERT EXPLAIN in XML).
    • Select the Dynamic Plans checkbox to capture dynamic (IPE) plan information. Enter a Request Name and optional Comment in the respective edit boxes.
      This option is enabled only if the Capture in QCD checkbox is selected.
  4. Verify the Capture in QCD is selected.
  5. Enter the information in the table in the View Execution Plan(s) dialog box.
    Option Description
    Query Enter statement text, or open a file containing statement text in the Query window. Do one of the following:
    • Enter statement text in the Query window.
    • Click Browse and locate the .sql or .txt file.
    Default Database Enter the name of the default database in the Default Database box.
    QCD Name Enter the name of the QCD database in the QCD Name box.
    This field is enabled only when Capture in QCD is selected.
    Query Tag Enter a name used to identify the query in the box.
  6. [Optional] Click the Options button to view more items. The Options dialog box opens.
    Options dialog box

  7. [Optional] Enter the information in the table in the Options dialog box.
    Option Description
    Frequency Specify the number of times an SQL statement is typically performed within its identified workload.
    1. Ensure that the Capture in QCD checkbox is selected.
    2. Enter a value in the Frequency box.
      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. The frequency defaults to 1.
    Limit Text Limit the size of the query size and DDL text captured in the QCD.
    1. Ensure that the Capture in QCD checkbox is selected.
    2. Enter a Limit Text value in the checkbox.
      If no value is entered, 0 is the default.
    Collect Statistics Collect statistics on columns.
    1. Ensure that the Capture in QCD checkbox is selected.
    2. Select the Collect Statistics checkbox.
      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.
    Collect Demographics Collect demographics on selected tables.
    1. Ensure that the Capture in QCD checkbox is selected.
    2. Select the Demographics checkbox.
    3. Click Select Tables.
    4. Select the desired tables.
    5. Click OK.
    Demographics

  8. To emulate a target environment at various levels or scopes:
    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 or Setting or Resetting Cost Parameters.

  9. Click Submit to create the execution plan.