stored outline用来保存标准的sql执行计划,无论环境或统计信息的变化，都将不影响sql的性能。 11g以后stored outline被SQL plan management 代理,oracle建议11g以后将stored outline复制到SQL Plan Baselines
1. 创建outline
需要的权限
CREATE ANY OUTLINE
EXECUTE_CATALOG_ROLE
创建outline的两种方式
CREATE OUTLINE emp_dept FOR CATEGORY scott_outlines
ON SELECT e.empno, e.ename, d.dname FROM emp e;
--v$sql
BEGIN
DBMS_OUTLN.create_outline(
hash_value => SQL hash_value ,
child_number => 0,
category => 'SCOTT_OUTLINES');
END;
/
2. 查询outline
Outline存储于outln.OL$;outln.OL$HINTS; outln.OL$NODES;
也可以使用 dba_outlines视图查询
3. 使用outline
ALTER SESSION SET query_rewrite_enabled=TRUE;
ALTER SESSION SET use_stored_outlines=SCOTT_OUTLINES;
use_stored_outlines设置为true 使用DEFAULT目录
查询outline 是否被使用
SELECT name, category, used FROM user_outlines;
4. 删除outline
BEGIN
DBMS_OUTLN.drop_by_cat (cat => 'SCOTT_OUTLINES');
END;
/
5. 自动创建outline
启动该选型oracle 将自动对执行的sql创建outline
ALTER SYSTEM SET create_stored_outlines=TRUE| category_name ;
ALTER SESSION SET create_stored_outlines=TRUE| category_name ;
6. 调整outline的执行计划
How to Edit a Stored Outline to Use the Plan from Another Stored Outline (Doc ID 730062.1)
7. Outline 注意事项
Outline是SQL文本全匹配, 不支持FORCE_MATCHING,对变量绑定的sql 必须要保证变量值一致
8. 示例
SQL> conn / as sysdba
Connected.
SQL> grant connect,resource to test;
Grant succeeded.
SQL> grant create any outline,alter any outline to test;
Grant succeeded.
SQL> alter user outln identified by outln account unlock;
User altered.
SQL> conn test/oracle
Connected.
SQL> create table t as select * from all_objects;
Table created.
SQL> set autotrace traceonly explain;
SQL> select * from t where OBJECT_ID=888;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 27 | 3456 | 439 (2)| 00:00:06 |
|* 1 | TABLE ACCESS FULL| T | 27 | 3456 | 439 (2)| 00:00:06 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=888)
Note
-----
- dynamic sampling used for this statement
SQL> set autotrace off
SQL> create outline t_outln1 for category CATEGORY_T on select * from t where OBJECT_ID=888;
Outline created.
SQL> create index i_t on t(object_id);
Index created.
SQL> set autotrace traceonly explain;
SQL> select * from t where OBJECT_ID=258;
Execution Plan
----------------------------------------------------------
Plan hash value: 2928007915
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | 1 | 128 | 2 (0)| 00:00:
01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 128 | 2 (0)| 00:00:
01 |
|* 2 | INDEX RANGE SCAN | I_T | 1 | | 1 (0)| 00:00:
01 |
--------------------------------------------------------------------------------
----
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=258)
Note
-----
- dynamic sampling used for this statement
SQL> select * from t where OBJECT_ID=888;
Execution Plan
----------------------------------------------------------
Plan hash value: 2928007915
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | 1 | 128 | 2 (0)| 00:00:
01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 128 | 2 (0)| 00:00:
01 |
|* 2 | INDEX RANGE SCAN | I_T | 1 | | 1 (0)| 00:00:
01 |
--------------------------------------------------------------------------------
----
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=888)
Note
-----
- dynamic sampling used for this statement
SQL> alter session set use_stored_outlines=CATEGORY_T;
Session altered.
SQL> select * from t where OBJECT_ID=888;
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2027 | 253K| 439 (2)| 00:00:06 |
|* 1 | TABLE ACCESS FULL| T | 2027 | 253K| 439 (2)| 00:00:06 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=888)
Note
-----
- outline "T_OUTLN1" used for this statement
SQL> select * from t where OBJECT_ID=258;
Execution Plan
----------------------------------------------------------
Plan hash value: 2928007915
--------------------------------------------------------------------------------
----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
----
| 0 | SELECT STATEMENT | | 1 | 128 | 2 (0)| 00:00:
01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 128 | 2 (0)| 00:00:
01 |
|* 2 | INDEX RANGE SCAN | I_T | 1 | | 1 (0)| 00:00:
01 |
--------------------------------------------------------------------------------
----
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=258)
Note
-----
- dynamic sampling used for this statement