Chat now with support
Chat with Support

SQL Optimizer for Oracle 10.0 - User Guide

Welcome to SQL Optimizer
About SQL Optimizer SQL Optimization Workflow New in This Release Additional Resources Database Privileges Database Privileges Script Connect to the Database Windows Layout Customize Toolbars Keyboard Shortcuts Support Bundle Register SQL Optimizer Check for Updates SQL Operations
ALL PARTITION ALTER INDEX AND EQUAL ANTI JOIN BITMAP AND BITMAP COMPACTION BITMAP CONSTRUCTION BITMAP CONVERSION BITMAP INDEX BITMAP JOIN INDEX UPDATE BITMAP JOIN INDEX UPDATE STATEMENT BITMAP KEY ITERATION BITMAP MERGE BITMAP MINUS BITMAP OR BUFFER SORT CARTESIAN JOIN COLLECTION ITERATOR CONCATENATION CONNECT BY CONNECT BY PUMP COUNT COUNT STOPKEY CREATE AS SELECT CUBE SCAN DDL STATEMENT DELETE DOMAIN INDEX FAST FULL INDEX SCAN FILTER FIRST ROWS FIXED INDEX FIXED TABLE FOR UPDATE FULL INDEX SCAN FULL INDEX SCAN DESCENDING FULL INDEX SCAN (MIN/MAX) HASH GROUP BY HASH GROUP BY PIVOT HASH JOIN HASH JOIN BUFFERED HASH PARTITION HASH UNIQUE INDEX INDEX BUILD NON UNIQUE INDEX RANGE SCAN INDEX RANGE SCAN DESCENDING INDEX RANGE SCAN (MIN/MAX) INDEX SAMPLE FAST FULL SCAN INDEX SKIP SCAN INDEX SKIP SCAN DESCENDING INDEX UNIQUE SCAN INLIST ITERATOR INLIST PARTITION INSERT INTERSECTION INTO INVALID PARTITION ITERATOR PARTITION LOAD AS SELECT MAT_VIEW ACCESS MAT_VIEW REWRITE ACCESS MERGE JOIN MINUS MULTI-TABLE INSERT NESTED LOOPS OUTER JOIN PARTITION PARTITION HASH EMPTY PARTITION LIST PARTITION RANGE PROJECTION PX BLOCK ITERATOR PX COORDINATOR PX ITERATOR PX PARTITION PX PARTITION HASH ALL PX PARTITION LIST ALL PX PARTITION RANGE ALL PX RECEIVE PX SEND RANGE PARTITION RECURSIVE EXECUTION RECURSIVE WITH PUMP REFERENCE MODEL REMOTE SELECT SEMI JOIN SEQUENCE SINGLE PARTITION SINGLE RANGE PARTITION SORT SORT AGGREGATE SORT GROUP BY SORT GROUP BY CUBE SORT GROUP BY NOSORT SORT GROUP BY ROLLUP SORT JOIN SORT ORDER BY SORT UNIQUE SQL MODEL TABLE ACCESS TABLE ACCESS BY GLOBAL INDEX ROWID TABLE ACCESS BY INDEX ROWID TABLE ACCESS BY LOCAL INDEX ROWID TABLE ACCESS BY ROWID TABLE ACCESS BY USER ROWID TABLE ACCESS CLUSTER TABLE ACCESS FULL TABLE ACCESS HASH TABLE ACCESS SAMPLE TABLE QUEUE TEMP TABLE GENERATION TEMP TABLE TRANSFORMATION UNION UNION ALL UNION ALL (RECURSIVE WITH) UNPIVOT UPDATE VIEW VIEW PUSHED PREDICATE WINDOW
Optimize SQL
Create Optimize SQL Sessions Open Optimizer SQL Sessions Rewrite SQL Generate Execution Plan Alternatives
Optimize Indexes Batch Optimize SQL Scan SQL Inspect SGA Analyze Impact Manage Plans Configure Options SQL Optimizer Tutorials About Us Legal Notices

Compare SQL Statements

Use the Compare window to view the SQL text, execution plan, and execution statistics for your original SQL statement and all alternatives SQL Optimizer generates. The Compare window consists of the Alternatives window and the Comparison window. The Alternatives window displays execution statistics and the Comparison window displays SQL statements and execution plans. You can compare your original SQL statement with an alternative SQL Optimizer generates or compare two different alternatives.

Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.

To compare SQL statements

  1. Select the Optimize SQL tab in the main window.
  2. Select a SQL Rewrite session.

  3. Select .

  4. Select an alternative in the Alternatives pane to compare the alternative plan with your original execution plan.

    Note: The Comparison window displays the original execution plan in both panes by default.

    Tip: The alternative you selected is shown under your original SQL statement in the Alternatives window by default. To unfreeze the alternative selected, right-click the alternative and select Unfreeze Comparing Rows

  5. Click to switch the location of the original execution plan and selected alternative in the Comparison pane.

    Note: Switch the location of the original SQL statement to compare two alternatives. Once you select the first alternative, click and select the second alternative.

  6. Click the current layout option in the Comparison pane to change the layout for the statements. 

  7. Review the following for additional information:

    SQL and Plan (Left-Right) Displays the SQL text and execution plan for the statements you are comparing side by side.
    SQL Only (Left-Right) Displays the SQL text for the statements you are comparing side by side.
    Plan Only (Left-Right) Displays the execution plan for the statements you are comparing side by side.
    SQL and Plan (Top-Bottom) Displays the SQL text and execution plan for the statements you are comparing beneath each other.
    SQL Only (Top-Bottom) Displays the SQL text for the statements you are comparing beneath each other.
    Plan Only (Top-Bottom) Displays the execution plan for the statements you are comparing beneath each other.

    Tip: Click to maximize the Comparison pane. You can click to restore the Comparison pane to its original size.

  

