Oracle outline

1.参数设置
alter session set create_stored_outlines = true;


2.创建测试表


SQL> create table mmm as select * from dba_objects;


Table created.




3.创建一个 outline


SQL>  create or replace outline  myoutline for category cate_outline  on select * from mmm where object_id=:p;  
  


Outline created.


4.查看创建情况




SQL> SELECT name,category,sql_text FROM user_outlines ;


NAME                           CATEGORY
------------------------------ ------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
MYOUTLINE                      CATE_OUTLINE
select * from mmm where object_id=:p


5.执行查询


SQL> var p NUMBER;
SQL> EXEC :p :=1000;
SQL> select * from mmm where object_id=:p;




Execution Plan
----------------------------------------------------------
Plan hash value: 1266100218


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   894 |   180K|   300   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| MMM  |   894 |   180K|   300   (1)| 00:00:04 |
--------------------------------------------------------------------------


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


   1 - filter("OBJECT_ID"=TO_NUMBER(:P))


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




Statistics
----------------------------------------------------------
        134  recursive calls
         72  db block gets
       1164  consistent gets
          0  physical reads
      16264  redo size
       1623  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


SQL> select count(*) from mmm where object_id=:p;




Execution Plan
----------------------------------------------------------
Plan hash value: 381537006


---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    13 |   300   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
|*  2 |   TABLE ACCESS FULL| MMM  |   894 | 11622 |   300   (1)| 00:00:04 |
---------------------------------------------------------------------------


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


   2 - filter("OBJECT_ID"=TO_NUMBER(:P))


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




Statistics
----------------------------------------------------------
         59  recursive calls
         28  db block gets
       1158  consistent gets
          0  physical reads
       6200  redo size
        526  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


6.查看outline使用情况


SQL> SELECT name,category,used FROM user_outlines;


NAME                           CATEGORY                       USED
------------------------------ ------------------------------ ------
SYS_OUTLINE_17022209004809104  DEFAULT                        USED
SYS_OUTLINE_17022209003316503  DEFAULT                        UNUSED
MYOUTLINE                      CATE_OUTLINE                   UNUSED
SYS_OUTLINE_17022209003316401  DEFAULT                        USED




这种情况是因为使用 default 的原因
从10g起，可以通过引用共享池中已经存在的SQL语句来创建outline
可使用命令


ALTER SESSION SET USE_STORED_OUTLINES=cate_outline;
或者
ALTER database SET USE_STORED_OUTLINES=cate_outline;
设置系统使用自己的outline,否则会使用默认的.是从内存池里拿的.




SQL> ALTER SESSION SET USE_STORED_OUTLINES=cate_outline;


Session altered.


SQL> select count(*)  from mmm;




Execution Plan
----------------------------------------------------------
Plan hash value: 381537006


-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   299   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| MMM  | 89358 |   299   (1)| 00:00:04 |
-------------------------------------------------------------------


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




7.删除outline


exec dbms_outln.drop_by_cat('cate_outline');
EXECUTE DBMS_OUTLN.CLEAR_USED('myoutline');
 
EXECUTE DBMS_OUTLN.drop_unused; -- 这是删除所有状态为unused




8.更改outline
1、更改outline名称
alter outline SYS_OUTLINE_14061114223605901 rename to dh_test2
2、更改outline类别
exec dbms_outln.update_by_cat(oldcat=>'DDD',newcat=>'DEFAULT');
3、重建outline
alter outline DH_TEST2 rebuild;


9.根据plan创建outline
SQL> exec dbms_outln.create_outline(1970577185,0,'MYCAT1');
PL/SQL procedure successfully completed


































关于outlines的使用，有几个需要注意的地方：




1. 参数cursor_sharing设置为force时，outlines将无效；
2. literial sql的共享程度不高的情况下，使用outline会生产很多个执行计划，可能会有一些问题；
3. 一般情况我们在使用outline的时候，也是发现某个sql的执行计划不稳定的时候，由于执行计划是基于统计信息的，那么由于生产系统中统计信息可能是在不断的变化，那么使用outline固定的执行计划不见得一定就是最好的，这一点需要考虑。
4. 由于outlines信息的存放在用户outln下，那么该用户就显得尤为重要，维护的时候需要注意，不能随便给drop了。
5. outline创建以后，不是说就一成不变了，可以进行编辑，至于什么时候编辑，怎么编辑，大家可以参考


metalink文档 730062.1 How to Edit a Stored Outline to Use the Plan from Another Stored Outline。