Use SQL Rewrite sessions to optimize SQL statements by using SQL Optimizer's Artificial Intelligence engine to rewrite SQL statement syntax. This process begins with SQL Optimizer analyzing your original SQL statement and generating a list of semantically equivalent statement alternatives. The SQL rewrite process continues until SQL Optimizer cannot generate additional alternatives. SQL Optimizer then applies a set of Oracle hints to each alternative to generate additional alternatives.
Once SQL Optimizer completes the SQL rewrite process, you can test run the alternatives to retrieve execution plans and run time statistics. You can compare execution times and run time statistics for alternatives to identify the best statements for your database environment.
Although different SQL statement alternatives produce the same results, Oracle may use different paths to retrieve data for each alternative. The database structure, indexes, and data volumes affect execution statistics. Therefore, you should test the alternatives with your database environment to find the best alternative.
This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions..
To select a connection
Review the following for additional information:
Click to select a previously created connection.
Tip: Click to open the Connection Manager to create a new connection. See Connect to the Database for more information.
|Click to select a schema for the connection.|
You can use SQL Rewrite Sessions in Optimize SQL to generate semantically equivalent SQL alternatives with unique execution plans from your original SQL statement.
You can then test run the alternatives to retrieve execution times and other run-time statistics for identifying the best SQL alternative for your database environment.
To learn how to optimize SQL statements and test run alternatives all in one step, see Automatically Optimize SQL Statements.
To generate and test run alternative SQL statements
Select the Optimize SQL tab in the main window.
Select SQL Rewrite from the Optimize SQL start page.
Note: If the start page does not display, click the arrow beside and select New SQL Rewrite Session.
Enter a SQL statement in the Alternative Details pane.
Click to retrieve the execution plan for your SQL statement.
(Optional) When you create a new SQL Rewrite session, your global default Intelligence Level settings are displayed in the top-right corner of the session window. To change settings for the current session only, click or .
Changing settings for the current session leaves the global settings unchanged. To specify global default values, see Intelligence (Optimize SQL).
Click to optimize (rewrite) the SQL statement or click to optimize the SQL statement and generate index alternatives in one step.
When SQL Optimizer is finished generating SQL alternatives, you can test run one or more alternatives to collect run-time statistics. Use one of the following methods:
Note: The statements are test run against the database during this process to obtain accurate run-time statistics. SQL Optimizer automatically rolls back any changes made to the database after it executes SELECT, SELECT INTO, INSERT, DELETE, or UPDATE statements.
If you sent your original SQL statement to SQL Optimizer from another Toad application, click to send the selected alternative SQL back to the original application.
To clear optimization results