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     SQL> alter session set create_stored_outlines=true;
    会话已更改。
    SQL> select count（*） from t1 where object_id     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     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     SQL> alter session set use_stored_outlines=true;
    会话已更改。
    SQL> set autotrace on explain
    SQL> select count（*） from t1 where object_id     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"    Note
    -----
    - dynamic sampling used for this statement （level=2）
    SQL> set autotrace off
    SQL> select count（*） from t1 where object_id     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     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]