SQL plan
management in Oracle Database 11g
Arup Nanda
Use SQL plan management in
Oracle Database 11g to optimize execution plans.
Have you ever found yourself in a
situation in which some database queries that used to behave
well suddenly started performing poorly? The cause for this is
usually traced 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.
This situation may be humbling , but
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.
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.
SQL Plan Management
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.
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.
Capture
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..
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.
Using Baselines
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 |
--------------------------------------
Administration and Evolution
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.
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. 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). 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.
Conclusion
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.
|
