OracleOutline总结 - 数据库编程

DEX SQL> select name,category,used,sql_text from dba_outlines;
NAME CATEGORY USED SQL_TEXT ------------------------------ ---------- ------ ------------------------------------------------------- SYS_OUTLINE_14061210153067004 TEST USED select /* outlinetest2 */ /*+ full(dh_stat) */ * from d h_stat where id=771 SYS_OUTLINE_14061209594622403 TEST UNUSED select /* outlinetest1 */ * from dh_stat where id=771 SQL> alter session set use_stored_outlines=TRUE; 会话已更改。 SQL> select /* outlinetest1 */ * from dh_stat where id=771; ID NAME TYPE ---------- ------------------------------ --------------- 771 APPLY$_CONF_HDLR_COLUMNS_UNQ1 INDEX SQL> select name,category,used,sql_text from dba_outlines;
NAME CATEGORY USED SQL_TEXT ------------------------------ ---------- ------ ------------------------------------------------------- SYS_OUTLINE_14061210153067004 TEST USED select /* outlinetest2 */ /*+ full(dh_stat) */ * from d h_stat where id=771 SYS_OUTLINE_14061209594622403 TEST UNUSED select /* outlinetest1 */ * from dh_stat where id=771 SQL> exec dbms_outln.update_by_cat(oldcat=>'TEST',newcat=>'DEFAULT'); PL/SQL 过程已成功完成。 SQL> select /* outlinetest1 */ * from dh_stat where id=771; ID NAME TYPE ---------- ------------------------------ --------------- 771 APPLY$_CONF_HDLR_COLUMNS_UNQ1 INDEX SQL> select name,category,used,sql_text from dba_outlines; NAME CATEGORY USED SQL_TEXT ------------------------------ ---------- ------ ------------------------------------------------------- SYS_OUTLINE_14061210153067004 DEFAULT USED select /* outlinetest2 */ /*+ full(dh_stat) */ * from d h_stat where id=771 SYS_OUTLINE_14061209594622403 DEFAULT USED select /* outlinetest1 */ * from dh_stat where id=771 SQL>

select * from table(dbms_xplan.display_cursor('053nzgm4f6rdr','','')); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------- ....为了排版，省略了前面的4个子执行计划......... SQL_ID 053nzgm4f6rdr, child number 4 ------------------------------------- select /* outlinetest1 */ * from dh_stat where id=771 Plan hash value: 1845196118 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 124 (100)| | |* 1 | TABLE ACCESS FULL| DH_STAT | 1 | 38 | 124 (1)| 00:00:02 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): ------------------------------------------------- 1 - filter("ID"=771) Note ----- - outline "SYS_OUTLINE_14061209594622403" used for this statement 已选择102行。可以看到，这次outline已经和我们预期的一样生效，并且是通过全表扫描来实现而且通过多次实验证明，必须修改一下outline的类别名或者将语句刷出共享池才能实现，因此，我们先将outline创建为一个私有的类别，等验证完毕且符合预期后，再正式发布出来，这样也不会需要修改数据库当前已有参数。

OracleOutline总结(三)