Creating SQL plan
baselines from STS
If you are upgrading from
10gR2 or have an 11g test system, you might already have an STS containing some
or all of your SQL statements. This STS might contain plans that perform
satisfactorily. Let's call this STS MY_STS. You can create a SQL plan baseline
from this STS as follows:
SQL> variable pls
number;
SQL> exec :pls :=
dbms_spm.load_plans_from_sqlset(sqlset_name => 'MY_STS', -
> basic_filter => 'sql_text
like ''select%p.prod_name%''');
This will create SQL plan
baselines for all statements that match the specified filter.
Creating SQL plan
baselines from cursor cache
You can automatically
create SQL plan baselines for any cursor that is currently in the cache as
follows:
SQL> exec :pls :=
dbms_spm.load_plans_from_cursor_cache( -
> attribute_name =>
'SQL_TEXT', -
> attribute_value =>
'select%p.prod_name%');
This will create SQL plan
baselines for all statements whose text matches the specified string. Several
overloaded variations of this function allow you to filter on other cursor
attributes.
Creating SQL plan
baselines using a staging table
If you already have SQL
plan baselines (say on an 11g test system), you can export them to another
system (a production system for instance). First, on the test system, create a
staging table and pack the SQL plan baselines you want to export:
SQL> exec
dbms_spm.create_stgtab_baseline(table_name => 'MY_STGTAB', -
> table_owner => 'SH');
PL/SQL procedure
successfully completed.
SQL> exec :pls :=
dbms_spm.pack_stgtab_baseline( -
> table_name =>
'MY_STGTAB', -
> table_owner => 'SH', -
> sql_text =>
'select%p.prod_name%');
This will pack all SQL
plan baselines for statements that match the specified filter. The staging
table, MY_STGTAB, is a regular table that you should export to the production
system using Datapump Export.
On the production system,
you can now unpack the staging table to create the SQL plan baselines:
SQL> exec :pls :=
dbms_spm.unpack_stgtab_baseline( -
> table_name =>
'MY_STGTAB', -
> table_owner => 'SH', -
> sql_text =>
'select%p.prod_name%');
This will unpack the
staging table and create SQL plan baselines. Note that the filter for unpacking
the staging table is optional and may be different than the one used during
packing. This means that you can pack several SQL plan baselines into a staging
table and selectively unpack only a subset of them on the target system.
Creating SQL plan
baselines automatically
You can create SQL plan
baselines for all repeatable statements automatically by setting the parameter
optimizer_capture_sql_plan_baselines to TRUE (default is FALSE). The first plan
captured for any statement is automatically accepted and becomes part of the
SQL plan baseline, so enable this parameter only when you are sure that the
default plans are performing well.
You can use the automatic
plan capture mode when you have upgraded from a previous database version. Set
optimizer_features_enable to the earlier version and execute your workload.
Every repeatable statement will have its plan captured thus creating SQL plan
baselines. You can reset optimizer_features_enable to its default value after
you are sure that all statements in your workload have had a chance to execute.
Note that this automatic
plan capture occurs only for repeatable statements, that is, statements that
are executed at least twice. Statements that are only executed once will not
benefit from SQL plan baselines since accepted plans are only used in
subsequent hard parses.
The following example
shows a plan being captured automatically when the same statement is executed
twice:
SQL> alter session set
optimizer_capture_sql_plan_baselines = true;
Session altered.
SQL> var pid number
SQL> exec :pid := 100;
PL/SQL procedure
successfully completed.
SQL> select
p.prod_name, s.amount_sold, t.calendar_year
2 from sales s, products p, times t
3 where s.prod_id = p.prod_id
4 and s.time_id = t.time_id
5 and p.prod_id < :pid;
PROD_NAME AMOUNT_SOLD
CALENDAR_YEAR
--------- -----------
-------------
...
9 rows selected.
SQL> select
p.prod_name, s.amount_sold, t.calendar_year
2 from sales s, products p, times t
3 where s.prod_id = p.prod_id
4 and s.time_id = t.time_id
5 and p.prod_id < :pid;
PROD_NAME AMOUNT_SOLD
CALENDAR_YEAR
--------- -----------
-------------
...
9 rows selected.
SQL> alter session set
optimizer_capture_sql_plan_baselines = false;
Session altered
Automatic plan capture
will not occur for a statement if a stored outline exists for it and is enabled
and the parameter use_stored_outlines is TRUE. In this case, turn on
incremental capture of plans into an STS using the function
capture_cursor_cache_sqlset() in the DBMS_SQLTUNE package. After you have
collected the plans for your workload into the STS, manually create SQL plan
baselines using the method described earlier. Then, disable the stored outlines
or set use_stored_outlines to FALSE. From now on, SPM will manage your workload
and stored outlines will not be used for those statements. In this article, we
have seen how to create SQL plan baselines. In the next, we will describe the
SPM aware optimizer and how it uses SQL plan baselines.
In Part 1, we saw how you
can create SQL plan baselines. After you create a SQL plan baseline for a
statement, subsequent executions of that statement will use the SQL plan
baseline. From all the plans in the SQL plan baseline, the optimizer will
select the one with the best cost in the current environment (including bind
values, current statistics, parameters, etc.). The optimizer will also generate
the best-cost plan that it would otherwise have used without a SQL plan
baseline. However, this best-cost plan will not be used but instead added to
the statement's plan history for later verification. In other words, the
optimizer will use a known plan from the SQL plan baseline instead of a new and
hitherto unknown plan. This guarantees no performance regression.
Let's see this plan
selection process in action. First, we create a SQL plan baseline by enabling
automatic plan capture and executing the query twice:
SQL> alter session set
optimizer_capture_sql_plan_baselines = true;
Session altered.
SQL> var pid number
SQL> exec :pid := 100;
PL/SQL procedure
successfully completed.
SQL> select
p.prod_name, s.amount_sold, t.calendar_year
2 from sales s, products
p, times t
3 where s.prod_id =
p.prod_id
4 and s.time_id =
t.time_id
5 and p.prod_id < :pid;
PROD_NAME AMOUNT_SOLD
CALENDAR_YEAR
--------- -----------
-------------
...
9 rows selected.
SQL> select * from
table(dbms_xplan.display_cursor('bfbr3zrg9d5cc', 0, 'basic note'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select p.prod_name,
s.amount_sold, t.calendar_year from sales s,
products p, times t where
s.prod_id = p.prod_id and s.time_id =
t.time_id and p.prod_id
< :pid
Plan hash value:
2787970893
----------------------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 |
NESTED LOOPS
| |
| 2 |
NESTED LOOPS
| |
| 3 |
HASH JOIN
| |
| 4 |
TABLE ACCESS BY INDEX ROWID
| PRODUCTS |
| 5 |
INDEX RANGE SCAN |
PRODUCTS_PK |
| 6 |
PARTITION RANGE ALL
| |
| 7 |
TABLE ACCESS BY LOCAL INDEX ROWID| SALES |
| 8 |
BITMAP CONVERSION TO ROWIDS
| |
| 9 |
BITMAP INDEX RANGE SCAN |
SALES_PROD_BIX |
| 10 |
INDEX UNIQUE SCAN
| TIME_PK |
| 11 |
TABLE ACCESS BY INDEX ROWID
| TIMES |
----------------------------------------------------------------
25 rows selected.
SQL> select
p.prod_name, s.amount_sold, t.calendar_year
2 from sales s, products p, times t
3 where s.prod_id = p.prod_id
4 and s.time_id = t.time_id
5 and p.prod_id < :pid;
PROD_NAME AMOUNT_SOLD
CALENDAR_YEAR
--------- -----------
-------------
...
9 rows selected.
SQL> alter session set
optimizer_capture_sql_plan_baselines = false;
Session altered.
SQL> select sql_text,
plan_name, enabled, accepted from dba_sql_plan_baselines;
SQL_TEXT PLAN_NAME ENA ACC
----------------------------------------
------------------------------ --- ---
select p.prod_name,
s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b0a62d0f4d YES YES
endar_year
from sales s, products p,
times t
where s.prod_id =
p.prod_id
and s.time_id = t.time_id
and p.prod_id < :pid
We can see that a SQL plan
baseline was created for the statement. Suppose the statement is hard parsed
again (we do it here by flushing the shared pool). Let's turn off SQL plan
management and execute the query with a different bind value:
SQL> exec :pid :=
100000;
PL/SQL procedure
successfully completed.
SQL> alter system flush
shared_pool;
System altered.
SQL> alter session set
optimizer_use_sql_plan_baselines = false;
Session altered.
SQL> select
p.prod_name, s.amount_sold, t.calendar_year
2 from
sales s, products p, times t
3 where s.prod_id = p.prod_id
4 and s.time_id = t.time_id
5 and p.prod_id < :pid;
PROD_NAME AMOUNT_SOLD
CALENDAR_YEAR
--------- -----------
-------------
...
960 rows selected.
SQL> select * from
table(dbms_xplan.display_cursor('bfbr3zrg9d5cc', 0, 'basic note'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select p.prod_name, s.amount_sold,
t.calendar_year from sales s,
products p, times t where
s.prod_id = p.prod_id and s.time_id = t.time_id and
p.prod_id < :pid
Plan hash value:
2361178149
------------------------------------------
| Id | Operation | Name |
------------------------------------------
| 0 | SELECT STATEMENT |
|
| 1 |
HASH JOIN | |
| 2 |
HASH JOIN | |
| 3 |
PARTITION RANGE ALL| |
| 4 |
TABLE ACCESS FULL | SALES |
| 5 |
TABLE ACCESS FULL | TIMES |
| 6 |
TABLE ACCESS FULL | PRODUCTS |
------------------------------------------
20 rows selected.
We can see that the
optimizer selected a different plan because the new bind value makes the
predicate less selective. Let's turn SQL plan management back on and re-execute
the query with the same bind value:
SQL> alter session set
optimizer_use_sql_plan_baselines = true;
Session altered.
SQL> select
p.prod_name, s.amount_sold, t.calendar_year
2 from sales s, products p, times t
3 where s.prod_id = p.prod_id
4 and s.time_id = t.time_id
5 and p.prod_id < :pid;
PROD_NAME AMOUNT_SOLD
CALENDAR_YEAR
--------- -----------
-------------
...
960 rows selected.
SQL> select * from
table(dbms_xplan.display_cursor('bfbr3zrg9d5cc', 0, 'basic note'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select p.prod_name,
s.amount_sold, t.calendar_year from sales s,
products p, times t where
s.prod_id = p.prod_id and s.time_id =
t.time_id and p.prod_id
< :pid
Plan hash value:
2787970893
----------------------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 |
NESTED LOOPS
| |
| 2 |
NESTED LOOPS
| |
| 3 |
HASH JOIN
| |
| 4 |
TABLE ACCESS BY INDEX ROWID
| PRODUCTS |
| 5 |
INDEX RANGE SCAN |
PRODUCTS_PK |
| 6 |
PARTITION RANGE ALL
| |
| 7 |
TABLE ACCESS BY LOCAL INDEX ROWID| SALES |
| 8 |
BITMAP CONVERSION TO ROWIDS
| |
| 9 |
BITMAP INDEX RANGE SCAN |
SALES_PROD_BIX |
| 10 |
INDEX UNIQUE SCAN
| TIME_PK |
| 11 |
TABLE ACCESS BY INDEX ROWID
| TIMES |
----------------------------------------------------------------
Note
-----
- SQL plan baseline
SYS_SQL_PLAN_fcc170b0a62d0f4d used for this statement
29 rows selected.
The note at the bottom
tells you that the optimizer is using the SQL plan baseline. In other words, we
can see that the optimizer used an accepted plan in the SQL plan baseline in
favor of a new plan. In fact, we can also check that the optimizer inserted the
new plan into the statement's plan history:
SQL> select sql_text,
plan_name, enabled, accepted
from dba_sql_plan_baselines;
SQL_TEXT PLAN_NAME ENA ACC
----------------------------------------
------------------------------ --- ---
select p.prod_name,
s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b08cbcb825 YES NO
endar_year
from sales s, products p,
times t
where s.prod_id =
p.prod_id
and s.time_id = t.time_id
and p.prod_id < :pid
select p.prod_name,
s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b0a62d0f4d YES YES
endar_year
from sales s, products p,
times t
where s.prod_id =
p.prod_id
and s.time_id = t.time_id
and p.prod_id < :pid
The 'NO' value for the
accepted column implies that the new plan is in the plan history but is not
available for use until it is verified to be a good plan. The optimizer will
continue to use an accepted plan until new plans are verified and added to the SQL
plan baseline. If there is more than one plan in the SQL plan baseline, the
optimizer will use the one with the best cost under the then-current conditions
(statistics, bind values, parameter settings and so on).
When you create a SQL plan
baseline for a SQL statement, the SPM aware optimizer thus guarantees that no
new plans will be used other than the ones in the SQL plan baseline. This
prevents unexpected plan changes that sometimes lead to performance
regressions.
Preventing new plans from
being used is fine, but what if the new plans are in fact better? In Part 3, we
will describe how new and improved plans are added to a SQL plan baseline
In the example in Part 2,
we saw that the optimizer used an accepted plan instead of a brand new plan.
The statement has two plans in its plan history, but only one is accepted and
thus in the SQL plan baseline:
SQL> select sql_text,
plan_name, enabled, accepted from dba_sql_plan_baselines;
SQL_TEXT PLAN_NAME ENA ACC
----------------------------------------
------------------------------ --- ---
select p.prod_name,
s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b08cbcb825 YES NO
endar_year
from sales s, products p,
times t
where s.prod_id =
p.prod_id
and s.time_id = t.time_id
and p.prod_id < :pid
select p.prod_name,
s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b0a62d0f4d YES YES
endar_year
from sales s, products p,
times t
where s.prod_id =
p.prod_id
and s.time_id = t.time_id
and p.prod_id < :pid
Non-accepted plans can be
verified by executing the evolve_sql_plan_baseline function. This function will
execute the non-accepted plan and compare its performance to the best accepted
plan. The execution is performed using the conditions (e.g., bind values,
parameters, etc.) in effect at the time the non-accepted plan was added to the
plan history. If the non-accepted plan's performance is better, the function
will make it accepted, thus adding it to the SQL plan baseline. Let's see what
happens when we execute this function:
SQL> var report clob;
SQL> exec :report :=
dbms_spm.evolve_sql_plan_baseline();
PL/SQL procedure
successfully completed.
SQL> print :report
REPORT
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Evolve SQL Plan
Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE =
PLAN_NAME
=
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY
= YES
COMMIT
= YES
Plan:
SYS_SQL_PLAN_fcc170b08cbcb825
-----------------------------------
Plan was verified: Time used .1 seconds.
Passed performance criterion: Compound
improvement ratio >= 10.13
Plan was changed to an accepted plan.
Baseline Plan Test Plan Improv. Ratio
------------- --------- -------------
Execution Status: COMPLETE COMPLETE
Rows Processed: 960 960
Elapsed Time(ms): 19 15 1.27
CPU Time(ms): 18 15 1.2
Buffer Gets: 1188 116 10.24
Disk Reads: 0 0
Direct Writes: 0 0
Fetches: 0 0
Executions: 1 1
-------------------------------------------------------------------------------
Report Summary
-------------------------------------------------------------------------------
Number of SQL plan
baselines verified: 1.
The plan verification
report shows that the new plan's performance was better and so it was made
accepted and became part of the SQL plan baseline. We can confirm it by looking
in the dba_sql_plan_baselines view:
SQL> select sql_text,
plan_name, enabled, accepted from dba_sql_plan_baselines;
SQL_TEXT PLAN_NAME ENA ACC
----------------------------------------
------------------------------ --- ---
select p.prod_name,
s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b08cbcb825 YES YES
endar_year
from sales s, products p,
times t
where s.prod_id =
p.prod_id
and s.time_id = t.time_id
and p.prod_id < :pid
select p.prod_name,
s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b0a62d0f4d YES YES
endar_year
from sales s, products p,
times t
where s.prod_id =
p.prod_id
and s.time_id = t.time_id
and p.prod_id < :pid
The SQL plan baseline now
has two accepted plans: SYS_SQL_PLAN_fcc170b08cbcb825 is now accepted. You can
either execute the evolve_sql_plan_baseline() function manually or schedule it
to run automatically in a maintenance window. Another way of evolving a SQL
plan baseline is to use the SQL Tuning Advisor. Instead of executing
evolve_sql_plan_baseline, suppose we start from the original state where we
have one accepted and one non-accepted plan:
SQL> select sql_text,
plan_name, enabled, accepted from dba_sql_plan_baselines;
SQL_TEXT PLAN_NAME ENA ACC
----------------------------------------
------------------------------ --- ---
select p.prod_name,
s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b08cbcb825 YES NO
endar_year
from sales s, products p,
times t
where s.prod_id =
p.prod_id
and s.time_id = t.time_id
and p.prod_id < :pid
select p.prod_name,
s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b0a62d0f4d YES YES
endar_year
from sales s, products p,
times t
where s.prod_id =
p.prod_id
and s.time_id = t.time_id
and p.prod_id < :pid
You can execute the SQL
Tuning Advisor on the cursor in the cursor cache:
SQL> var tname
varchar2(30);
SQL> exec :tname :=
dbms_sqltune.create_tuning_task(sql_id => 'bfbr3zrg9d5cc');
PL/SQL procedure
successfully completed.
SQL> exec
dbms_sqltune.execute_tuning_task(task_name => :tname);
PL/SQL procedure
successfully completed.
SQL> select
dbms_sqltune.report_tuning_task(:tname, 'TEXT', 'BASIC') FROM dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK(:TNAME,'TEXT','BASIC')
-------------------------------------------------------------------------------
GENERAL INFORMATION
SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_505
Tuning Task Owner : SH
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 11/11/2008 16:43:12
Completed at : 11/11/2008 16:43:13
-------------------------------------------------------------------------------
Schema Name: SH
SQL ID : bfbr3zrg9d5cc
SQL Text : select p.prod_name, s.amount_sold,
t.calendar_year
from sales s, products p, times t
where s.prod_id = p.prod_id
and s.time_id = t.time_id
and p.prod_id < :pid
-------------------------------------------------------------------------------
FINDINGS SECTION (1
finding)
-------------------------------------------------------------------------------
1- A potentially better
execution plan was found for this statement.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted
Cost
------------------------------
Plan hash value:
2787970893
----------------------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 |
NESTED LOOPS
| |
| 2 |
NESTED LOOPS
| |
| 3 |
HASH JOIN
| |
| 4 |
TABLE ACCESS BY INDEX ROWID
| PRODUCTS |
| 5 |
INDEX RANGE SCAN |
PRODUCTS_PK |
| 6 |
PARTITION RANGE ALL
| |
| 7 |
TABLE ACCESS BY LOCAL INDEX ROWID| SALES |
| 8 |
BITMAP CONVERSION TO ROWIDS
| |
| 9 |
BITMAP INDEX RANGE SCAN |
SALES_PROD_BIX |
| 10 |
INDEX UNIQUE SCAN
| TIME_PK |
| 11 |
TABLE ACCESS BY INDEX ROWID
| TIMES |
----------------------------------------------------------------
2- Original With Adjusted
Cost
------------------------------
Plan hash value:
2787970893
----------------------------------------------------------------
| Id | Operation
|
Name |
----------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 |
NESTED LOOPS
| |
| 2 |
NESTED LOOPS
| |
| 3 |
HASH JOIN
| |
| 4 |
TABLE ACCESS BY INDEX ROWID
| PRODUCTS |
| 5 |
INDEX RANGE SCAN |
PRODUCTS_PK |
| 6 |
PARTITION RANGE ALL
| |
| 7 |
TABLE ACCESS BY LOCAL INDEX ROWID| SALES |
| 8 |
BITMAP CONVERSION TO ROWIDS
| |
| 9 |
BITMAP INDEX RANGE SCAN |
SALES_PROD_BIX |
| 10 |
INDEX UNIQUE SCAN
| TIME_PK |
| 11 |
TABLE ACCESS BY INDEX ROWID
| TIMES |
----------------------------------------------------------------
3- Using SQL Profile
--------------------
Plan hash value: 2361178149
------------------------------------------
| Id | Operation | Name |
------------------------------------------
| 0 | SELECT STATEMENT |
|
| 1 |
HASH JOIN | |
| 2 |
HASH JOIN |
|
| 3 |
PARTITION RANGE ALL| |
| 4 |
TABLE ACCESS FULL | SALES |
| 5 |
TABLE ACCESS FULL | TIMES |
| 6 |
TABLE ACCESS FULL | PRODUCTS |
------------------------------------------
-------------------------------------------------------------------------------
SQL> exec
dbms_sqltune.accept_sql_profile(task_name => :tname);
PL/SQL procedure
successfully completed.
SQL> select sql_text,
plan_name, enabled, accepted from dba_sql_plan_baselines;
SQL_TEXT PLAN_NAME ENA ACC
----------------------------------------
------------------------------ --- ---
select p.prod_name,
s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b08cbcb825 YES YES
endar_year
from sales s, products p,
times t
where s.prod_id =
p.prod_id
and s.time_id = t.time_id
and p.prod_id < :pid
select p.prod_name,
s.amount_sold, t.cal SYS_SQL_PLAN_fcc170b0a62d0f4d YES YES
endar_year
from sales s, products p,
times t
where s.prod_id = p.prod_id
and s.time_id = t.time_id
and p.prod_id < :pid
SQL> select sql_text,
type, status from dba_sql_profiles;
SQL_TEXT TYPE STATUS
----------------------------------------
------- --------
select p.prod_name, s.amount_sold,
t.cal MANUAL ENABLED
endar_year
from sales s, products p,
times t
where s.prod_id =
p.prod_id
and s.time_id = t.time_id
and p.prod_id < :pid
What we see here is that
SQL Tuning Advisor found a tuned plan (that coincidentally happened to be the
non-accepted plan in our plan history). When we accepted the recommended SQL
profile, the SQL Tuning Advisor created a SQL profile and also changed the non-accepted
plan to accepted status, thus evolving the SQL plan baseline to two plans.
Note that the SQL Tuning
Advisor may also find a completely new tuned plan, one that is not in the plan
history. If you then accept the recommended SQL profile, the SQL Tuning Advisor
will create a SQL profile and also add the tuned plan to the SQL plan baseline.
Thus, you can evolve a SQL
plan baseline either by executing the evolve_sql_plan_baseline function or by
using the SQL Tuning Advisor. New and provably better plans will be added by
either of these methods to the SQL plan baseline.
n the first three parts of
this article, we have seen how SQL plan baselines are created, used and
evolved. In this final installment, we will show some user interfaces, describe
the interaction of SPM with other features and answer some of your questions.
DBMS_SPM package
A new package, DBMS_SPM,
allows you to manage plan histories. We have already seen in previous examples
how you can use it to create and evolve SQL plan baselines. Other management
functions include changing attributes (like enabled status and plan name) of
plans or dropping plans. You need the ADMINISTER SQL MANAGEMENT OBJECT
privilege to execute this package.
Viewing the plan history
Regardless of how a plan
history is created, you can view details about the various plans in the view
DBA_SQL_PLAN_BASELINES. At the end of Part 3 of this blog, we saw that the SQL
statement had two accepted plans:
SQL> select sql_text,
sql_handle, plan_name, enabled, accepted
2 from
dba_sql_plan_baselines;
SQL_TEXT SQL_HANDLE PLAN_NAME ENA ACC
------------------------
------------------------ ----------------------------- --- ---
select p.prod_name, s.am
SYS_SQL_4bf04d85fcc170b0 SYS_SQL_PLAN_fcc170b08cbcb825 YES YES
ount_sold, t.calendar_ye
ar
from sales s, products p
, times t
where s.prod_id = p.prod
_id
and s.time_id = t.time
_id
and p.prod_id < :pid
select p.prod_name, s.am
SYS_SQL_4bf04d85fcc170b0 SYS_SQL_PLAN_fcc170b0a62d0f4d YES YES
ount_sold, t.calendar_ye
ar
from sales s, products p
, times t
where s.prod_id = p.prod
_id
and s.time_id = t.time
_id
and p.prod_id < :pid
The SQL handle is a unique
identifier for each SQL statement that you can use when managing your plan
history using the DBMS_SPM package.
Creating an accepted plan
by modifying the SQL text
Some of you may be
manually tuning SQL statements by adding hints or otherwise modifying the SQL
text. If you enable automatic capture of SQL plans and then execute this
statement, you will be creating a SQL plan baseline for this modified
statement. What you most likely want, however, is to add this plan to the plan
history of the original SQL statement. Here's how you can do this using the
above SQL statement as an example.
Let's modify the SQL
statement, execute it and look at the plan:
SQL> var pid number
SQL> exec :pid := 100;
PL/SQL procedure
successfully completed.
SQL> select /*+
leading(t) */ p.prod_name, s.amount_sold, t.calendar_year
2 from sales s, products p, times t
3 where s.prod_id = p.prod_id
4 and s.time_id = t.time_id
5 and p.prod_id < :pid;
PROD_NAME AMOUNT_SOLD
CALENDAR_YEAR
--------- -----------
-------------
...
9 rows selected.
SQL> select * from
table(dbms_xplan.display_cursor('b17wnz4y8bqv1', 0, 'basic note'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /*+ leading(t) */
p.prod_name, s.amount_sold, t.calendar_year
from sales s, products p,
times t where s.prod_id = p.prod_id and
s.time_id = t.time_id and p.prod_id < :pid
Plan hash value:
2290436051
---------------------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 |
HASH JOIN
| |
| 2 |
HASH JOIN
| |
| 3 |
TABLE ACCESS FULL
| TIMES |
| 4 |
PARTITION RANGE ALL
| |
| 5 |
TABLE ACCESS BY LOCAL INDEX ROWID| SALES |
| 6 |
BITMAP CONVERSION TO ROWIDS
| |
| 7 |
BITMAP INDEX RANGE SCAN |
SALES_PROD_BIX |
| 8 |
TABLE ACCESS BY INDEX ROWID
| PRODUCTS |
| 9 |
INDEX RANGE SCAN
| PRODUCTS_PK |
---------------------------------------------------------------
23 rows selected.
We can now create a new accepted plan for the
original SQL statement by associating the modified statement's plan to the
original statement's sql handle (obtained from DBA_SQL_PLAN_BASELINES):
SQL> var pls number
SQL> exec :pls :=
dbms_spm.load_plans_from_cursor_cache( -
> sql_id =>
'b17wnz4y8bqv1', -
> plan_hash_value =>
2290436051, -
> sql_handle =>
'SYS_SQL_4bf04d85fcc170b0');
If the original SQL
statement does not already have a plan history (and thus no SQL handle),
another version of load_plans_from_cursor_cache allows you to specify the
original statement's text.
To confirm that we now
have three accepted plans for our SQL statement, let's check in
DBA_SQL_PLAN_BASELINES:
SQL> select sql_text,
sql_handle, plan_name, enabled, accepted
2 from
dba_sql_plan_baselines;
SQL_TEXT SQL_HANDLE PLAN_NAME ENA ACC
------------------------
------------------------ ----------------------------- --- ---
select p.prod_name, s.am
SYS_SQL_4bf04d85fcc170b0 SYS_SQL_PLAN_fcc170b0888547d3 YES YES
ount_sold, t.calendar_ye
ar
from sales s, products p
, times t
where s.prod_id = p.prod
_id
and s.time_id = t.time
_id
and p.prod_id < :pid
select p.prod_name, s.am
SYS_SQL_4bf04d85fcc170b0 SYS_SQL_PLAN_fcc170b08cbcb825 YES YES
ount_sold, t.calendar_ye
ar
from sales s, products p
, times t
where s.prod_id = p.prod
_id
and s.time_id = t.time
_id
and p.prod_id < :pid
select p.prod_name, s.am
SYS_SQL_4bf04d85fcc170b0 SYS_SQL_PLAN_fcc170b0a62d0f4d YES YES
ount_sold, t.calendar_ye
ar
from sales s, products p
, times t
where s.prod_id = p.prod
_id
and s.time_id = t.time
_id
and p.prod_id < :pid
Displaying plans
When the optimizer uses an
accepted plan for a SQL statement, you can see it in the plan table (for
explain) or V$SQL_PLAN (for shared cursors). Let's explain the SQL statement
above and display its plan:
SQL> explain plan for
2
select p.prod_name, s.amount_sold, t.calendar_year
3 from
sales s, products p, times t
4
where s.prod_id = p.prod_id
5
and s.time_id = t.time_id
6
and p.prod_id < :pid;
Explained.
SQL> select * from
table(dbms_xplan.display('plan_table', null, 'basic note'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value:
2787970893
----------------------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 |
NESTED LOOPS
| |
| 2 |
NESTED LOOPS
| |
| 3 |
HASH JOIN
| |
| 4 |
TABLE ACCESS BY INDEX ROWID
| PRODUCTS |
| 5 |
INDEX RANGE SCAN |
PRODUCTS_PK |
| 6 |
PARTITION RANGE ALL
| |
| 7 |
TABLE ACCESS BY LOCAL INDEX ROWID| SALES |
| 8 |
BITMAP CONVERSION TO ROWIDS
| |
| 9 |
BITMAP INDEX RANGE SCAN |
SALES_PROD_BIX |
| 10 |
INDEX UNIQUE SCAN
| TIME_PK |
| 11 |
TABLE ACCESS BY INDEX ROWID
| TIMES |
----------------------------------------------------------------
Note
-----
- SQL plan baseline
"SYS_SQL_PLAN_fcc170b0a62d0f4d" used for this statement
22 rows selected.
The note at the bottom
tells you that the optimizer used an accepted plan.
A plan history might have
multiple plans. You can see one of the accepted plans if the optimizer selects
it for execution. But what if you want to display some or all of the other
plans? You can do this using the display_sql_plan_baseline function in the
DBMS_XPLAN package. Using the above example, here's how you can display the
plan for all plans in the plan history.
SQL> select *
2 from
table(dbms_xplan.display_sql_plan_baseline(
3 sql_handle =>
'SYS_SQL_4bf04d85fcc170b0', format => 'basic'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle:
SYS_SQL_4bf04d85fcc170b0
SQL text: select
p.prod_name, s.amount_sold, t.calendar_year from sales s,
products p, times t where s.prod_id =
p.prod_id and s.time_id =
t.time_id and p.prod_id < :pid
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name:
SYS_SQL_PLAN_fcc170b0888547d3
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
Plan hash value:
2290436051
---------------------------------------------------------------
| Id | Operation
|
Name |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 |
HASH JOIN
| |
| 2 |
HASH JOIN | |
| 3 |
TABLE ACCESS FULL
| TIMES |
| 4 |
PARTITION RANGE ALL
| |
| 5 |
TABLE ACCESS BY LOCAL INDEX ROWID| SALES |
| 6 |
BITMAP CONVERSION TO ROWIDS
| |
| 7 |
BITMAP INDEX RANGE SCAN |
SALES_PROD_BIX |
| 8 |
TABLE ACCESS BY INDEX ROWID
| PRODUCTS |
| 9 |
INDEX RANGE SCAN
| PRODUCTS_PK |
---------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name:
SYS_SQL_PLAN_fcc170b08cbcb825
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value:
2361178149
------------------------------------------
| Id | Operation | Name |
------------------------------------------
| 0 | SELECT STATEMENT |
|
| 1 |
HASH JOIN | |
| 2 |
HASH JOIN | |
| 3 |
PARTITION RANGE ALL| |
| 4 |
TABLE ACCESS FULL | SALES |
| 5 |
TABLE ACCESS FULL | TIMES |
| 6 |
TABLE ACCESS FULL | PRODUCTS |
------------------------------------------
--------------------------------------------------------------------------------
Plan name:
SYS_SQL_PLAN_fcc170b0a62d0f4d
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value:
2787970893
----------------------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 |
NESTED LOOPS
| |
| 2 |
NESTED LOOPS
| |
| 3 |
HASH JOIN
| |
| 4 |
TABLE ACCESS BY INDEX ROWID
| PRODUCTS |
| 5 |
INDEX RANGE SCAN |
PRODUCTS_PK |
| 6 |
PARTITION RANGE ALL
| |
| 7 |
TABLE ACCESS BY LOCAL INDEX ROWID| SALES |
| 8 |
BITMAP CONVERSION TO ROWIDS
| |
| 9 |
BITMAP INDEX RANGE SCAN |
SALES_PROD_BIX |
| 10 |
INDEX UNIQUE SCAN
| TIME_PK |
| 11 |
TABLE ACCESS BY INDEX ROWID
| TIMES |
----------------------------------------------------------------
72 rows selected.
Parameters
Two parameters allow you
to control SPM. The first, optimizer_capture_sql_plan_baselines, which is FALSE
by default, allows you to automatically capture plans. SPM will start managing
every repeatable SQL statement that is executed and will create a plan history
for it. The first plan that is captured will beautomatically accepted.
Subsequent plans for these statements will not be accepted until they are
evolved.
The second parameter,
optimizer_use_sql_plan_baselines, is TRUE by default. It allows the SPM aware
optimizer to use the SQL plan baseline if available when compiling a SQL
statement. If you set this parameter to FALSE, the SPM aware optimizer will be
disabled and you will get the regular cost-based optimizer which will select
the best plan based on estimated cost.
SPM and SQL profiles
A SQL statement can have
both a SQL profile and a SQL plan baseline. Such a case was described in Part 3
where we evolved a SQL plan baseline by accepting a SQL profile. In this case,
the SPM aware optimizer will use both the SQL profile and the SQL plan
baseline. The SQL profile contains additional information that helps the
optimizer to accurately cost each accepted plan and select the best one. The
SPM aware optimizer may choose a different accepted plan when a SQL profile is
present than when it is not.
SPM and stored outlines
It is possible for a SQL
statement to have a stored outline as well as a SQL plan baseline. If a stored
outline exists for a SQL statement and is enabled for use, then the optimizer
will use it, ignoring the SQL plan baseline. In other words, the stored outline
trumps a SQL plan baseline. If you are using stored outlines, you can test SPM
by creating SQL plan baselines and disabling the stored outlines. If you are
satisfied with SPM, you can either drop the stored outlines or leave them
disabled. If SPM doesn't work for you (and we would love to know why), you can
re-enable the stored outlines.
If you are using stored
outlines, be aware of their limitations:
You can only have one
stored outline at a time for a given SQL statement. This may be fine in some
cases, but a single plan is not necessarily the best when the statement is
executed under varying conditions (e.g., bind values).
The second limitation is
related to the first. Stored outlines do not allow for evolution. That is, even
if a better plan exists, the stored outline will continue to be used,
potentially degrading your system's performance. To get the better plan, you
have to manually drop the current stored outline and generate a new one.
If an access path (e.g.,
an index) used in a stored outline is dropped or otherwise becomes unusable,
the partial stored outline will continue to be used with the potential of a
much worse plan.
One question that readers
have is what we plan to do with the stored outlines feature. Here is the
official word in Chapter 20 of Oracle's Performance Tuning Guide:
Stored outlines will be
desupported in a future release in favor of SQL plan management. In Oracle
Database 11g Release 1 (11.1), stored outlines continue to function as in past
releases. However, Oracle strongly recommends that you use SQL plan management
for new applications. SQL plan management creates SQL plan baselines, which
offer superior SQL performance and stability compared with stored outlines.
If you have existing
stored outlines, consider migrating them to SQL plan baselines by using the
LOAD_PLANS_FROM_CURSOR_CACHE or LOAD_PLANS_FROM_SQLSET procedure of the
DBMS_SPM package. When the migration is complete, you should disable or remove
the stored outlines.
SPM and adaptive cursor
sharing
Adaptive cursor sharing
(ACS) may generate multiple cursors for a given bind sensitive SQL statement if
it is determined that a single plan is not optimal under all conditions. Each
cursor is generated by forcing a hard parse of the statement. The optimizer
will normally select the plan with the best cost upon each hard parse.
When you have a SQL plan
baseline for a statement, the SPM aware optimizer will select the best accepted
plan as the optimal plan. This also applies for the hard parse of a bind
sensitive statement. There may be multiple accepted plans, each of which is
optimal for different bind sets. With SPM and ACS enabled, the SPM aware
optimizer will select the best plan for the current bind set.
Thus, if a hard parse
occurs, the normal SPM plan selection algorithm is used regardless of whether a
statement is bind sensitive.
No comments:
Post a Comment