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

Index Type

Use this page of the Options dialog to specify default index settings to be used in index generation for SQL Rewrite. Settings specified through the Options dialog apply to new SQL Rewrite sessions.

Settings specified through the Current Session dialog override the global settings and apply to the current session only. See Optimize SQL Statements for more information.

To set index type options

  1. Click in the main menu.

  2. Select Optimize SQL | Index Generation | Index Type.

  3. Specify options for index type. Review the following for additional information:

    Index Type

    Description

    B-Tree index

    Select to generate index alternatives that are B-Tree indexes.

    Bitmap index

    Select to generate index alternatives that are bitmap indexes.

    Parallel degree

    Select to generate index alternatives in parallel and enter a parallel degree.

    Key-Compressed for B-Tree index when selectivity is less than or equal to

    Select to key-compress a B-Tree index alternative when the selectivity of the data is less than or equal to the value you specify.

 

Related Topic

Intelligence (Index Generation)

Index Generation Options

Intelligence (Plan Control)

Use this page of the Options dialog to specify a default Intelligence level to use for Plan Control. Settings specified through the Options dialog apply to new Plan Control sessions.

Settings specified through the Current Session dialog override the global settings and apply to the current session only. See Generate Execution Plan Alternatives for more information.

To set intelligence options

  1. Click in the main menu.

  2. Select Optimize SQL | Plan Control | Intelligence.

  3. Select a method to use to specify the total quota.

    Intelligence

    Select one of the following:

    • Predefined Settings—Select this option to use a predefined Total Quota based on the Intelligence Level you select. Then select an Intelligence Level.
    • Use Extended Total Quota—Select to use a quota even higher than level 5. Then enter a value in the Total Quota field to indicate the maximum number of execution plan alternatives generated.

 

 

 

Test Run

When you test run SQL, SQL Optimizer provides various options to allow you to customize the test-run behavior according to your environment and objectives. These options are presented in the Test Run Settings dialog that opens when you initiate the test-run process (for multiple SQL).

Note: The customized options are accessed when you click the Customize Test Run Settings link at the bottom of the Test Run Settings dialog.

Use this Test Run page in the Options dialog to specify default values for some of the custom test run options. If default values are specified, the Test Run Settings dialog opens with these values pre-populated.

To specify test-run options (default values)

  1. Click in the main menu.
  2. Select Optimize SQL | Test Run.

  3. Specify default values for one or more of the options listed. Review the following for additional information:

    Default number of rows to retrieve

    Instead of retrieving all rows, you can instruct SQL Optimizer to retrieve the first N rows. Use this field to specify a default value.

    Purpose: This option is useful for simulating the performance of SQL which normally retrieves only a specific number of rows.

    Default threshold to run SQL for a second time

    If you select Dynamically determine the number of times to test run (running at most twice) in the Test Run Settings dialog (Run Time Retrieval Method tab), you can instruct SQL Optimizer to Run all SQL twice if original SQL runs faster than N seconds.

    Specify a default value for N here. If the run time of the original SQL is less than this threshold, the original SQL and all alternatives run twice, and the second run time is used.

    Test run SQL multiple times

    If you select Dynamically determine the number of times to test run (may run multiple times) in the Test Run Settings dialog (Run Time Retrieval Method tab), you can instruct SQL Optimizer to Run all SQL N times when original SQL is faster than N seconds

    Default number of times to test run SQL multiple times—Specify a default value for N (number of run times).

    Default threshold to test run SQL multiple times—Specify a default value for N (number of seconds). If the run time of the original SQL is less than this threshold, the original SQL and all alternatives run multiple times, and the average run time is used.

    Test Run Different Bind Values

    Specify defaults to use when testing SQL alternatives using different bind values.

    Default percentage of original SQL total run time—Specify a default value for this SQL termination option. This value is used in the Order and Termination tab of the Test Run Settings dialog.
    Ignore record count difference from original SQL

    Select this option if you want SQL Optimizer to ignore a record count mismatch between the alternative and the original SQL. No warning message is displayed in the Status column.

    Clear this check box to display a warning message when there is a record count mismatch.

See Test Run Settings for more information about the options in the Test Run Settings dialog.

  

Related Topics

Test Run Settings

Best Practices

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating