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

Select SQL Workload Source

Step one. Before defining a new SQL workload, select the type of source from which you want to collect SQL. You can instruct SQL Optimizer to gather SQL from one of several different sources.

To select a SQL source and create a new session

  1. Select Optimize Indexes.
  2. Click Define New SQL Workload.
  3. Select the type of SQL workload source from which you want to collect SQL. Select from the following:

    SQL Workload Source Description
    AWR

    Collect SQL from the Oracle Automatic Workload Repository (AWR).

    Caution: By default, Oracle installs and enables some features in the database which may not be covered by your existing licenses, such as Automatic Workload Repository (AWR).
    Selecting AWR as a SQL workload source will instruct SQL Optimizer to retrieve SQL from the AWR. Using the AWR may increase your Oracle licensing fees. Being properly licensed by Oracle is your (organization's) responsibility.

    SGA Collect SQL from the Oracle System Global Area.
    Source Code Scan and collect SQL statements from source code, a database object, a file, or the clipboard.
  4. Specify the criteria for collecting a SQL workload. See one of the following topics (based on the source you selected):

    Optimize Indexes for AWR Workload

    Optimize Indexes for SGA Workload

    Optimize Indexes from Scanned Code Workload

 

 

Related Topics

Optimize AWR Workload

Steps two, three, and four. The Optimize Indexes module allows you to collect SQL statements from the Automatic Workload Repository (AWR) of an Oracle database. Oracle uses the AWR to collect and maintain performance statistics. By default, every hour an Oracle database collects a snapshot of performance data. These snapshots are stored in the AWR. For more information on Oracle shapshots and the AWR, see your Oracle database documentation.

Use Optimize Indexes to define a SQL workload from an Oracle AWR and then search for new indexes to optimize the SQL workload performance.

Step two.

Define a new AWR workload

  1. If you have already created a new session, skip to 3. Otherwise, select Optimize Indexes. Click Define New SQL Workload.
  2. Click AWR.
  3. Enter a SQL workload name and select a database connection from which to collect SQL. The database must have AWR access.

    Note: Top N SQL displays the Top N setting for the database—the setting which specifies the number of SQL retained in the AWR for each category. If you want to modify this setting for a database, use the MODIFY_SNAPSHOT_SETTINGS system procedure.

  4. Specify SQL filter criteria. Review the following for additional information:

    SQL filter criteria Description

    Date

    Select a date from which to choose snapshots. After you select a date, Optimize Indexes displays all the snapshots available for the selected 24 hour period.

    Select SQL for parsing schema

    You must select a parsing schema before collecting SQL.

    Module, Action

    Use these fields to filter the available SQL before selecting snapshots.

    Default = All

  5. Select the SQL snapshots from which you want to collect SQL statements. Click a snapshot in the graph to select it. Click the selected snapshot again to deselect it.

    Notes:

    • Use the cursor to hover over a snapshot to display snapshot details.
    • Click List snapshot data to display snapshots in a grid format. Right-click a column heading to select options for filtering, sorting, or displaying.
    • You can display additional data in the graph (or in the grid). To do this, select/deselect data options from the group of options at the bottom of the page.

    • Select Total in the data options to display the total activity (the activity for all modules, actions, etc.).
  6. To begin collecting SQL, click configure search process .
  7. Optimize Indexes immediately asks you to choose a second database connection. Select a database connection to use to retrieve execution plans and evaluate index alternatives.

    Note: This step allows you to select an alternate database (other than the one used to collect SQL) to run the index optimization process. In other words, you can collect your SQL from one database, but run the optimize indexes process on a second database. The second database must have the same application environment and data volume statistics as the first.

  8. The Search Process page opens. Proceed with one of the following steps:

Step three.

(Optional) Review collected SQL

  1. To review and modify your SQL workload, click Review/adjust workload SQL. Review the following for additional information:
    Pane / Button Description

    SQL Tuning Set Pane /

    SQL Workload Pane

    This pane displays the list of SQL in the workload.

    Include—To include a SQL statement in the optimize indexes process, select the checkbox in the Include column.

    SQL Text Pane This pane displays the SQL text of the selected SQL statement.
    Parsed Execution Plan Pane

    This pane displays the execution plan of the selected SQL statement.

    SQL Statistics—Select this tab to display statistics of the selected statement.

    Select to send your SQL to Optimize SQL or Batch Optimize SQL. Click the arrow for more options.

    Notes:

    • Click to return to the SQL Collection page. (On the SQL Collection page, click Review SQL to go back to the SQL Review page.)
    • To collect a new SQL workload, return to the SQL Collection page and click Collect SQL again.

      Caution: If you click Collect SQL again, your current SQL workload will be lost, as well as any analysis data already gathered for the current session.

  2. After reviewing the collected SQL, select the Search Process tab to return to the Search Process page.

Step four.

Specify search process criteria

  1. With the Search Process page open, locate the Search Process Control pane. Use these options to specify criteria for SQL Optimizer to use to find new indexes. Review the following for additional information:
    Search Process Control Pane Description

    Primary goal (minimize)

    Select a primary goal. SQL Optimizer evaluates indexes to optimize the workload based on this primary goal.

    Scheduled to run on

    Click the link to schedule a time to run the optimization process on this workload. Select a start time, end time, duration, and interval.

    Stop condition(s)

    Select criteria to determine when to end the optimization process. You can select one or more conditions. The default setting is When no further improvement found in 1:00:00.

    Note: If you do not specify a Stop condition or an End date, the optimization process will run indefinitely.

    Advanced options

    Click this link to select the number of columns in a composite index, the maximum number of indexes to recommend, and an index-type time distribution.

    Recommend function-based indexes—Select this checkbox if you want SQL Optimizer to include function-based indexes.

    Search time distribution between B-Tree and Bitmap indexes—Select a pre-defined or user-defined time distribution. Determines the percentage of time spent searching for B-Tree vs Bitmap index types.

  2. Click Start to begin searching for new indexes to improve the workload performance.

 

Go to Step five. Review Optimize Indexes Results.

 

 

Related Topics

Review Optimize Indexes Results

 

Optimize SGA Workoad

Steps two, three, and four. If you selected the SGA as a workload source, define the new workload and specify the search process criteria.

Step two.

Define a new SGA Workload

  1. If you have already created a new session, skip to 3. Otherwise, select Optimize Indexes.
  2. Click SGA.
  3. Enter a SQL workload name and select a database connection from which to collect SQL.
  4. Specify SQL filter criteria. To modify filter criteria, click the Edit link beside each filter. The available options display as bars in the graph. Click an option (bar) in the graph to select it. The selected option then displays in the filter criterion field in the top pane. Review the following for additional information:

    SQL filter criteria pane Description

    Collect SQL for parsing schema

    You must select a parsing schema before collecting SQL.

    Module, Action

    Use these fields to filter the available SQL statements.

    Default = All

    Notes:

    • Use the cursor to hover over an option (bar) in the graph to display option details.
    • Click List statistics data to display the options for the currently selected filter criterion in a grid format.
    • You can display additional data in the graph (or in the grid). To do this, select/deselect data options from the group of options at the bottom of the page.

  5. To begin collecting SQL, click configure search process.
  6. Optimize Indexes immediately asks you to choose a second database connection. Select a database connection to use to retrieve execution plans and evaluate index alternatives.

    Note: This step allows you to select an alternate database (other than the one used to collect SQL) to run the index optimization process. In other words, you can collect your SQL from one database, but run the optimize indexes process on a second database. The second database must have the same application environment and data volume statistics as the first.

  7. The Search Process page opens. Before specifying search process criteria, you can review and modify your SQL workload.

Step three.

(Optional) Review collected SQL

  1. To review and modify your SQL workload, click Review/adjust workload SQL. Review the following for additional information:
    Pane / Button Description

    SQL Tuning Set Pane /

    SQL Workload Pane

    This pane displays the list of SQL in the workload.

    Include—To include a SQL statement in the optimize indexes process, select the checkbox in the Include column.

    SQL Text Pane This pane displays the SQL text of the selected SQL statement.
    Parsed Execution Plan Pane

    This pane displays the execution plan of the selected SQL statement.

    SQL Statistics—Select this tab to display statistics of the selected statement.

    Select to send your SQL to Optimize SQL or Batch Optimize SQL. Click the arrow for more options.

    Notes:

    • Click to return to the SQL Collection page. (On the SQL Collection page, click Review SQL to go back to the SQL Review page.)
    • To collect a new SQL workload, return to the SQL Collection page and click Collect SQL again.

      Caution: If you click Collect SQL again, your current SQL workload will be lost, as well as any analysis data already gathered for the current session.

  2. After reviewing the collected SQL, select the Search Process tab to return to the Search Process page.

Step four.

Specify search process criteria

  1. With the Search Process page open, locate the Search Process Control pane. Use these options to specify criteria for SQL Optimizer to use to find new indexes. Review the following for additional information:
    Search Process Control Pane Description

    Primary goal (minimize)

    Select a primary goal. SQL Optimizer evaluates indexes to optimize the workload based on this primary goal.

    Scheduled to run on

    Click the link to schedule a time to run the optimization process on this workload. Select a start time, end time, duration, and interval.

    Stop condition(s)

    Select criteria to determine when to end the optimization process. You can select one or more conditions. The default setting is When no further improvement found in 1:00:00.

    Note: If you do not specify a Stop condition or an End date, the optimization process will run indefinitely.

    Advanced options

    Click this link to select the number of columns in a composite index, the maximum number of indexes to recommend, and an index-type time distribution.

    Recommend function-based indexes—Select this checkbox if you want SQL Optimizer to include function-based indexes.

    Search time distribution between B-Tree and Bitmap indexes—Select a pre-defined or user-defined time distribution. Determines the percentage of time spent searching for B-Tree vs Bitmap index types.

  2. Click Start to begin searching for new indexes to improve the workload performance.

 

Go to Step five. Review Optimize Indexes Results.

 

 

Related Topics

About Optimize Indexes

 

Optimize Scan Code Workload

If you selected to scan code for the SQL workload, collect workload SQL and specify the search process criteria using the following steps.

Step two.

Scan code for a new workload

  1. If you have already created a new session, skip to 3. Otherwise, select Optimize Indexes.
  2. Click Scan code.
  3. Select a source database connection.
  4. (Optional) Click Scan Options to specify options before scanning SQL.
  5. Select a method for scanning and collecting SQL.
    • Scan from Clipboard—Select this option to paste the SQL that you have previously copied to the clipboard.
    • Scan from File—Select this option to browse to and select a SQL file.
    • Scan from DB Object—Select this option to browse to and select a database object.
  6. After the SQL is scanned, the SQL Selector dialog opens displaying the list of scanned SQL. Select the checkbox to the left of each listed SQL to include it in your workload. Click on a scanned SQL in the left pane to highlight its SQL text in the right pane.

    Note: Enter a value in the REF (Relative Execution Frequency) column for each SQL. The REF will be used as an approximation of the number of times a SQL statement is executed relative to the other statements.

  7. Click OK to close the dialog. The Scan Code window now displays the list of SQL statements in your workload. To add additional SQL, select an additional method and source.

    Note: Click Remove to remove a selected SQL from the workload.

  8. Click configure search process.
  9. Optimize Indexes immediately asks you to choose a second database connection. Select a database connection to use to retrieve execution plans and evaluate index alternatives.

    Note: This step allows you to select an alternate database (other than the one used to collect SQL) to run the index optimization process. In other words, you can collect your SQL from one database, but run the optimize indexes process on a second database. The second database must have the same application environment and data volume statistics as the first.

  10. The Search Process page opens. Proceed with one of the following steps:

Step three.

(Optional) Review collected SQL

  1. To review and modify your SQL workload, click Review/adjust workload SQL. Review the following for additional information:
    Pane / Button Description

    SQL Tuning Set Pane /

    SQL Workload Pane

    This pane displays the list of SQL in the workload.

    Include—To include a SQL statement in the optimize indexes process, select the checkbox in the Include column.

    SQL Text Pane This pane displays the SQL text of the selected SQL statement.
    Parsed Execution Plan Pane

    This pane displays the execution plan of the selected SQL statement.

    SQL Statistics—Select this tab to display statistics of the selected statement.

    Select to send your SQL to Optimize SQL or Batch Optimize SQL. Click the arrow for more options.

    Notes:

    • Click to return to the SQL Collection page. (On the SQL Collection page, click Review SQL to go back to the SQL Review page.)
    • To collect a new SQL workload, return to the SQL Collection page and click Collect SQL again.

      Caution: If you click Collect SQL again, your current SQL workload will be lost, as well as any analysis data already gathered for the current session.

  2. After reviewing the collected SQL, select the Search Process tab to return to the Search Process page.

Step four.

Specify search process criteria

  1. With the Search Process page open, locate the Search Process Control pane. Use these options to specify criteria for SQL Optimizer to use to find new indexes. Review the following for additional information:
    Search Process Control Pane Description

    Primary goal (minimize)

    Select a primary goal. SQL Optimizer evaluates indexes to optimize the workload based on this primary goal.

    Scheduled to run on

    Click the link to schedule a time to run the optimization process on this workload. Select a start time, end time, duration, and interval.

    Stop condition(s)

    Select criteria to determine when to end the optimization process. You can select one or more conditions. The default setting is When no further improvement found in 1:00:00.

    Note: If you do not specify a Stop condition or an End date, the optimization process will run indefinitely.

    Advanced options

    Click this link to select the number of columns in a composite index, the maximum number of indexes to recommend, and an index-type time distribution.

    Recommend function-based indexes—Select this checkbox if you want SQL Optimizer to include function-based indexes.

    Search time distribution between B-Tree and Bitmap indexes—Select a pre-defined or user-defined time distribution. Determines the percentage of time spent searching for B-Tree vs Bitmap index types.

  2. Click Start to begin searching for new indexes to improve the workload performance.

 

Go to Step five. Review Optimize Indexes Results.

 

 

Related Topics

About Optimize Indexes

 

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating