oracle创建简单的outline

优点：数据库的outline是为了让某些sql走指定的执行计划，不会随创建索引，数据量的增加，统计信息的收集等影响执行计划。

缺点：这个执行计划不一定是最好的

SQL> conn /as sysdba
已连接。
SQL> grant create any outline to scott;
授权成功。

SQL> conn scott/scott
已连接。

SQL> exec dbms_outln_edit.create_edit_tables;
PL/SQL 过程已成功完成。

SQL> alter session set create_stored_outlines=xionglang7;--这里仅仅设置的session级别的
会话已更改。

SQL> alter session set use_stored_outlines=xionglang7;
会话已更改。

SQL> create or replace outline t2_empno for category xionglang7 on select ename,
job,sal,deptno from t2 where empno=7499;
大纲已创建。

SQL> select ename,job,sal,deptno from t2 where empno=7499;
已选择1920行。

执行计划
----------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1880 | 47000 |    49   (3)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T2   |  1880 | 47000 |    49   (3)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("EMPNO"=7499)

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

统计信息
----------------------------------------------------------
         33  recursive calls
        123  db block gets
        311  consistent gets
          0  physical reads
        568  redo size
      26345  bytes sent via SQL*Net to client
       1782  bytes received via SQL*Net from client
        129  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
       1920  rows processed

--创建索引后是否走索引
SQL> create index t2_empno on t2(empno);
索引已创建。

SQL> select ename,job,sal,deptno from t2 where empno=7499;
已选择1920行。

执行计划
----------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1880 | 47000 |    49   (3)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T2   |  1880 | 47000 |    49   (3)| 00:00:01 |      --查看执行计划依然走的全表扫描
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("EMPNO"=7499)

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

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        291  consistent gets
          0  physical reads
          0  redo size
      26345  bytes sent via SQL*Net to client
       1782  bytes received via SQL*Net from client
        129  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1920  rows processed

查询用户的outline

SQL> SELECT name, category, used FROM user_outlines;

oracle官网给的outline的信息：

A stored outline is a collection of hints associated with a specific SQL statement that allows a standard execution plan to be maintained, regardless of changes in the system environment or associated statistics. Plan stability is based on the preservation of execution plans at a point in time where the performance of a statement is considered acceptable. The outlines are stored in the OL$, OL$HINTS, and OL$NODES tables, but the [USER|ALL|DBA]_OUTLINES and[USER|ALL|DBA]_OUTLINE_HINTS views should be used to display information about existing outlines.

All of the caveats associated with optimizer hints apply equally to stored outlines. Under normal running the optimizer chooses the most suitable execution plan for the current circumstances. By using a stored outline you may be forcing the optimizer to choose a substandard execution plan, so you should monitor the affects of your stored outlines over time to make sure this isn’t happening. Remember, what works well today may not tomorrow.