Managing SQL Execution Plans

SQL plan management is a preventative mechanism that records and evaluates execution plans of SQL statements over time. The database builds SQL plan baselines consisting of a set of existing plans known to be efficient. If the same SQL statement runs repeatedly, and if the optimizer generates a new plan differing from the baseline, then the database compares the plan with the baseline and chooses the best one.

SQL plan management avoids SQL performance regression. Events such as new optimizer statistics, changes to initialization parameters, database upgrades, and so on can cause changes to execution plans. These changes can cause SQL performance regressions that are difficult and time-consuming to fix manually. SQL plan baselines preserve performance of SQL statements, regardless of changes in the database.

To load SQL execution plans: 

1. From the Database Home page, click Server.

   The Server page appears.

2. Under Query Optimizer, click SQL Plan Control.

   The SQL Profile subpage of the SQL Plan Control page appears.

3. Click SQL Plan Baseline.

   The SQL Plan Baseline subpage appears.

4. Under Settings, click the link next to Capture SQL Plan Baselines.

   The Initialization Parameters page appears.

5. In the Value column of the table, select TRUE and then click OK.

   You are returned to the SQL Plan Baseline subpage, which now shows Capture SQL Baselines set to TRUE.

   Because you configured baselines to be captured, the database automatically keeps a history of execution plans for all SQL statements executed more than once.

6. Click Load.

   The SQL Plan Control page appears.

7. Select the SQL plan baselines to be loaded by completing the following steps:

   1. Under Load SQL Plan Baselines, select Load plans from SQL Tuning Set (STS).

   2. In Job Name, enter a name for the job. For example, enter SPM_LOAD_TEST.

   3. Under Schedule, select Immediately.

   4. Click OK.

   The SQL Profile subpage of the SQL Plan Control page appears.

   The table displays a list of SQL plans that are stored as SQL plan baselines.

8. Optionally, fix the execution plan of a baseline to prevent the database from using an alternative SQL plan baseline. Complete the following steps:

   1. Select a SQL plan baseline that is not fixed.

   2. Select Fixed - Yes from the list preceding the baseline table.

   3. Click Go.

   The table is refreshed to show the SQL execution plan with the value YES in the Fixed column of the table.