oracle执行计划存储纲要和SQL语句之间是一一对应的关系，因此如果我们改变了sql语句中的谓词条件，存储纲要就会失去作用或者说我们需要创建新的存储纲要来巩固执行计划，为了避免这种情况，我们可以使用变量来替代文本信息。
在没有使用绑定变量的情况下：
SQL> show user
USER 为 "EASY1"
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
T1 TABLE
SQL> select ol_name,creator from outln.ol$;
未选定行
SQL> create outline outline1 on select count(*) from t1 where object_id < 100;
大纲已创建。
SQL> set autotrace on explain
SQL> alter session set use_stored_outlines=true;
会话已更改。
SQL> select count(*) from t1 where object_id < 100;
COUNT(*)
----------
98
执行计划
----------------------------------------------------------
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 | 4411 | 57343 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<100)
Note
-----
- outline "OUTLINE1" used for this statement
SQL> select count(*) from t1 where object_id < 200;
COUNT(*)
----------
192
执行计划
----------------------------------------------------------
Plan hash value: 3900446664
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | INDEX RANGE SCAN| I1 | 192 | 2496 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<200)
Note
-----
- dynamic sampling used for this statement (level=2)
由此可见，如果不采用绑定变量，那么存储纲要必须要和sql text完全匹配才可发挥作用；
在使用绑定变量的情况下：
SQL> drop outline outline1;
大纲已删除。
SQL> create outline outline2 on select count(*) from t1 where object_id < :var;
大纲已创建。
SQL> var v number;
SQL> exec :v := 300;
PL/SQL 过程已成功完成。
SQL> select count(*) from t1 where object_id < :v;
COUNT(*)
----------
286
执行计划
----------------------------------------------------------
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 | 4580 | 59540 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID" var var number;
SQL> exec :var := 300;
PL/SQL 过程已成功完成。
SQL> select count(*) from t1 where object_id < :var;
COUNT(*)
----------
286
执行计划
----------------------------------------------------------
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 | 4411 | 57343 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"
SQL> select count(*) from t1 where object_id < 200; COUNT(*) ---------- 192 执行计划 ---------------------------------------------------------- Plan hash value: 3900446664 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 13 | | | |* 2 | INDEX RANGE SCAN| I1 | 192 | 2496 | 2 (0)| 00:00:01 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"<200) Note ----- - dynamic sampling used for this statement (level=2)
在使用绑定变量创建存储纲要时，要使存储纲要发挥作用，sql语句必须使用绑定变量，且变量名称要和创建存储纲要时的变量名称一致；
当然我们也可以使用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_ID 3y9v7qyqns9ws, child number 1
--------------------------