ORACLE使用STOREDOUTLINE固化执行计划

欢迎进入Oracle社区论坛，与200万技术人员互动交流 >>进入 当然我们也可以使用curosr_sharing参数来增强存储纲要的适用范围，但是这里存在一个限制，即cursor_sharing参数仅仅会影响通过CREATE_STORED_OUTLINES参数创建的存储纲要。官方文档描述如下： See A

欢迎进入Oracle社区论坛，与200万技术人员互动交流 >>进入

当然我们也可以使用curosr_sharing参数来增强存储纲要的适用范围，但是这里存在一个限制，即cursor_sharing参数仅仅会影响通过CREATE_STORED_OUTLINES参数创建的存储纲要。官方文档描述如下：
See Also: Oracle Database can allow similar statements to share SQL by replacing literals with system-generated bind variables. This works with plan stability if the outline was generated using the CREATE_STORED_OUTLINES parameter, not the CREATE OUTLINE statement. Also, the outline must have been created with the CURSOR_SHARING parameter set to FORCE or SIMILAR, and the parameter must also set to FORCE or SIMILAR when attempting to use the outline.
首先在session1中：
SQL> show user
USER 为 "SYS"
SQL> show parameter cursor_shar
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
SQL> alter system set cursor_sharing=force;
系统已更改。
session 2中
SQL> show user
USER 为 "EASY1"
SQL> select ol_name,creator,sql_text from outln.ol$;
OL_NAME CREATOR
------------------------------ ------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
OUTLINE2 EASY1
select count（*） from t1 where object_id < :var
SQL> alter session set create_stored_outlines=true;
会话已更改。
SQL> select count（*） from t1 where object_id < 500;
COUNT（*）
----------
478
SQL> alter session set create_stored_outlines=false;
会话已更改。
SQL> select ol_name,creator,sql_text from outln.ol$;
OL_NAME CREATOR
------------------------------ ------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
SYS_OUTLINE_14010314202705203 EASY1
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL（SAMPLESUB
OUTLINE2 EASY1
select count（*） from t1 where object_id < :var
SYS_OUTLINE_14010314202706005 EASY1
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS opt_param（'parallel_execution_enabled', '
OL_NAME CREATOR
------------------------------ ------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
SYS_OUTLINE_14010314202705001 EASY1
select count（*） from t1 where object_id < :"SYS_B_0" --系统自动创建了outline
SQL> alter session set use_stored_outlines=true;
会话已更改。
SQL> set autotrace on explain
SQL> select count（*） from t1 where object_id < 600;
COUNT（*）
----------
566
执行计划
----------------------------------------------------------
Plan hash value: 3900446664
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 3 （0）| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | INDEX RANGE SCAN| I1 | 566 | 7358 | 3 （0）| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information （identified by operation id）：
---------------------------------------------------
2 - access（"OBJECT_ID"<600）
Note
-----
- dynamic sampling used for this statement （level=2）
SQL> set autotrace off
SQL> select count（*） from t1 where object_id < 600;
COUNT（*）
----------
566
SQL> select * from table（dbms_xplan.display_cursor（））；
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID3y9v7qyqns9ws, child number 1
-------------------------------------
select count（*） from t1 where object_id < :"SYS_B_0"
Plan hash value: 3900446664
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 （100）| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|* 2 | INDEX RANGE SCAN| I1 | 4411 | 57343 | 3 （0）| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information （identified by operation id）：
---------------------------------------------------
2 - access（"OBJECT_ID"<:SYS_B_0）
Note
-----
- outline "SYS_OUTLINE_14010314202705001" used for this statement --已经使用了自动生成的存储纲要，但是使用autotrace或者explain却无法表明这点，可能是个bug
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
已选择23行。
另外有一点需要特别注意： When Oracle Database creates an outline, plan stability examines the optimization results using the same data used to generate the execution plan. That is, Oracle Database uses the input to the execution plan to generate an outline, and not the execution plan itself.

[1] [2] [3]