Tutorial: Optimize SQL (SQL Rewrite)
Tutorial: Optimize SQL (Plan Control)
Using SQL Rewrite mode in Optimize SQL consists of two steps. In the first step, SQL Optimizer generates semantically equivalent alternatives with unique execution plans for your original SQL statement. An Oracle cost estimate displays for each alternative generated. In the second step, SQL Optimizer executes the alternatives to test each statement's performance. This provides execution times and run time statistics that allow you to find the best SQL statement for your database environment.
Tip: The Oracle cost only provides an estimate of resource usage to execute a SQL statement. Since statements with higher cost may perform better, you should test alternatives generated to determine the best statements for your database environment.
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.The Select Connection and Schema window displays.
Click to optimize the SQL statement.
Click after SQL Optimizer completes the SQL rewrite process to compare your original SQL statement with the alternatives generated.
To test the alternatives SQL Optimizer generates, you can test run one or more selected alternatives to obtain actual execution statistics. This function does not affect network traffic since SQL Optimizer can provide these statistics without having to retrieve result sets from the database server. Additionally, data consistency is maintained when using SELECT, SELECT INTO, INSERT, DELETE, and UPDATE statements because these statements run in a transaction that is rolled back after execution.
To test a SQL statement alternative
The Test Run Settings dialog opens. Select criteria to apply to this test run. You can answer the questions on the Usage and Symptom page to allow SQL Optimizer to determine settings for you. Or you can click the Customize Test Run Settings link at the bottom of the page to manually specify test run settings.
Tip: You can specify default values for some Test Run Settings options by clicking and selecting Optimize SQL | Test Run.
Review the execution statistics in the Alternatives pane.
Using Plan Control mode in Optimize SQL consists of two steps. In the first step, SQL Optimizer generates execution plan alternatives for your SQL statement without changing the source code. You can then execute the alternatives to retrieve run time statistics and identify the best alternative for your database environment. In the second step, you can use Plan Control mode to deploy the execution plan to the Manage Plans module as an Oracle plan baseline.
Note: This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions.
Select Plan Control from the Optimize SQL start page.
Note: If the start page does not display, click the arrow beside and select New Plan Control Session.
Enter a SQL statement in the Original SQL pane.
Tip: Select the This SQL is contained inside a PL/SQL block checkbox if your SQL statement originated from a PL/SQL block. Selecting this checkbox ensures that the SQL text for the baseline you create matches the SQL text in your database.
Click to generate alternative execution plans for your SQL statement. The Select Connection and Schema window displays.
Click to execute all alternative execution plans to retrieve run time statistics.
Review the run time statistics in the Plans pane to identify the best alternative.
Click .
Review the following for additional information:
Deploy | Description |
---|---|
Select a plan to deploy |
Click |
Performance comparison |
Use this to review the performance of selected plans against the original. |
Mark the plan as |
Review the following for additional information:
|
Plan name |
Enter a name for the plan. |
Description |
Enter a description for this plan. |
SQL Optimizer identifies columns to use as index alternatives for a SQL statement after it analyzes SQL syntax, relationships between tables, and selectivity of data. SQL Optimizer then combines identified alternatives into index sets.
To generate an index alternative
Select the Optimize SQL tab in the main menu.
Enter a SQL statement in the Alternative Details pane.
Click . The Select Connection and Schema window displays.
Select a connection and schema to use.
Select Index Details in the SQL Information pane to view index generation information.
To test an index, select it in the Alternatives pane and click .
Note: The Test Run function allows you to test an index set SQL Optimizer generated. It physically creates the indexes on the database, runs the SQL statement, retrieves execution statistics, and drops the indexes. Since this process physically creates indexes on your database, it may impact performance of other SQL statements.
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Feedback 이용 약관 개인정보 보호정책 Cookie Preference Center