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

Tutorial: Generate Indexes

SQL Optimizer identifies columns to use as index alternatives for a SQL statement after it analyzes SQL syntax, relationships between tables, and selectivity of data. SQL Optimizer then combines identified alternatives into index sets.

To generate an index alternative

  1. Select the Optimize SQL tab in the main menu.

  2. Enter a SQL statement in the Alternative Details pane.

  3. Click . The Select Connection and Schema window displays.

  4. Select a connection and schema to use.

  5. Select Index Details in the SQL Information pane to view index generation information.

  6. Select an index set to view index name, details and generation script in the Alternative Details pane.

  7. To test an index, select it in the Alternatives pane and click .

    Note: The Test Run function allows you to test an index set SQL Optimizer generated. It physically creates the indexes on the database, runs the SQL statement, retrieves execution statistics, and drops the indexes. Since this process physically creates indexes on your database, it may impact performance of other SQL statements.

 

Related Topics

About Generating Indexes

Generate Index Sets

Execute Index Candidates

 

Tutorial: Deploy Outlines

The Deploy Outline function in Optimize SQL improves SQL statement performance without changing your original source code. Using Optimize SQL, you can generate SQL statements that are semantically equivalent to your original SQL statement with alternative execution plans. Once you identify the best alternative for your database environment, you can deploy it as a stored outline to use with your original statement.

To deploy an outline

  1. Select the Optimize SQL tab in the main window.

  2. Click the arrow beside and select New SQL Rewrite Session.

  3. Enter your original SQL statement in the Alternative Details pane and click . The Select Connection and Schema window displays.

  4. Select a connection and schema to use.

  5. Right-click the alternative you want to deploy as an outline in the Alternatives pane and select Deploy Outline. The Deploy Outline window displays.

  6. Review the following for additional information:

    Outline name Enter a name for the stored outline
    Category

    Click and select a previously created category or enter a new category name.

    Notes:

    • The default category name is SQL_OPTIMIZIER.

    • You can add the outline to a disabled category until you test the SQL statement with and without using the outline.
  7. Click .

Note: You can use the Outline Management feature in Manage Plans to enable and disable categories or to move outlines to different categories.

 

Related Topics

Deploy Outlines

 

Tutorial: Best Practices

You can use Best Practices 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

Note: The Best Practices function is only available in SQL Rewrite mode in Optimize SQL.

To view best practices

  1. Select the Optimize SQL tab in the main window.

  2. Click .

    Tip: 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.

  3. Enter a SQL statement into the Alternative Details pane.

  4. Click . The Select Connection and Schema window displays.

  5. Select a connection and schema to use.

  6. Review the recommendations provided.

 

Related Topic

View Best Practices

Tutorial: Send Indexes to Analyze Impact

You can analyze the impact of new indexes on your SQL statement's execution plans before you physically create the indexes on your database. You can create indexes in Optimize SQL or Optimize Indexes and then send the index or indexes to Analyze Impact to evaluate the impact on your SQL workload.

Use the Optimize SQL module to generate index alternatives for a single SQL statement. Or use the Optimize Indexes module to generate index alternatives for a SQL workload or group of SQL statements.

To send indexes to Analyze Impact from Optimize SQL

  1. Generate index alternatives using the Index Generation feature in Optimize SQL. See Tutorial: Generate Indexes in Optimize SQL for more information.

  2. Select the virtual index alternative you want to use for the analysis in the Alternatives pane in Optimize SQL.

  3. Click in Optimize SQL. A new Analyze Impact session opens.

  4. The Indexing Change Details page is populated with the new index that you sent from the Optimize SQL session. Use this page to modify the index, if necessary. See Analyze the Impact of Additional Indexes for more information.

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

To send indexes to Analyze Impact from Optimize Indexes

  1. Generate index alternatives in Optimize Indexes. See About Optimize Indexes for more information.
  2. In the Optimize Indexes session, select the Results tab.
  3. On the Results page, in the Results Summary pane, click Send to Analyze Impact. A new Analyze Impact session opens.
  4. 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, if necessary. See Analyze the Impact of Additional Indexes for more information.
  5. When you have finished modifying the indexes, click to start collecting the SQL to evaluate. See Collect SQL and Run an Impact Analysis.

 

Related Topics

About Analyze Impact

About Optimize Indexes

Analyze Index Impact (SQL Rewrite)

 

Verwandte Dokumente

The document was helpful.

Bewertung auswählen

I easily found the information I needed.

Bewertung auswählen