Related Topics  

About Optimizing SQL (SQL Rewrite)

 

 

View Best Practices

Use Best Practices in Optimize SQL to analyze your SQL statement and database to recommend common techniques for improving database performance. Since the recommendations can also affect performance of other statements in your database, you should review and test the recommendations before implementing them. When evaluating the recommendations, take into account that database performance is affected by the following:

  • System resources (CPU, I/O, memory, database architecture, and more)

  • Data distribution

  • System architecture

  • SQL execution plans

  • User's usage behavior

Notes:

  • Best Practices is only available in SQL Rewrite mode in Optimize SQL.

  • The Best Practices tab is not displayed by default. To display the Best Practices tab, click , select Optimize SQL | Best Practices | General, and select the Display Best Practices tab in SQL Rewrite mode checkbox.

To view best practices

  1. Select the Optimize SQL tab in the main window.
  2. Select a SQL Rewrite session.

  3. Select a statement in the Alternatives Details pane.

  4. Click .

  5. Click .

 

Related Topic

About Optimizing SQL (SQL Rewrite)

Generate Optimize SQL Resolution Report

You can generate a resolution report for a SQL Rewrite or Plan Control session after you generate execution plan alternatives. The resolution report includes the following:

  • Resolution summary
  • Comparison graph
  • Execution statements comparison
  • Optimization session information
  • Text, statistics, and plan for each selected statement
  • Best Alternative scenario
  • Other Alternative scenarios

Tip: You can also export the contents of a SQL Rewrite grid (or other grid) to an Excel file. Right-click the grid and select Save As | Excel Document. Other file formats are also available.

To generate a report

  1. In a SQL Rewrite or Plan Control session, after generating and executing SQL or plan alternatives, click .

  2. Use the report window to customize the layout and the information displayed in the report, and to select publishing options. Use the following Report toolbar buttons.

    Toolbar Button Description
    Current Settings. Click to specify the information to include in your report.
    Page Setup. Click to modify margins and select page orientation.
    Scale. Click to adjust the scale of the printed output.

    Watermark. Click to add a watermark to the report.

    • To upload an image to use as the watermark, select the Picture Watermark tab.
    Color. Click to change the report background color.

    Export Document. Click to save the report to file using the default format. Click the arrow to select from several file format options (or to change the default setting).

    You can select from several different file formats, including PDF, HTML, and Excel.

    Send via Email. Click to save the report to file using the default format and to also attached the file to an email. Click the arrow to select from several file format options (or to change the default setting).

Note: To select SQL or plan alternatives to include in the report, click and then click Select Alternatives. Select alternatives from the list.

To export the report as a PDF file

  1. In the Report window, click the arrow beside and select PDF.
  2. In the PDF Export Options dialog, configure the PDF options and click OK.
  3. Enter a file name and select a location for your report.

  

Related Topics

About Optimizing SQL (SQL Rewrite)

About Optimizing SQL (Plan Control)

Register SQL Translation

The Register SQL Translation feature allows you to substitute a better-performing SQL alternative for your original SQL without modifying the application code. This feature uses Oracle SQL Translation Framework to store the original SQL and the substitution (translation) and then run the substitution in place of the original SQL in the client application. The original/substitution SQL pairs are stored in a SQL Translation Profile.

After optimizing your application SQL, use this feature to deploy (register) a better-performing SQL alternative (SQL translation). Then Oracle runs the SQL alternative in place of the original SQL in the application.

Note: This feature is only applicable to SQL sent from a client application. For SQL originating from a database object (e.g., stored procedure), the SQL substitution/translation process does not take place.

To register a SQL translation

  1. After optimizing a SQL statement in Optimize SQL (SQL Rewrite), select an alternative to deploy as a SQL translation.
  2. Right-click the selected SQL alternative and select Register SQL Translation.

  3. In the Register SQL Translation dialog, select an existing SQL Translation Profile or create a new one to store the SQL translation. A SQL Translation Profile is a database object that contains the original SQL statement and its SQL translation.

    • To create a new SQL Translation Profile, click and enter a name. Click OK.
  4. Select the alternative to use as the SQL translation.
  5. Review the text of the SQL statements and the execution plans.

    • You can edit the Original SQL and the Translation SQL text in the editor windows. The modified SQL text is sent to the profile when you click Add.

      Note: The format of the Original SQL text must be exactly the same as the SQL in your client application for the SQL substitution to take place.

    • You can right-click in an editor window and select Open to add SQL from a file.
  6. Click Add to add the SQL translation to the selected profile.
  7. If the selected profile already contains a translation for the original SQL, the existing translation is displayed in a third pane. To replace the existing translation with your selected alternative, click Replace.

Note: This feature is only available when connected to Oracle Database 12c or later.

To use a SQL Translation

  • To use a SQL translation, use the following commands in Oracle:

    alter session set events = '10601 trace name context forever, level 32';

    alter session set sql_translation_profile = <profile_name>;

  

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating