Baselines and Better Plans

Use SQL plan management in Oracle Database 11g to optimize execution plans.

March/April 2009

Have you ever been in a situation in which some database queries that used to behave well suddenly started performing poorly? More likely than not, you traced the cause back to a change in the execution plan. Further analysis may have revealed that the performance change was due to newly collected optimizer statistics on the tables and indexes referred to in those queries.

And thoroughly humbled by this situation, have you ever made a snap decision to stop statistics collection? This course of action keeps the execution plans pretty much the same for those queries, but it makes other things worse. Performance of some other queries, or even the same queries with different predicates (the WHERE clauses), deteriorates because of suboptimal execution plans generated from stale statistics.

Whatever action you take next carries some risk, so how can you mitigate that risk and ensure that the execution plans for the SQL statements generated are optimal while maintaining a healthy environment in which optimizer statistics are routinely collected and all SQL statements perform well without significant changes (such as adding hints)? You may resort to using stored outlines to freeze the plan, but that also means that you‘re preventing the optimizer from generating potentially beneficial execution plans.

In Oracle Database 11g, using the new SQL plan management feature, you can now examine how execution plans change over time, have the database verify new plans by executing them before using them, and gradually evolve better plans in a controlled manner.

When SQL plan management is enabled, the optimizer stores generated execution plans in a special repository, the SQL management base. All stored plans for a specific SQL statement are said to be part of a plan history for that SQL statement.

Some of the plans in the history can be marked as “accepted.”When the SQL statement is reparsed, the optimizer considers only the accepted plans in the history. This set of accepted plans for that SQL statement is called a SQL plan baseline , or baseline for short.

The optimizer still tries to generate a better plan, however. If the optimizer does generate a new plan, it adds it to the plan history but does not consider it while reparsing the SQL, unless the new plan is better than all the accepted plans in the baseline. Therefore, with SQL plan management enabled, SQL statements will never suddenly have a less efficient plan that results in worse performance.

With SQL plan management, you can examine all the available plans in the plan history for a SQL statement, compare them to see their relative efficiency, promote a specific plan to accepted status, and even make a plan the permanent (fixed) one.

This article will show you how to manage SQL plan baselines—including capturing, selecting, and evolving baselines—by using Oracle Enterprise Manager and SQL from the command line to ensure the optimal performance of SQL statements.

The capture function of SQL plan management captures the various optimizer plans used by SQL statements. By default, capture is disabled—that is, SQL plan management does not capture the history for the SQL statements being parsed or reparsed.

Now let‘s capture the baselines for some SQL statement examples coming from one session. We will use a sample schema provided with Oracle Database 11g—SH—and the SALES table in particular.

First, we enable the baseline capture in the session:

alter session
set optimizer_capture_sql_plan_baselines = true;

Now all the SQL statements executed in this session will be captured, along with their optimization plans, in the SQL management base. Every time the plan changes for a SQL statement, it is stored in the plan history. To see this, run the script shown in Listing 1, which executes exactly the same SQL but under different circumstances. First, the SQL runs with all the defaults (including an implicit default optimizer_mode = all_rows). In the next execution, the optimizer_mode parameter value is set to first_rows. Before the third execution of the SQL, we collect fresh stats on the table and the indexes.

Code Listing 1: Capturing SQL plan baselines

alter session set optimizer_capture_sql_plan_baselines = true;
-- First execution. Default Environment
select * /* ARUP */ from sales
where quantity_sold > 1 order by cust_id;
-- Change the optimizer mode
alter session set optimizer_mode = first_rows;
-- Second execution. Opt Mode changed
select * /* ARUP */ from sales
where quantity_sold > 1 order by cust_id;
-- Gather stats now
begin
    dbms_stats.gather_table_stats (
        ownname            => 'SH',
        tabname             => 'SALES',
        cascade              => TRUE,
        no_invalidate      => FALSE,
        method_opt        => 'FOR ALL INDEXED COLUMNS SIZE AUTO',
        granularity          => 'GLOBAL AND PARTITION',
        estimate_percent => 10,
        degree                => 4
    );
end;
/
-- Third execution. After stats
select * /* ARUP */ from sales
where quantity_sold > 1 order by cust_id;

If the plan changes in each of the executions of the SQL in Listing 1, the different plans will be captured in the plan history for that SQL statement. (The /* ARUP */ comment easily identifies the specific SQL statements in the shared pool.)

The easiest way to view the plan history is through Oracle Enterprise Manager. From the Database main page, choose the Server tab and then click SQL Plan Control . From that page, choose the SQL Plan Baseline tab. On that page, search for the SQL statements containing the name ARUP , as in Figure 1, which shows the plan history for the SQL statements on the lower part of the screen.

Figure 1: SQL plan history

If you click the SQL plan names, such as SYS_SQL_PLAN_27a47aa154bc8843 , you will be able to see the details of the plan stored in the plan history. The important columns on the screen are as follows:

