打算把固定执行计划的方法做个整理和比较。上面的链接是SQL PROFILE的用法。这篇说下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 statement