Chat now with support
Chat mit 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

Review Optimize Indexes Results

Step five. You can review the results or the progress of your search for new indexes in Optimize Indexes.

To View Optimize Indexes Search Results

  1. If you already have a session open, skip to 3. Otherwise, select Optimize Indexes.

  2. Click Open a Saved SQL Workload and select a workload session. Click Open to open the session.

    Note: You can view a summary of the results, or the progress of the search if it is ongoing, in the bottom pane of the Open SQL Workload window.

  3. Select the Search Process tab to review details related to the search process. Each line on the graph indicates a new indexing recommendation.

    Notes:

    • Use your cursor to hover over a line on the graph to display a summary of the recommendation’s improvements (in a tooltip). Then click View details (in the tooltip) to open the Results page for the selected recommendation.
    • Select All in the Display Range box, to display all index recommendations in the graph.
    • Click List all results to display all index recommendations and their cost improvement values in a grid format. Click a recommendation in the list to open the results page for the selected recommendation.

  4. Select the Results tab to view the final recommendation (or the last recommendation if the search process is ongoing).

    Review the following for additional information:

    Pane / Option / Button Description
    n Recommended Indexes

    Displays a list of the indexes for the recommendation.

    Index Name—Click an Index name to see the index details, including a list of existing indexes which you can use to check for duplication.

    Tip: Select (highlight) an index in the Recommended Indexes list to highlight the SQL statements in the Impacted SQL list that are impacted by that recommended index.

    Results Summary

    (left side)—Displays a summary of workload improvement results.

    Note: If the links at the bottom of the Results Summary pane are not in view, click and drag the pane's bottom edge down to resize.

    (right side)—Displays the workload timeslots.

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

    Note: Timeslots display only for workloads collected from snapshots.

    View

    Displays one of two different evaluations for the 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.

    Publish Report

    		 Click to view the Optimize Indexes Workload Report which you can customize and then export, print, or email.

    Send to Analyze Impact

    		 Click to send the results to the Analyze Impact module where you can evaluate the impact of the new indexes on other SQL.

    Chart Options

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

    SQL Name—Click a SQL name to review the SQL statement and to compare the recommended execution plan to the original execution plan.

    Tip: Select (highlight) a SQL statement in the Impacted SQL list to highlight the indexes in the Recommended Indexes list that contributed to the performance improvement of that statement.

    Click to create a script to generate the new indexes recommended by Optimize Indexes.

Notes:

  • To save the contents of a grid (list) as a file, right-click the grid and select Save As.
  • An open database connection is not required when opening a completed session, reviewing results, or creating and publishing (printing) a report.

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

 

Related Topics

About Optimize Indexes

Send Virtual Indexes to Analyze Impact (Optimize Indexes)

Create Index Generation Script

Generate an Optimize Indexes Workload Report

 

Send to Analyze Impact

You can send the virtual indexes created in Optimize Indexes to Analyze Impact to evaluate the effect of the new indexes on other SQL or database performance.

 

To send results to Analyze Impact

  1. Select Optimize Indexes.
  2. Click Open a Saved SQL Workload and select a workload session. Click Open to open the session.
  3. Open the Results page by clicking .

  4. On the Results page, in the Results Summary pane, click Send to Analyze Impact. A new Analyze Impact session opens.

  5. The Indexing Change Details page is populated with the new indexes that you sent from the Optimize Indexes session. Use this page to modify the index or indexes. See Analyze the Impact of Additional Indexes for more information.

  6. When you have finished modifying the indexes, click to start collecting the SQL to evaluate. See Collect SQL and Run an Impact Analysis.

 

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

 

 

Related Topics

About Optimize Indexes

Review Optimize Indexes Results

 

Create Index Scripts

After Optimize Indexes finds indexes that optimize a SQL workload, you can create the scripts to use to generate the indexes in your database environment.

To create index scripts for new indexes

  1. Select Optimize Indexes.
  2. Click Open a Saved SQL Workload and select a workload session. Click Open to open the session.
  3. Open the Results page by clicking .

  4. Click to generate the index scripts.

    The Index Script page opens where you can 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 Optimize Indexes Results

About Optimize Indexes

 

Generate Report

After SQL Optimizer finds new indexes to optimize a SQL workload, you can generate a report that includes the workload summary, the recommended indexes, the index scripts, and the impacted SQL statements.

 

To generate an Optimize Indexes Workload Report

  1. Select Optimize Indexes.
  2. Click Open a Saved SQL Workload and select a workload session. Click Open to open the session.
  3. Open the Results page by clicking .

  4. On the Results page, in the Results Summary pane, click Publish Report. SQL Optimizer generates the report and opens it in the Report window.

  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 export the report as a PDF file

  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 Optimize Indexes Results

About Optimize Indexes

 

Verwandte Dokumente

The document was helpful.

Bewertung auswählen

I easily found the information I needed.

Bewertung auswählen