sql plan baselines(计划基线）Oracle 原文

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 efficient plans. 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 newoptimizer 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.

You can have the database capture SQL plan baselines automatically or you can load  SQL execution plans manually.
To capture SQL plan baselines automatically:
1. Access the Database Home page.
See Oracle Database 2 Day DBA for more information.
2. Select Performance, then SQL, and then SQL Plan Control.
If the Database Login page appears, then log in as a user with administrator  privileges. 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.
To load SQL execution plans manually, the Capture SQL Baselines setting must be FALSE.
To manually load SQL execution plans:
1. Access the Database Home page.
See Oracle Database 2 Day DBA for more information.
2. Select Performance, then SQL, and then SQL Plan Control.
3. Click SQL Plan Baseline.
The SQL Plan Baseline subpage appears.
4. Click Load.
The SQL Plan Control page appears.

5. Select the SQL plan baselines to be loaded by completing the following steps:
a. Under Load SQL Plan Baselines, select Load plans from SQL Tuning Set (STS).
In this example, load plans from the SQL tuning set that you created in
"Creating a SQL Tuning Set" on page 10-7.
b. In Job Name, enter a name for the job. For example, enter SPM_LOAD_TEST.
c. Under Schedule, select Immediately.
d. 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

6. Optionally, fix the execution plan of a baseline to prevent the database from using
an alternative SQL plan baseline. Complete the following steps:
a. Select a SQL plan baseline that is not fixed.
b. Select Fixed - Yes from the list preceding the baseline table.
c. Click Go.
The table is refreshed to show the SQL execution plan with the value YES in the
Fixed column of the table.
See Also:
■ Oracle Database Performance Tuning Guide to learn how to use SQL
plan management