Chat now with support
Chat with Support

SQL Optimizer for Oracle 9.3.3 - 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

Review Results

Step six. In an Analyze Impact session, the specified change may or may not impact the SQL workload.

  • If the analysis concluded that there is no impact to the specified SQL workload, then the analysis log page displays.
  • If the analysis finds an impact to the specified SQL workload, then the Impacts page displays.

To review impact analysis results - No Impact

  1. Click Open a saved Impact Session to open a completed Analyze Impact session.

  2. Select the Impacts tab.

    If the analysis completed and no impacted SQL were found, you will notice the following:

    • The only page available to view is the Process Log.

    • The Log displays this statement "No impacted SQL found during the analysis."

  3. Click to re-run the analysis without modifying any criteria.

  4. To modify the SQL workload or the Change criteria and then re-run the analysis within the same session, select the SQL or Change Details tab. Then click Edit criteria.

    Caution: If you click Edit Criteria, the analysis results for the current session will be lost, as well as the SQL workload or the Change criteria for the current session.

To review impact analysis results - Impacts

  1. Click Open a saved Impact Session to open a completed Analyze Impact session.

    Tip: The Open SQL Workload window displays a summary of results for each completed session. Select an Analyze Impact session in the list to display the summary in the lower pane.

  2. Select the Impacts tab.

  3. The Impacts page displays the details of the impact analysis. Review the following for additional information:

    Pane / Option Description
    <change type> Changes Displays a summary of the specified changes.

    Projected Workload by Timeslots

    (left side)—Displays a summary of the SQL workload improvements for the specified change.

    (right side)—Use this pane to investigate how the SQL workload was impacted by the specified change.

    Use the cursor to hover over a timeslot to display a summary of the impact to that timeslot’s SQL.

    Note: Only workloads collected from snapshots display Timeslots.

    View

    Displays one of two different impact evaluations for the Projected Workload:

    • Plan Statistics view—Displays the estimated improvements based on changes in plan statistics and weighed by the number of executions for each SQL per timeslot.
    • Projected Actual Statistics view—Displays the estimated improvements calculated using actual statistics and changes in plan statistics.

    Number of <category> SQL

    Displayed below the Estimated Improvement chart are data for the number of SQL statements in several different categories.

    » Click the number (N) to open the list of SQL statements for that category.

    Note: Once on the SQL list page, use the breadcrumb drop-down menu to navigate to other SQL lists.

    Click to return to the Impacts Summary page.

    Publish Report

    		 Click this link to view the Analyze Impact report which you can customize, save to file, print, and email. See Generate an Analyze Impact Report for more information.

    Analyze Again

    		 Click to re-run the analysis without modifying any criteria.

    View Process Log

    Click to display the log of the analysis process.

    Click to return to the Impacts Summary page.

    Selected timeslots

    Displays the number of timeslots selected. Click the link to select timeslots, or click a timeslot in the graph to select it.

    Note: This action also filters the SQL statements displayed in the Impacted SQL pane. Only SQL for the selected timeslots are displayed.

    Chart Options

    		 Click this link to select which charts/statistics to display.
    Impacted SQL

    Use this pane to review the impacted SQL statements

    Note: Only SQL statements for the selected timeslots are displayed in the Impacted SQL pane.

    SQL Name

    Click a SQL name to display SQL statement details, including a comparison of the original and the recommended execution plans.

    Click to return to the Impacts Summary page.

Impact Analysis - Error

» If the analysis results in an error, you can view the error details by clicking the link on the error page. Then modify your analysis criteria, if necessary.

Tip: Use and to navigate back and forth between the pages of a tab.

  

Related Topics

Generate an Analyze Impact Report

Collect SQL and Run an Impact Analysis

About Analyze Impact

 

Create Index Scripts

After running an impact analysis of additional indexes, SQL Optimizer allows you to easily create a script that you can use to generate the indexes in your database environment.

To create an index generation script

  1. Select Analyze Impact.
  2. Click Open a Saved SQL Workload.
  3. Select the session for which you want to create a script and click Open.
  4. Select the session's Impacts tab.
  5. In the Indexes List pane, click Create/Edit Index Generation Script.

  6. The Index Script page opens with the scripts created. Use this page to modify scripts and index names. Review the following for additional information:

    Pane / Option Description
    Naming Convention  

    Prefix, Name, Suffix

    		 Create a naming convention for SQL Optimizer to use to give the index a name in the script. You can include a prefix and a suffix. The naming convention is applied to all the indexes in this session.
    n Recommended Indexes  

    Modify Tablespace

    		 Click to select a tablespace for the selected index or indexes.

    Modify Schema

    		 Click to select a schema for the selected index or indexes.
    Generated Index Scripts

    SQL Optimizer automatically creates the index generating scripts. You can use this text editor to modify the scripts.

    Tip: Use the right-click menu or buttons at the bottom of the window to copy or to print the scripts.

 

Tip: Use and to navigate back and forth between the pages of a tab.

 

  

Related Topics

Review Impact Analysis Results

About Analyze Impact

 

Generate Report

You can generate a report of the impact analysis results and export the report (or save to file) to one of a number of different formats. You can also easily email the report with one click.

To generate an Analyze Impact report

  1. Select Analyze Impact.
  2. Click Open a saved Impact Session and select a session. Click Open to open the session.
  3. Select the Impacts tab to view the latest results.
  4. Click Publish Report. SQL Optimizer generates the report and opens the report page.

  5. 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).

To publish an Analyze Impact report as a PDF

  1. On the report page, select File | Export Document | PDF File, or click and select PDF from the list.

    Tip: You can select from several different file formats for the exported (published) report, including HTML and Excel.

  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.

 

Tip: Use and to navigate back and forth between the pages of a tab.

  

Related Topics

Review Impact Analysis Results

About Analyze Impact

 

Manage Plans

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating