Plan Control sessions use the SQL Plan Management feature introduced in Oracle 11g to optimize execution plans and deploy plan baselines for SQL statements without changing the original source code. This feature is particularly useful for third-party applications where you do not have access to the source code.
Important: You can only use Plan Control mode with an Oracle 11g or later connection.
The process begins with SQL Optimizer analyzing your original SQL statement to generate execution plan alternatives. SQL Optimizer then applies a set of Oracle hints to each execution plan alternative to generate additional alternatives. Once SQL Optimizer completes this process, you can execute the alternatives to retrieve run time statistics for each execution plan alternative and compare the alternatives to identify the best one for your database environment. You can then deploy the selected alternative as a plan baseline to the Manage Plan module.
This topic focuses on information that may be unfamiliar to you. It does not include all step and field descriptions..
To select a connection
Click .
Review the following for additional information:
Select Connection | Description |
Connection |
Click Tip: Click |
Select Schema | Description |
Schema |
Click |
Related Topics
Plan Control sessions optimize your SQL statement by generating execution plan alternatives for the statement. You can then select an alternative execution plan for your SQL statement and deploy it as a plan baseline to improve database performance.
To generate execution plan alternatives
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 is a PL/SQL checkbox to indicate 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 retrieve the execution plan for your SQL statement.
(Optional) When you create a new Plan Control session, your default Intelligence Level setting is displayed in the top-right corner of the session window. To change the Intelligence Level setting for the current session, click .
When you change the setting for the current session, the default setting remains unchanged. To specify a default setting, see Intelligence (Plan Control).
Click to generate execution plan alternatives.
Note: Click to stop the plan generation process.
To clear the Plan Control window
Right-click within the SQL Text pane and do one of the following:
Once SQL Optimizer generates execution plan alternatives in an Optimize SQL - Plan Control session, you can test run the alternatives to retrieve run-time statistics and identify the best alternative for your database environment.
SQL Optimizer allows you to test run a single alternative, a selected group of alternatives, or all the alternatives at one time.
To test run execution plan alternatives
After generating execution plan alternatives in a Plan Control session (see Generate Execution Plan Alternatives), use one of the following methods to test run the plan alternatives.
Notes:
To clear the Plan Control window
Right-click within the SQL Text pane and do one of the following:
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Feedback 利用規約 プライバシー Cookie Preference Center