Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Once you have executed a tuning job, the Overview tab reflects tuning analysis of the specified statements. The Analysis tab shows the resulting analysis of the query, including indexes used, not used, and missing (or suggested to create). For more information on using the Analysis tab, see Understanding the Analysis Tabtab.

  • The Generated case Expand/Collapse control lets you hide or display the hint-based cases and transformation-based case generated for a statement.
  • The Perform detail analysis and Execute each generated case check boxes let you enable multiple statements or cases for simultaneous execution while the Run/Cancel Job controls let you start and stop simultaneous execution.
  • Use the Schema and Catalog Selectors to select a schema and catalog for the tuning job.
    The catalog selector is available only for SQL Server and Sybase data sources. By specifying the schema and catalog, the tuner can use the paths of the schema and catalog selected to find the tables queried in the job rather that use the paths of the schema and catalog used to connect to the data source. If you change the schema or catalog used in a tuning statement you will need to refresh the tuning statements in order for new cases to be generated, which take into consideration the schema used. Right-click a tuning statement, and then select Refresh Tuning Statements.

...

  • The explain plan-based Cost field can be expended to display a graphical representation of the values for statements or cases. Similarly, after executing a statement or case, the Elapsed Time field can be expanded to display a graphical representation. The bar length and colors used in the representation are intended as an aid in comparing values, particularly among cases. For example:

    In the case of both Cost and Elapsed Time, the values for the original statement are considered the baseline values. With respect to color-coding for individual case variants, values within a degradation threshold (default 10%) and improvement threshold (default 10%) are represented with a neutral color (default light blue). Values less than the improvement threshold are represented with a distinctive color (default green). Values greater than the degradation threshold are shown with their own distinctive color (default red).
    With respect to bar length, the baseline value of the original statement spans half the width of the column. For child-cases of the original statement, if one or more cases show a degradation value, the largest degradation value spans the width of the column. Bar length for all other children cases is a function of the value for that case in comparison to the highest degradation value. 

    Info

    For information on specifying colors, and the improvement threshold and degradation threshold values used in these graphical representations, see Specify Tuning Job Editor Preferencespreferences

Additionally, once results have been generated you can:

Anchor
_COMPARE_CASES
_COMPARE_CASES
Compare

...

cases

You can compare cases between an original statement and one of its tuning-generated statements, or another statement case via the Compare to Parent and Compare Selected commands, respectively.



To compare a case side-by-side with its parent

...

Select the two cases, and then right-click in the Name field of either case. Select Compare Selected from the context menu.

Anchor
_FILTER_AND_DELETE
_FILTER_AND_DELETE
Filter and

...

delete cases

You filter cases from the Generated Cases table via the Filter icons on the Generated Cases Toolbar of the Overview tab.

...

When removing cases, the criteria you set has no effect on cases subsequently generated.

Anchor
_CREATE_AN_OUTLINE
_CREATE_AN_OUTLINE
Create an

...

outline

If SQL is executed by an external application or If you cannot directly modify the SQL being executed but would like to improve the execution performance, you can create an outline on the Oracle platform. An outline instructs the Oracle database on the execution path that should be taken for a particular statement.

...

  1. On the Overview tab of a tuning job, right-click in the Name field of a case and select CreateOutline from the context menu.
    A NewOutline wizard opens.
  2. On the first panel, provide an Outlinename, select an Outlinecategory, and then click Next.
    A Preview Outline panel opens previewing the SQL code to create the outline. 
  3. Select an Actiontotake option of Execute or OpeninnewSQLeditor and then click Finish.
    For more information, see Using the Outlines Tab tab (Oracle).


Scroll pdf ignore
Automate SQL tuning and profiling with DB Optimizer. Learn more > >