• Enabled indicates whether the plan is active.
• Accepted indicates whether the plan should be considered by the optimizer. If more than one plan is accepted, the optimizer will select the best plan among them.
• Fixed indicates whether the plan is to be used permanently for that SQL statement. If more than one plan is fixed, the optimizer will select the best plan among them.
• Auto Purge indicates whether the plan, if unused, will automatically be deleted from the plan history after a specified amount of time. Unused plans are automatically deleted from the plan history after a specified amount of time, unless auto purge is disabled. The time after which the unused plans are deleted is shown in Figure 1, next to the Plan Retention(Weeks) label. In this case, it is set to 53 weeks, but you can change it by clicking the Configure button.

You can also enable the capture and use of SQL plan baselines on this Oracle Enterprise Manager screen by clicking the appropriate links in the Settings section.

Note that you can also load plans to a SQL plan baseline from the cursor cache or from a SQL tuning set. When you manually load plans into a SQL plan baseline, these loaded plans are added as accepted plans. For more information, see Chapter 15, “Using SQL Plan Management” in Oracle Database Performance Tuning Guide.

With the SQL plan baselines captured, we now enable the optimizer to use them:

alter session set
optimizer_use_sql_plan_baselines = true;

With baseline use enabled, when the optimizer reparses a SQL statement, it examines the plans stored in the baseline for that SQL statement and chooses the best among them. This is where the most important benefit of baselines comes in. The optimizer also still reparses the SQL statements—the presence of a baseline does not prevent that—and if the newly generated plan is not found in the plan history of the SQL, it will be added, but not as “accepted.”So, if the newly generated plan is worse, the performance of the SQL will not be affected, because the plan is not used. However, in some cases, you may decide that the new plan is better, based on your knowledge of the data distribution or the application logic. For instance, suppose the plan was captured when the table was practically empty, making the optimizer quite appropriately choose an index scan. But you know that the application populates the table before invoking the SQL statement later and that a full table scan will actually be better for the plan in the long run. In such a case, you can examine the new plan later, and if it is better, you can accept it—after which the optimizer will consider it. That‘s why you get the best of both worlds: a good plan is always used, but if the optimizer generates a better one, it will be available for comparison.

If you don‘t want to use plans in the baseline for a SQL statement, you can use the following statement in the session prior to calling the SQL statement to disable the use of baselines:

alter session set
optimizer_use_sql_plan_baselines = false;

Listing 2 runs the same query twice—first with baselines enabled and then with baselines disabled, and you can see how the plan changes after the baselines are disabled. Initially the optimizer chose BITMAP INDEX FULL SCAN on the SALES_TIME_BIX index. After the baseline was disabled, the plan changed to TABLE ACCESS FULL on the SALES table, because that seems to be the best plan based on the optimizer stats and other variables affecting the optimizer right now . Earlier, when the baseline was enabled, the optimizer selected the best plan from the set of accepted plans stored in the baseline.

Code Listing 2: Use of SQL plan baseline

SQL> explain plan for select * /* ARUP */ from sales
  2    where quantity_sold > 1 order by cust_id;
Explained.
SQL> select * from table(dbms_xplan.display(null, null, 'basic'));
PLAN_TABLE_OUTPUT
---------------------------
Plan hash value: 143117509
--------------------------------------------------------------
| Id  | Operation                           | Name            |
--------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                 |
|   1 |  SORT ORDER BY                      |                 |
|   2 |   PARTITION RANGE ALL               |                 |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID| SALES           |
|   4 |     BITMAP CONVERSION TO ROWIDS     |                 |
|   5 |      BITMAP INDEX FULL SCAN         | SALES_TIME_BIX  |
--------------------------------------------------------------
-- Now disable baselines and look at the latest plan
SQL> alter session set optimizer_use_sql_plan_baselines = false;
Session altered.
SQL> explain plan for select * /* ARUP */ from sales
  2    where quantity_sold > 1 order by cust_id;
Explained.
SQL> select * from table(dbms_xplan.display(null, null, 'basic'));
PLAN_TABLE_OUTPUT
----------------------------
Plan hash value: 3803407550
--------------------------------------
| Id  | Operation             | Name  |
--------------------------------------
|   0 | SELECT STATEMENT      |       |
|   1 |  SORT ORDER BY        |       |
|   2 |   PARTITION RANGE ALL |       |
|   3 |    TABLE ACCESS FULL  | SALES |
--------------------------------------

After the baselines are created for a specific SQL statement, you can examine them by clicking their associated plan names in the Oracle Enterprise Manager screen shown in Figure 1 (Oracle Enterprise Manager -> SQL Plan Control page -> SQL Plan Baseline tab) and checking the details of the plan. If a specific plan is never going to be good, you can completely disable it by clicking the Disable button. You can click the Enable button if you change your mind later. The Drop button completely drops a plan from the SQL management base. Note that if a plan is not used, it will be purged automatically after the retention period has passed.

If you suspect that a plan in the current baseline is not optimal and that a different plan in the plan history may be better, you can compare the performance of the plans by using the evolve function (in Oracle Enterprise Manager -> SQL Plan Control page -> SQL Plan Baseline tab or using the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function from the command line). To use evolve, on the Oracle Enterprise Manager screen shown in Figure 1, select the plan you want to compare and click the Evolve button. The comparison is done between the plan the optimizer chooses to be the best and the plan you select. The function produces a report, shown in Listing 3. Note this line at the top of the report:

