固定执行计划--通过OUTLINE实现

SQL> select * from V$version;


BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

1. 正常的执行计划

SQL> create table t1 as select * from dba_objects;


SQL> set autot trace exp

1.1未创建索引的执行计划

SQL> select * from t1 where object_id=111;


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   177 |    37   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |   177 |    37   (0)| 00:00:01 |
--------------------------------------------------------------------------

1.2创建了索引的执行计划

SQL> create index ind_t1 on t1(object_id);
SQL> select * from t1 where object_id=111;


--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |   177 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |     1 |   177 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_T1 |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

2.使用了OUTLINE的执行计划

2.1 未指定category

SQL> drop index ind_t1;
SQL> select * from t1 where object_id=111;


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   177 |    37   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |   177 |    37   (0)| 00:00:01 |
--------------------------------------------------------------------------

2.1.1未使用category创建outline

SQL> create outline o_t1_noc on
  2  select * from t1 where object_id=111;


SQL> create index ind_t1 on t1(object_id);


SQL> select * from t1 where object_id=111;


--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |   177 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |     1 |   177 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_T1 |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------


可以看到索引生效,outline未生效。

2.1.2 指定category，让上面Outline生效

SQL> alter session set use_stored_outlines=default;
如果在创建outline时未指定category，则catagory为默认的default。也可以在这设为true以使用默认的default
本次使用alter session,全局指定用alter system 


SQL> select * from t1 where object_id=111;


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   107 | 18939 |    37   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   107 | 18939 |    37   (0)| 00:00:01 |
--------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - filter("OBJECT_ID"=111)


Note
-----
   - outline "O_T1_NOC" used for this statement
   
   可以看到走了全表，并在最后提示使用o_t1_noc
   
   

 2.2 使用有category的outline

 
 
SQL> drop index ind_t1;
SQL> drop outline o_t1_noc;




SQL> select * from t1 where object_id=111;


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   177 |    37   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |   177 |    37   (0)| 00:00:01 |
--------------------------------------------------------------------------






   
 SQL> create index ind_t1 on t1(object_id);
SQL> select * from t1 where object_id=111;


Execution Plan
----------------------------------------------------------
Plan hash value: 634656657
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |   177 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |     1 |   177 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_T1 |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------




SQL> alter session set use_stored_outlines=c_t1;


SQL> select * from t1 where object_id=111;




--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   107 | 18939 |    37   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   107 | 18939 |    37   (0)| 00:00:01 |
--------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - filter("OBJECT_ID"=111)


Note
-----
   - outline "O_T1_USEC" used for this statement

3.对outline的管理

3.1清空未用的Outline
exec DBMS_OUTLN.DROP_UNUSED
3.2 查看创建好的outline
select * from user_outlines;


3.2关于use_stored_outlines
To use stored outlines when Oracle compiles a SQL statement, set the system parameter USE_STORED_OUTLINES to true or to a category name. If you set USE_STORED_OUTLINES to true, then Oracle uses outlines in the default category. If you specify a category with the USE_STORED_OUTLINES parameter, then Oracle uses outlines in that category until you reset the parameter to another category name or until you suspend outline use by setting USE_STORED_OUTLINES to false




   

参考文章：

http://docs.oracle.com/cd/B19306_01/server.102/b14211/outlines.htm#sthref1341