SQL Optimizer Window

SQL Optimizer Window

View the SQL Optimizer Window

SQL Optimizer helps you optimize an SQL statement. That is, it rewrites the SQL to produce tweaked versions, or alternatives, of the original SQL and suggests indexes, all of which you can test to determine whether performance improves.

The SQL Optimizer window is the point at which you start the optimization process. This window shows the original SQL statement that you have entered manually or extracted from another facility, such as Toad or SQL Scanner. From this window, run either or both of these functions to optimize the SQL:

• SQL Rewrite function to create virtual SQL alternatives
• Index Expert function to create virtual index sets that display as alternatives as well

To help you determine the best-performing version of your SQL, the SQL Optimizer window lets you do the following:

• Review the access plan, plan statistics, and optimized SQL for the original SQL and each alternative
• Execute the original SQL and the index-set and SQL alternatives to find the fastest alternative
• Compare SQL text, access plans, run-time statistics, costs, and other overhead of the original SQL to the index-set and SQL alternatives, as well as compare alternatives with each other
• Compare the execution results of the original SQL to the index-set and SQL alternatives

The SQL Optimizer window consists of the following:

SQL Tab

Access Plan Tab

Plan Statistics Tab

Summary Tab

Compare Tab

Execution Result Tab

Notes:

• You can optimize only one SELECT, DELETE, UPDATE, or INSERT SQL statement in the SQL Optimizer window.
• To optimize SQL statements within database objects, SQL PL , or other application source code, use the SQL Scanner module to identify potential problematic SQL statements within the code. Then optimize each SQL statement in the SQL Rewrite function. 
• In Toad, to open a SQL Optimizer session on the SQL in a view, trigger, or MQ table, click in the Create or Alter window for the object.
• In Toad, to open a SQL Optimizer session on an SQL statement in a package, click on the SQL tab in the Database Explore or Object Explorer for the specific package.