Oracle Outline总结

和通数据库htsjk.Com2019-03-07 19:09 来源:未知阅读:6030 评论 474 热度5

标签：

Oracle Outline总结

一、基本概述 Oracle Outline，中文也称为存储大纲，是最早的基于提示来控制SQL执行计划的机制，也是9i以及之前版本唯一可以用来稳定和控制SQL执行计划的工具。 outline是一个hints（提示）的集合，更具体的讲，outline可以锁定一个给定SQL的执行计划，保持其执行计划稳定，不管数据库环境如何变更（如统计信息，部分参数等）注意： 二、运行机制
Outline将执行计划的hint集合保存在outline的表中（数据字典）。当执行SQL解析时，Oracle会与outline中的SQL比较，如果该SQL有保存的outline，则通过保存的hint集合生成指定执行计划。
注意：
三、使用场景 注意任何一个数据库中，大部分的SQL语句执行计划应该是通过优化器自动生成，并且高效运行，而只有极少部分，需要通过各种工具（outine、sql profile）来锁定执行计划 四、注意事项 五、outline相关的视图

两个基本视图：dba_outlines,dba_outline_hints
三个底层表：ol$、ol$hints、ol$nodes

六、使用outline 一、创建outline 创建outline的方法有三种，下面我们一一简单介绍 1、给会话甚至整个系统执行的每一条SQL语句都创建outline，可以设置如下参数，分别针对会话级和系统级 ALTER SYSTEM SET create_stored_outlines=TRUE; ALTER SESSION SET create_stored_outlines=TRUE; 注意：基本上没有任何一个数据库会这么做，因此这种方式我们不做测试； 2、手工通过CREATE OUTLINE方式来创建给定SQL语句的outline，如下 CREATE or replace OUTLINE outline_dh_test FOR CATEGORY test on select * from dh_stat where id=11; or CREATE or replace OUTLINE outline_dh_test1 on select * from dh_stat where id=11; 示例：
SQL> CREATE or replace OUTLINE outline_dh_test FOR CATEGORY test on select * from dh_stat where id=11; Outline created. SQL> set linesize 200 pagesize 999
SQL> set long 30
SQL> set long 50
SQL> select name,owner,category,used,sql_text from dba_outlines;
NAME     OWNER   CATEGORY USED SQL_TEXT
------------------------------ ------------------------------ ------------------------------ ------
OUTLINE_DH_TEST   DBMON TEST UNUSED select * from dh_stat where id=11
SQL> select name,hint from dba_outline_hints;
NAME   HINT
------------------------------ --------------------------------------------------
OUTLINE_DH_TEST FULL(@"SEL$1" "DH_STAT"@"SEL$1")
OUTLINE_DH_TEST OUTLINE_LEAF(@"SEL$1")
OUTLINE_DH_TEST ALL_ROWS
OUTLINE_DH_TEST OPT_PARAM('_optimizer_use_feedback' 'false')
OUTLINE_DH_TEST OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'fa
OUTLINE_DH_TEST OPT_PARAM('_optimizer_extended_cursor_sharing_rel'
OUTLINE_DH_TEST OPT_PARAM('_bloom_pruning_enabled' 'false')
OUTLINE_DH_TEST OPT_PARAM('_gby_hash_aggregation_enabled' 'false')
OUTLINE_DH_TEST OPT_PARAM('_optimizer_extended_cursor_sharing' 'no
OUTLINE_DH_TEST OPT_PARAM('_bloom_filter_enabled' 'false')
OUTLINE_DH_TEST OPT_PARAM('_optimizer_null_aware_antijoin' 'false'
OUTLINE_DH_TEST OPT_PARAM('_optim_peek_user_binds' 'false')
OUTLINE_DH_TEST DB_VERSION('11.2.0.1')
OUTLINE_DH_TEST OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
OUTLINE_DH_TEST IGNORE_OPTIM_EMBEDDED_HINTS
15 rows selected. 注意：

不指定outline类别是，默认为default，而且此处创建时，不能指定为default类别（会报错）。
这个方法不是很方便，因为必须将整个SQL文本作为语句的一部分，可能导致语句无法共享等问题，因此很少使用这种方法

3、从10g起，可以通过引用共享池中已经存在的SQL语句来创建outline exec DBMS_OUTLN.create_outline(hash_value=>1752921103,child_number => 0,category=>'test'); 注意

这种方法不能指定outline的具体名字，由系统自动生成，可以通过alter outline SYS_OUTLINE_14061114223605901 rename to dh_test2修改
不指定类别的话默认为default，而且此处创建时，不能指定为default类别（会报错）。
我们使用outline固定执行计划时，一般都是选用此种方法
后面有一个简单示例，可以加深理解

二、修改outline 1、更改outline名称 alter outline SYS_OUTLINE_14061114223605901 rename to dh_test2 2、更改outline类别 exec dbms_outln.update_by_cat(oldcat=>'DDD',newcat=>'DEFAULT'); 3、重建outline alter outline DH_TEST2 rebuild; 三、激活outline Oracle优化器只会考虑激活的outline，这意味着如果创建的outline没有被激活，Oracle根本不会使用它，一个outline必须满足如下两个条件才能被激活： 1、outline必须处于可用状态（创建时默认就是可用，一般不会有问题），修改方法,alter outline DH_TEST2 disable; 2、类别必须通过初始化参数use_stored_ouotlines在会话或者系统级激活，可以设置为“TRUE/FALSE/类别名”三种，其中TRUE表示启用default类别 alter session set use_stored_outlines=TRUE; 四、移动outline 只能通过将数据字典中保存的hint数据复制到另一个数据库的数据字典，除此之外没有其它办法。不过还好这个方法也非常简单，因为outline相关的hint数据都保存在outln用户下的三张表中：ol$、ol$hints、ol$nodes。可以用下面的命令来导入和导出可用的outline exp tables=(outln.ol$,outln.ol$hints,outln.ol$nodes) file=xxx.dmp 五、编辑outline，后续会提供两种方法 当优化器无法为给定的SQL生成高效的执行计划时，可以通过编辑outline来实现，可以理解为修改outline中的hint 1、使用原语句建Outline 2、查看Outline执行计划

Select HINT_TEXT,USER_TABLE_NAME,JOIN_PRED,cardinality,bytes,cost
from OUTLN.OL$HINTS
where ol_name = 'OLXXXXX_ORIG'

3、在SQL上加hint，执行语句（注意语句结构不能改变，不能增加或删除查询块什么的） 4、查看加上hint的SQL语句，执行计划是否与我们期望的一致 5、得到期望的执行计划后，将两个outline的执行计划进行互换，即完成outline的编辑！ UPDATE OUTLN.OL$HINTS
   SET OL_NAME = DECODE(OL_NAME,
                        'OLXXXXX_MOD',
                        'OLXXXXX_ORIG',
                        'OLXXXXX_ORIG',
                        'OLXXXXX_MOD')
WHERE OL_NAME IN ('OLXXXXX_MOD', 'OLXXXXX_ORIG'); 6、启用outline 六、删除outline 可以用如下命令分别删除指定的outline或者某个类别下的所有outline drop outline dh_test1; dbms_outln.drop_by_cat(cat=>'test');
示例一（引用使用共享池中的SQL来创建outline） SQL> create table dh_stat as select rownum id ,object_name name ,object_type type from dba_objects; SQL> create index ind_1 on dh_stat(id) compute statistics; SQL> alter system flush shared_pool;
系统已更改。
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'dbmon',TABNAME=>'dh_stat',ESTIMATE_PERCENT=>30,METHOD_OPT=>'FOR ALL COLUMNS SIZE 1',NO_INVALIDATE=>FALSE,CASCADE=>TRUE,DEGREE => 1);
PL/SQL 过程已成功完成。
SQL>  col name format a15
SQL>  col name format a30
SQL>  col sql_text format a55 给运行的语句添加一个独特的注释，方便后续查找语句
SQL> select /* outlinetest1 */ * 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 '%outlinetest1%' and sql_text not like '%v$sql%';
SQL_TEXT SQL_ID HASH_VALUE CHILD_NUMBER
------------------------------------------------------- ------------- ---------- ------------
select /* outlinetest1 */ * from dh_stat where id=771   053nzgm4f6rdr 3370343863   0
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)
已选择19行。

SQL> exec DBMS_OUTLN.create_outline(hash_value=>3370343863,child_number => 0,category=>'TEST');
PL/SQL 过程已成功完成。
SQL> col category format a10
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
可以看到，outline确实已经生成
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_14061209594622403 TEST   UNUSED select /* outlinetest1 */ * from dh_stat where id=771
此处outline的USED状态没有改变，因为我们没有激活TEST类别的outline，再次申明，outline必须通过use_stored_outlines参数激活后，优化器才会使用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)
已选择19行。
下面这一步激活TEST类别的OUTLINE，也可以在系统级激活OUTLINE
SQL> alter session set use_stored_outlines=TEST;
会话已更改。
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_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
------------------------------------------------------- ------------- ---------- ------------
select /* outlinetest2 */ /*+ full(dh_stat) */ * from d 3fcq9c8xu4a92 1000483106   0
h_stat where id=771
SQL> alter session set use_stored_outlines=true;
会话已更改。
SQL> select * from table(dbms_xplan.display_cursor('3fcq9c8xu4a92','',''));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3fcq9c8xu4a92, child number 0
-------------------------------------
select /* outlinetest2 */ /*+ full(dh_stat) */ * 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)
已选择19行。

