--- ------------------------------ ---------------
771 APPLY$_CONF_HDLR_COLUMNS_UNQ1 INDEX
SQL> select name,category,used,sql_text from dba_outlines;
NAME CATEGORY USED SQL_TEXT
------------------------------ ---------- ------ -------------------------------------------------------
SYS_OUTLINE_14061209594622403 TEST USED select /* outlinetest1 */ * from dh_stat where id=771 再次查询OUTLINE的使用状态，可以看到，我们刚新建的outline确实已经被使用过，通过下面的执行计划，更加可以证实这一点
SQL> select * from table(dbms_xplan.display_cursor('053nzgm4f6rdr','',''));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 053nzgm4f6rdr, child number 0
-------------------------------------
select /* outlinetest1 */ * from dh_stat where id=771
Plan hash value: 2780970545
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| DH_STAT | 1 | 38 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_1 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=771)
SQL_ID 053nzgm4f6rdr, child number 1
-------------------------------------
select /* outlinetest1 */ * from dh_stat where id=771
Plan hash value: 2780970545
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| DH_STAT | 1 | 38 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_1 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=771)
Note
-----
- outline "SYS_OUTLINE_14061209594622403" used for this statement
已选择42行。
示例二、编辑outline的两种方法示例
1、第一种是直接修改字典里的hint，这里就不测试了。
2、通过更换两个outline名称，来完成修改 --这个实验紧接着“示例一（引用使用共享池中的SQL来创建outline）" SQL> exec dbms_outln.clear_used(name=>'SYS_OUTLINE_14061209594622403');
PL/SQL 过程已成功完成。 ---通过上面的过程，将outline的使用记录清理掉，USED状态再次转变为UNSED，方便我们测试
SQL> select name,category,used,sql_text from dba_outlines;
NAME CATEGORY USED SQL_TEXT
------------------------------ ---------- ------ -------------------------------------------------------
SYS_OUTLINE_14061209594622403 TEST UNUSED select /* outlinetest1 */ * from dh_stat where id=771
SQL> select /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=771;
ID NAME TYPE
---------- ------------------------------ ---------------
771 APPLY$_CONF_HDLR_COLUMNS_UNQ1 INDEX
SQL> select sql_text,sql_id,hash_value,child_number from v$sql a where sql_text like '%outlinetest2%' and sql_text not like '%v$sql%';
SQL_TEXT SQL_ID HASH_VALUE CHILD_NUMBER
------------------------------------------------------- ------------- ---------- -------