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 Tab.

  • 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 Selector to select a schema for the tuning job. By specifying the schema, the tuner can use the path of the schema selected to find the tables queried in the job rather that use the path of the schema used to connect to the data source. If you change the schema 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 Column set Expand/Collapse controls let you expand a column set to display more of the columns within the table.

  • The Tuning Status Indicator indicates whether a statement or case is ready to execute or has successfully executed. The following table provides information on the Tuning Status Indicator states:

    IconDescription

    The case has not been executed. There are no errors or warnings and the case is ready to be executed.

    The case has been successfully executed.

    		Execution for this case failed or was cancelled due to execution time exceeding 1.5 of original case time.

    Hovering the mouse over the Tuning Status Indicator displays a tip that notes the nature of a warning or error. 

    If a warning  indicates that one or more tables do not have statistics, you can right- click the statement and select Analyze Tables to gather statistics. A warning may also indicate that the tuning statements are out of sync, in which case you can right-click a tuning statement and select Refresh Tuning Statements. 

    A warning can indicate an object caching error. For example, a table may not exist or not be fully qualified. Cases cannot be generated for the associated statement. 

  • 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. 

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

Additionally, once results have been generated you can:

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

Right-click in the Name field of a case and select Compare to Parent from the context menu.

To compare two cases

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

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.

Filter the cases on the Overview tab so that hints that are not improvements on the original statement are not displayed. You can filter:

  • Non-optimizable statements
  • Optimized statements
  • Worst cost cases
  • Worst elapsed time cases

When filtering, the criteria remain in effect until you change the criteria. That is, as new cases are generated, only those cases that do not satisfy the filtering criteria are displayed. To restore an unfiltered set of cases, open the Filter dialog and deselect the filtering options.

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

To filter cases from the Overview table

  1. Click the Filter button, respectively. A Filters dialog opens.
  2. Use the check boxes to select your filtering and then click OK. 

To delete cases from the Overview table

  1. Right-click on the row of the case you want to delete and select Delete. A Delete dialog opens.
  2. Use the check boxes to select your filtering and then click OK.

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

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.

To create an outline for a change suggested by a case

  1. On the Overview tab of a tuning job, right-click in the Name field of a case and select Create Outline 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.



IDERA |  Products | Purchase | Support |  Community |  Resources |  About Us  | Legal
  • No labels