ORACLE使用STORED OUTLINE固化执行计划--CURSOR_SHARING

xiaoheliushuiya

浏览: 403049 次

最近访客更多访客>>

1754575407

necmy

huangwen566

hsgzs01

博主相关

博客

微博

相册

留言

关于我

文章分类

全部博客 (1029)

社区版块

存档分类

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"<TO_NUMBER(:V))

Note
-----
   - dynamic sampling used for this statement (level=2)

SQL> 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"<TO_NUMBER(:VAR))

Note
-----
   - outline "OUTLINE2" 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语句必须使用绑定变量，且变量名称要和创建存储纲要时的变量名称一致；


当然我们也可以使用curosr_sharing参数来增强存储纲要的适用范围，但是这里存在一个限制，即cursor_sharing参数仅仅会影响通过CREATE_STORED_OUTLINES参数创建的存储纲要。官方文档描述如下：
See Also: Oracle Database can allow similar statements to shareSQL by replacing literals with system-generated bind variables.This works with plan stability if the outline was generated usingthe CREATE_STORED_OUTLINES parameter, not the CREATE OUTLINEstatement.
 Also, the outline must have been created with theCURSOR_SHARING parameter set to FORCE or SIMILAR, and theparameter must also set to FORCE or SIMILAR when attempting touse 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
-------------------------------------
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.