SQL> exec DBMS_OUTLN.create_outline(hash_value=>1000483106,child_number => 0,category=>'TEST');
PL/SQL 过程已成功完成。
SQL> select name,category,used,sql_text from dba_outlines;

NAME   CATEGORY USED SQL_TEXT
------------------------------ ---------- ------ -------------------------------------------------------
SYS_OUTLINE_14061210153067004 TEST UNUSED 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
可以看到，我们新建的outline，确实已经成功
SQL> alter session set use_stored_outlines=TEST;
会话已更改。
SQL> select /* outlinetest1 */ * from dh_stat where id=771;
ID NAME     TYPE
---------- ------------------------------ ---------------
771 APPLY$_CONF_HDLR_COLUMNS_UNQ1 INDEX
SQL> select /* outlinetest2 */ /*+ full(dh_stat) */ * 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   USED   select /* outlinetest1 */ * from dh_stat where id=771
可以看到，两个outline都已经标记为已经使用过
SQL> select * from table(dbms_xplan.display_cursor('3fcq9c8xu4a92','',''));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 3fcq9c8xu4a92, child number 0
-------------------------------------
select /* outlinetest2 */ /*+ full(dh_stat) */ * 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_14061210153067004" used for this statement
已选择23行。

SQL> select * from table(dbms_xplan.display_cursor('053nzgm4f6rdr','',''));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------
....此处为了排版，省略了一个child number 0 的执行计划！........ 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行。
SQL> alter session set use_stored_outlines=TRUE;
会话已更改。通过如下方式，我们调换两个outline里面的hints
SQL> UPDATE OUTLN.OL$HINTS
  2     SET OL_NAME = DECODE(OL_NAME,
  3   'SYS_OUTLINE_14061210153067004',
  4   'SYS_OUTLINE_14061209594622403',
  5   'SYS_OUTLINE_14061209594622403',
  6   'SYS_OUTLINE_14061210153067004')
  7   WHERE OL_NAME IN ('SYS_OUTLINE_14061210153067004', 'SYS_OUTLINE_14061209594622403');
