Toad World Blog

SQL Optimizer for Oracle - Send Indexes to Analyze Impact

Jan 11, 2013 7:06:00 AM by Steve Hilker

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

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

  3. Click TB_AnalyzeIndexImpact.png-550x0 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.

  5. When you have finished modifying the index, click 0743.B_AI_NextSelectSQL.png-550x0 to start collecting the SQL to evaluate.

To send indexes to Analyze Impact from Optimize Indexes

  1. Generate index alternatives in Optimize Indexes.

  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.

  5. When you have finished modifying the indexes, click 0743.B_AI_NextSelectSQL.png-550x0 to start collecting the SQL to evaluate.

Tags: SQL Optimizer for Oracle Wiki Tutorial

Steve Hilker

Written by Steve Hilker

Steve Hilker was a Product Manager for Quest Software. Steve has over 35 years technical experience spanning application development, system administration, database management and various management positions at several software companies. Steve was the founder of RevealNet, best known for its desktop knowledge bases and unique database tools such as PL/Formatter. RevealNet was acquired by Quest Software in 2001. He's had the pleasure of being the product manager for many of Quest's database tools.

In his private life, Steve enjoys oil painting and living on a small farm in a pre-Civil war manor home in Fredericksburg, Virginia.