SQL Optimizer for Oracle 9.3.1 - User Guide

Tutorial: Optimize Indexes for SGA Workload

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

Define a new SGA Workload

1. If you have already created a new session, skip to 3. Otherwise, select Optimize Indexes.
2. Click .
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 .

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.

   • Review collected SQL. This step is optional. You can modify the SQL workload if you want.
   • Specify search process criteria.

(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. 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.

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.

Review Optimize Indexes Results

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.