已更新12行。
SQL> commit;
提交完成。
SQL> col hint_text format a50
SQL> select hint#,hint_text from outln.ol$hints a where ol_name='SYS_OUTLINE_14061209594622403';
HINT# HINT_TEXT
---------- --------------------------------------------------
1 FULL(@"SEL$1" "DH_STAT"@"SEL$1")
2 OUTLINE_LEAF(@"SEL$1")
3 ALL_ROWS
4 DB_VERSION('11.2.0.1')
5 OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
6 IGNORE_OPTIM_EMBEDDED_HINTS
已选择6行。
果然和我们预期的一样，outline里面的执行计划已经调换
SQL> select /* outlinetest1 */ * from dh_stat where id=771;

ID NAME     TYPE
---------- ------------------------------ ---------------
771 APPLY$_CONF_HDLR_COLUMNS_UNQ1 INDEX
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
SQL_ID 053nzgm4f6rdr, child number 2
-------------------------------------
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)
已选择61行。
激活outline
SQL> alter session set use_stored_outlines=TEST;
会话已更改。

SQL> exec dbms_outln.clear_used(name=>'SYS_OUTLINE_14061209594622403');
PL/SQL 过程已成功完成。

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> 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   USED   select /* outlinetest1 */ * from dh_stat where id=771

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
SQL_ID 053nzgm4f6rdr, child number 2
-------------------------------------
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 3
-------------------------------------
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
已选择84行。可以看到，虽然OUTLINE已经被使用，但是并不是我们预期的那样。想要得到我们预期，通过全表扫描来实现，我在测试中是通过下面的方法实现 SQL> alter session set use_stored_outlines=dd; 会话已更改。 SQL> exec dbms_outln.clear_used(name=>'SYS_OUTLINE_14061209594622403'); 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 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创建为一个私有的类别，等验证完毕且符合预期后，再正式发布出来，这样也不会需要修改数据库当前已有参数。

本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。

返回首页

暂无相关文章

评论暂时关闭