作者：Maria Colgan
Master Product Manager
在本文的前三部分中，我们已经了解了SQL计划基线是如何创建、使用和演进的。在最后一篇中，我们将展示一些用户界面，描述SPM与其他特性的交互，并回答您的一些问题。
DBMS_SPM 包
一个新的包DBMS_SPM允许您管理执行计划历史。我们已经在前面的示例中，看到了如何使用它来创建和演进SQL执行计划基线。其他管理功能还包括更改执行计划的属性（如启用状态和计划名称）或删除执行计划。您需要“ADMINISTER SQL MANAGEMENT OBJECT”权限才能执行此包。无论执行计划历史记录是如何创建的，您都可以在视图DBA_SQL_PLAN_BASELINES中查看有关各种执行计划的详细信息。在本系列文章中的第3篇(点此查看)的末尾，我们看到SQL语句有两个可接受的执行计划：
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
在使用DBMS_SPM包管理执行计划历史时，SQL句柄是每个SQL语句的唯一标识符。通过修改SQL文本，有些人可能通过添加提示或修改SQL文本来手动调整SQL语句，并创建可接受的计划。如果启用SQL执行计划的自动捕获，然后执行此语句，则将为该修改后的语句创建SQL计划基线。但是，您最希望的是将此计划添加到原始SQL语句的计划历史中。下面使用上面的SQL语句作为示例，演示如何来实现这一点。让我们修改SQL语句，执行它并查看计划：
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 |
---------------------------------------------------------------
通过关联已修改语句的执行计划到原始语句的句柄（通过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');
如果原始SQL语句还没有执行计划历史（故没有SQL句柄），则可以使用另一个版本的load_plans_from_cursor_cache指定原始语句的文本。为了确认SQL语句现在有三个已接受的计划，让我们检查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
展示执行计划
当优化器对某SQL使用了一个已接受的执行计划，你会在PLAN TABLE（EXPLAIN方式）或V$SQL_PLAN（共享游标）中看到这一点。
让我们EXPLAIN上面的SQL语句并展示它的执行计划：
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
底部的NOTE告诉你优化器使用了一个已接受的执行计划。一个执行计划历史可能有多个执行计划。如果优化器选择执行一个已接受的计划，您可以看到它。但是如果你想展示部分或者所有其他的计划呢？您可以使用DBMS_XPLAN包中的display_sql_plan_baseline函数来执行此操作。使用上面的示例，下面是如何显示计划历史记录中所有计划的计划。
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 |
----------------------------------------------------------------
参数
有两个参数允许您控制SPM。第一个，optimizer_capture_sql_plan_baselines（默认为FALSE）允许您自动捕获计划。SPM将开始管理每个已重复执行的SQL语句，并为其创建执行计划历史。第一个被捕获的执行计划，将自动成为已接受的执行计划。这些语句随后产生的执行计划在被演进前，是不会被接受的。在这些声明的后续计划得到完善之前，不会被接受。第二个参数 optimizer_use_sql_plan_baselines，其默认值为TRUE。它允许SPM感知优化器在编译SQL语句时使用SQL执行计划基线（如果可用）。如果将此参数设置为FALSE，则SPM感知优化器将被禁用，您将使用常规的基于成本的优化器，该优化器将根据评估的成本来选择最佳计划。
SPM and SQL profiles
一条SQL语句可以同时具有SQL profile文件和SQL执行计划基线。在本系列的第3篇文章(点此查看)中描述了这种情况，我们通过接受SQL profile文件来演进SQL执行计划基线。在这种情况下，SPM感知优化器将同时使用SQL profile文件和SQL执行计划基线。SQL profile 文件包含有助于优化器准确计算每个已接受计划的成本并选择最佳执行计划的附加信息。SPM感知优化器可能在SQL profile文件存在时，选择一个与SQL profile文件不在时不同的，已接受的执行计划。
SPM and Stored Outlines
SQL语句可能具有存储大纲（Stored Outlines），也有SQL执行计划基线。如果SQL语句中存在一个存储大纲并已启用，则优化器将使用它。如果使用的是存储大纲，则可以通过创建SQL执行计划基线并禁用存储大纲来测试SPM。如果您对SPM（的结果）满意，则即可以删除存储大纲，也可以禁用它们。如果SPM不适合您（我们很想知道原因），您可以重新启用存储大纲。如果使用的是存储大纲，请注意它们的局限性：对于给定的SQL语句，一次只能有一个存储大纲。在某些情况下，这可能是好的，但当语句在不同的条件下执行时（例如，绑定变量值），单个计划不一定是最佳的。第二个限制与第一个限制有关。存储大纲不允许进化。也就是说，即使存在更好的计划，存储大纲仍将继续使用，这可能会降低系统的性能。为了得到更好的计划，您必须手动删除当前的存储大纲并生成一个新的。如果存储大纲中使用的访问路径（例如索引）被删除或变得不可用，则部分存储大纲将继续使用，这可能有使用更差的执行计划的可能性。
读者的一个问题是，我们将来如何处理使用存储大纲的特性。存储大纲将在未来的版本中取消支持，而倾向于使用SQL plan management。在Oracle Database 12c Release 2中，存储大纲仍像以前的版本一样起作用。但是，Oracle强烈建议您对新应用程序使用SQL plan management。SQL plan management创建SQL执行计划基线，与存储大纲相比，SQL计划基线提供了优异的SQL性能和稳定性。如果已有存储大纲，请考虑使用DBMS_SPM包的LOAD_PLANS_FROM_CURSOR_CACHE或LOAD_PLANS_FROM_SQLSET过程将它们迁移到SQL执行计划基线。迁移完成后，应禁用或删除存储的大纲。
SPM and Adaptive Cursor Sharin（自适应游标共享）
如果确定单个计划在所有条件下都不是最优的，那么自适应游标共享（ACS）可能会为给定的绑定敏感的SQL语句生成多个游标。每个游标都是通过强制对语句进行硬解析来生成的。优化器通常会在每次硬解析时选择成本最佳的计划。当您有一个语句的SQL执行计划基线时，SPM感知优化器将选择已接受的最佳计划，作为最佳计划。这也适用于绑定敏感语句的硬解析。可能有多个可接受的计划，每个计划对不同的绑定集都是最优的。启用SPM和ACS后，SPM感知优化器将为当前绑定集选择最佳计划。因此，如果发生硬解析，则不管语句是否对绑定敏感，都将使用通常的SPM计划选择算法。
Enterprise Manager
你可以通过企业管理器（EM)查看SQL执行计划基线并管理大多数的SPM任务。以下截图显示了这些任务中的两个：
Setting init.ora parameters for SPM
Loading SQL plan baselines from cursor cache
原文链接：https://blogs.oracle.com/optimizer/sql-plan-management-part-4-of-4:-user-interfaces-and-other-features
原文内容：
SQL Plan Management (Part 4 of 4): User Interfaces and Other Features
Maria Colgan
Master Product Manager
In 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 |
---------------------------------------------------------------
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
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 |
----------------------------------------------------------------
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. 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. Stored outlines will be desupported in a future release in favor of SQL plan management. In Oracle Database 12c Release 2, 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.
Enterprise Manager
You can view SQL plan baselines and configure and manage most SPM tasks through the Enterprise Manager. The screenshots below show two of these tasks.
Setting init.ora parameters for SPM
