outline

目的：让执行计划走上全表扫描

步骤一-------------------------创建测试表，根据DBA_OBJECTS创建，OBJECT_ID上有索引
Create table wxh_tbd as select * from dba_objects;
create index t_3 on wxh_tbd(object_id);

步骤二--------------------------创建outline。由于默认的执行计划是索引的，因此后续我们需要修改这个OUTLINE。让它走全表
create or replace outline pub_out on select * from wxh_tbd where object_id=:1;


步骤三--------------------------创建两个私有OUTLINE，之所以创建两个，是为了让彼此的HINT做交换
create or replace private outline pri_out_1 on select * from wxh_tbd where object_id=:1;
create or replace private outline pri_out_2 on select /*+ full(wxh_tbd) */ * from wxh_tbd where object_id=:1;

步骤四-------------------------交换两个OUTLINE的HITN
update ol$hints set ol_name=decode(ol_name,'PRI_OUT_1','PRI_OUT_2','PRI_OUT_2','PRI_OUT_1') where ol_name in ('PRI_OUT_1','PRI_OUT_2');
commit;

步骤五------------------------测试结果。在当前SESSION生效，看到执行计划NOTE部分用到了OUTLINE
alter session set use_private_outlines=true;
explain plan for
select * from wxh_tbd where object_id=:1;
select * from table(dbms_xplan.display(null,null,'outline'));
-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |   577 |    99K|   140   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| WXH_TBD |   577 |    99K|   140   (2)| 00:00:02 |
-----------------------------------------------------------------------------
Note
-----
   - outline "PRI_OUT_1" used for this statement

步骤六--------------------------发布到公共OUTLINE
create or replace outline pub_out from private PRI_OUT_1;

步骤七--------------------------另开一个SESSION，确定已经用到了OUTLINE
SQL> explain plan for
  2   select * from wxh_tbd where object_id=:1;
已解释。
SQL> select * from table(dbms_xplan.display(null,null,'outline'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3295978849
-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |   577 |    99K|   140   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| WXH_TBD |   577 |    99K|   140   (2)| 00:00:02 |
-----------------------------------------------------------------------------
Note
-----

   - outline "PUB_OUT" used for this statemen

个人补充，使用到的两个参数

------------------------------------------------------------------------------------

SQL> show parameter outline

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
create_stored_outlines               string      FALSE    --关闭自动捕获

SQL> alter system set USE_STORED_OUTLINES=true;  --使用outline开关

个体私有会话参数，会话级别生效

SQL> alter session set use_private_outlines=true;