1.授权。赋予用户scott创建及执行outline的相应权限
SQL> GRANT CREATE ANY OUTLINE TO SCOTT;
Grant succeeded.
SQL> GRANT EXECUTE_CATALOG_ROLE TO SCOTT;
Grant succeeded.
2. 创建outline，以下创建一个名为test_outln的outline，期间执行了一条sql，该sql的执行计划被保存到test_outln中。
SQL> alter session set optimizer_mode=rule;
Session altered.
SQL> alter session set create_stored_outlines = TEST_OUTLN;
Session altered.
SQL> select * from emp where empno=9000;
no rows selected
SQL> alter session set create_stored_outlines = false;
Session altered.
3.使用outline。在会话级激活outline，并执行与上面相同的sql。
SQL> SELECT name, category, used FROM user_outlines;
NAME CATEGORY USED
------------------------------ ------------------------------ ------
SYS_OUTLINE_09080707363664016 TEST_OUTLN UNUSED
SQL> ALTER SESSION SET query_rewrite_enabled=TRUE;
Session altered.
SQL> ALTER SESSION SET use_stored_outlines=TEST_OUTLN;
Session altered.
SQL> select * from emp where empno=9000;
no rows selected
SQL> SELECT name, category, used FROM user_outlines;
NAME CATEGORY USED
------------------------------ ------------------------------ ------
SYS_OUTLINE_09080707363664016 TEST_OUTLN USED
SQL> set autot traceonly explain
SQL> set lines 132
SQL> select * from emp where empno=9000;
Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 37 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=9000)
Note
-----
- outline "SYS_OUTLINE_09080707363664016" used for this statement
可以发现，outline已经生效。
另外，通过查询v$sql.OUTLINE_CATEGORY，也可以查看哪些sql使用了outline。
SQL> select sql_text,outline_sid,outline_category from v$sql where outline_CATEGORY is not null;
SQL_TEXT OUTLINE_SID OUTLINE_CATEGORY
-------------------------------------------------- ----------- ----------------------------------------------------------------
select * from emp where empno=9000 TEST_OUTLN
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/10972173/viewspace-611588/ ，如需转载，请注明出处，否则将追究法律责任。