Code Listing 3: Baseline evolution report

-----------------------------------------------------
Evolve SQL Plan Baseline Report
-----------------------------------------------------
Inputs:
----
  PLAN_LIST   = SYS_SQL_PLAN_27a47aa15003759b
  TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
  VERIFY        = YES
  COMMIT       = YES
Plan: SYS_SQL_PLAN_27a47aa15003759b
----------------------
  Plan was verified: Time used 41.06 seconds.
  Failed performance criterion: Compound improvement ratio < .36
                       Baseline Plan       Test Plan   Improv. Ratio
                       --------------      ---------   -------------
  Execution Status:          COMPLETE       COMPLETE
  Rows Processed:                   0              0
  Elapsed Time(ms):              5036           1033         4.88
  CPU Time(ms):                   254            700          .36
  Buffer Gets:                   1728          43945          .04
  Disk Reads:                     254             22        11.55
  Direct Writes:                    0              0
  Fetches:                         49             22         2.23
  Executions:                       1              1
--------------------------------------------------------------------
Report Summary
--------------------------------------------------------------------
Number of SQL plan baselines verified: 1.
Number of SQL plan baselines evolved: 0.

Failed performance criterion:
Compound improvement ratio < .36. 

The line clearly shows that the newly considered plan performed worse than the original plan so it was rejected as a replacement for the optimizer‘s best plan choice. Had the comparison ratio yielded an improvement factor greater than 1, SQL plan management would have accepted that plan as a candidate for the optimizer to consider.

What if you feel that the decision made by the evolve function is not accurate and you would rather force the optimizer to use a specific plan? You can do that by making the plan fixed in the baseline. You can make a plan fixed by executing the alter_sql_plan_baseline function in the dbms_spm package, as shown in Listing 4.

Code Listing 4: Fixing a plan baseline

declare
   l_plans pls_integer;
begin
   l_plans := dbms_spm.alter_sql_plan_baseline (
      sql_handle         => 'SYS_SQL_f6b17b4c27a47aa1',
      plan_name         => 'SYS_SQL_PLAN_27a47aa15003759b',
      attribute_name   => 'fixed',
      attribute_value  => 'YES'
   );
end;
-- Now examine the plan:
SQL> explain plan for select * /* ARUP */ from sales
  2  where quantity_sold > 1 order by cust_id;
Explained.
SQL> select * from table(dbms_xplan.display(null, null, 'basic'));
Plan hash value: 143117509
--------------------------------------------------------------
| Id  | Operation                           | Name            |
--------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                 |
|   1 |  SORT ORDER BY                      |                 |
|   2 |   PARTITION RANGE ALL               |                 |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID| SALES           |
|   4 |     BITMAP CONVERSION TO ROWIDS     |                 |
|   5 |      BITMAP INDEX FULL SCAN         | SALES_PROMO_BIX |
--------------------------------------------------------------

From the output, you can see that the new plan used the SALES_PROMO_BIX index instead of the SALES_TIME_BIX index used in the previous plan (and shown in Listing 2). Now the new plan will be fixed.

Where can you use fixed plans? Suppose the plan for a SQL statement is not optimal, such as a plan that is using the SALES_PROMO_BIX index whereas a plan using the SALES_TIME_BIX index would be more efficient, but you can‘t change the code to place hints. In that case, you can follow these steps:

1. In a different session, change the optimizer_mode parameter to the value that produces the desired plan, as shown in Listing 1.

2. Execute the SQL statement, capture the baseline as in Listing 1, and disconnect the session.

3. Mark the plan, using the SALES_TIME_BIX index as fixed, as shown in Listing 4. Remember to replace the SQL handle and the plan name with what they are in your case.

After the plan is marked as fixed, the SQL statement will use that plan only, not the one generated by the optimizer. If there is more than one fixed plan, the optimizer will choose the best among them.

You can also use the same technique for ensuring stable execution paths for SQL statements during database upgrades. First you collect baselines for all SQL statements in the database by setting the system parameter optimizer_capture_sql_plan_baselines to true and mark only one plan as fixed for each of the critical SQL statements. Then, gradually, you should “unfix”the plans and use the Evolve function to check if there are any other optimal plans. If a plan generated later by the optimizer is worse, you can always revert to the previous fixed plan.

Stored outlines also make a plan stable, but they make it rigid. The optimizer identifies that there is an outline for a SQL statement and stops generating new plans. Baselines, on the other hand, never stop the optimizer from generating a new plan.

The SQL plan management feature enables you to store validated or well-known plans for SQL statements in the form of baselines, which can be very useful in diagnosing sudden performance degradations. Because the baselines (and the corresponding plans) are stored in a repository, you can also compare them and decide to make the most efficient use of them.

LEARN more about SQL plan management
“SQL Plan Management”
“Oracle by Example on SQL Plan Management”