Evolving SQL Plans

A SQL plan baseline for a SQL statement usually starts with a single accepted plan. However, some SQL statements perform well when executed with different plans under different conditions. For example, a SQL statement with bind variables whose values result in different selectivities may have several good plans. Creating a materialized view or an index or repartitioning a table may make current plans more expensive than other plans.

If new plans were never added to SQL plan baselines, then the performance of some SQL statements might degrade. Thus, it is sometimes necessary to evolve newly found plans to see if they should be added to SQL plan baselines. Plan evolution prevents performance regressions by verifying the performance of a new plan before including it in a SQL plan baseline.

Plan evolution consists of the following distinct steps:

1. Verifying that unaccepted plans perform at least as well as accepted plans in a SQL plan baseline.

2. Adding unaccepted plans in the plan history to the plan baseline as accepted plans when they have been proven to perform as well as previously accepted plans.

You can evolve a plan manually or you can use the SQL Plan Management (SPM) Evolve Advisor.

By default, the SPM Evolve Advisor runs daily in the scheduled maintenance window. It ranks all unaccepted plans, and then performs test executions of as many plans as possible during the window. The evolve advisor selects the lowest-cost accepted plan in the SQL plan baseline to compare against each unaccepted plan. If a plan performs sufficiently better than the existing accepted plan, then the database automatically accepts it.

See Also:

• Oracle Database SQL Tuning Guide for more information on configuring SPM Evolve Advisor and on running it manually by using APIs

To evolve plans manually:

1. Access the Database Home page.

   See "Accessing the Database Home Page" 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. In the table, select one or more SQL plans that have No in the Accepted column and then click Evolve.

   The Evolve SQL Plan Baselines page appears.

5. Specify the options to perform.

   • For Verify Performance, select one of the following:

     • Select Yes to have the database verify that the plan performs as good as or better than the current baseline plan.

     • Select No to automatically evolve the plan regardless of how it performs.

   • For Time Limit, select one of the following:

     • Auto to have the database determine how long to spend verifying the performance of the unaccepted plan.

     • Unlimited to run the verification to completion regardless of how long it takes.

     • Specify to specify a time limit for the verification process. Enter a value in minutes in the associated field.

   • For Action, select one of the following:

     • Report and Accept to have the database accept the plan and create a report.

     • Report Only to have the database create a report but not accept the plan.

   Click OK to implement the options.

   A report appears. After viewing the report, click Return to return to the SQL Plan Baseline subpage.