Analyzing Plans to Improve Performance

Teradata Visual Explain User Guide

brand
Teradata Tools and Utilities
prodname
Visual Explain
vrm_release
16.10
16.20
category
User Guide
featnum
B035-2504-086K
Use Teradata Visual Explain to improve plan performance by analyzing a set of queries (known as a workload) for index and partition recommendations. The recommendations are saved in the QCD tables.

Index and partition analysis can be performed separately or together on a current plan or on a group of plans.

Index recommendations for specific tables can be viewed after analysis. For more information, see Viewing Index Recommendations for Tables.

Use this procedure to analyze a current plan or a group of plans, performing index and partition analyses separately or simultaneously.

This feature is available only for plans captured in QCD.
  1. Select a plan or group of plans for analysis:
    Option Action
    To analyze a plan:
    1. Load the plan.
    2. Right-click anywhere on the background.
    3. Click Index Analysis from the shortcut menu.
    To analyze a group of plans:
    1. Load the plans.
    2. Click .
    3. In the Select Plans for Analysis dialog box, under Available Execution Plans, select the plans to analyze.
    4. Add selected plans to the Selected Execution Plans list.
    5. Click Next.

    The Define dialog box appears. A unique workload name and index recommendation tag are generated internally, by default.

    Index Analysis

    Define Workload

  2. [Optional] To change the default, type a different workload name and index recommendation tag.
  3. Under Analysis Type, select each type of analysis to perform:
    • Initiate Index Analysis
    • Initiate Partition Analysis
  4. [Optional] If Initiate Index Analysis is selected, click Parameters to further define the index analysis. The Index Analysis Parameters dialog box appears.
    Index Analysis Parameters

  5. [Optional] Set parameters to determine index recommendations.

    For information on these parameters, refer to Teradata Index Wizard User Guide (B035-2506) (Advanced Index Parameters).

  6. Click OK to return to the Define dialog box.
  7. [Optional] Enter a number in the Time Limit box.
  8. [Optional] Select the Apply modified statistics check box to use the modified statistics information during the analysis.
    The Apply modified statistics check box is available only when the Initiate Index Analysis check box is selected.
  9. [Optional] Select the Delete after use check box to remove the workload and any recommendations after the analysis.
  10. Click Analyze.

    All selected analysis types are performed. A progress indicator appears showing the elapsed time since the start of the analysis. To terminate the analysis, click Abort.

    The analysis is performed for the current plan. The resulting recommendations are shown in an Index Recommendation report. The index analysis provides the recommended secondary index for each table and the partition analysis provides partition recommendations for each table when a recommendation can be made. This report provides decision-making information to determine whether a recommendation is appropriate. The report also suggests indexes that can be dropped. For more information about the columns in this report, refer to Teradata Index Wizard User Guide (B035-2506) (Index Recommendation report).

    Index Recommendation Report