Oracle Outline总结

一、基本概述

Oracle Outline,中文也称为存储大纲,是最早的基于提示来控制SQL执行计划的机制,也是9i以及之前版本唯一可以用来稳定和控制SQL执行计划的工具。

outline是一个hints(提示)的集合,更具体的讲,outline可以锁定一个给定SQL的执行计划,保持其执行计划稳定,不管数据库环境如何变更(如统计信息,部分参数等)

注意:

  1. 从10g以后,oracle连续发布了sql profile和sql baseline来实现SQL执行计划的控制,并且outline这个工具基本已经被Oracle废弃并且不在维护,但是不管怎么说,在10g以及11g版本都还是可以使用,而且这个特性也一直使用的很好。
  2. 10g以后建议使用sql profile或者sql baseline
  3. 由于目前outline现在已经很少使用,此文也尽量介绍实用的一部分

二、运行机制

Outline将执行计划的hint集合保存在outline的表中(数据字典)。当执行SQL解析时,Oracle会与outline中的SQL比较,如果该SQL有保存的outline,则通过保存的hint集合生成指定执行计划。

注意:

  1. SQL解析时,使用SQL文本却匹配数据字典outline保存的文本,此处匹配的方式为去掉SQL空格,忽略SQL大小写区别后,进行的比较。
  2. 例如,select * from dual 和SELECT *    FROM dual这两个语句将使用同样的outline。

三、使用场景

  1. 为避免在升级后某些SQL出现严重性能下降而且在短时间内不能优化的情况,我们可以使用outline的功能将原生产库中的sql执行计划实施在新的数据库上。
  2. 为避免SQL的执行计划在统计数据不准确的情况(如未能及时收集表或索引的统计信息)下导致变化从而引起的性能降低。
  3. 为避免容易因为Bind Peeking导致SQL执行计划变差从而引起的性能降低。
  4. 避免大规模分布实施的应用出现数据库版本、配置等区别引起的优化器产生不同的执行计划。
  5. 某些Bug引起优化器生成较差的执行计划。在bug修复前我们可以使用outline来强制SQL的执行计划的正确。
  6. 早期优化器版本从rule转换为cbo模式时,过渡期间用来维护业务稳定(执行计划稳定)

注意

任何一个数据库中,大部分的SQL语句执行计划应该是通过优化器自动生成,并且高效运行,而只有极少部分,需要通过各种工具(outine、sql profile)来锁定执行计划

四、注意事项

  1. outline存在在outln用户中,Outln用户是一个非常重要的系统用户,其重要性跟sys,system一样。在任何情况下都不建议用户删除outln,否则会引起数据库错误。
  2. 优化器通过Outline生成执行计划前提是outline内所有hint都有效的。
  3. 只有设置use_stored_outlines参数后才能启用outline。
  4. 使用字面值的sql的共享程度不高(没有使用绑定变量),Outline针对绑定变量的sql较好。针对使用字面值的sql的情况,需要每条sql都生成outline。
  5. 创建outline需要create any outline  or execute_catelog_role权限 。
  6. 要注意从CBO的角度来看,数据库表和索引的统计信息是随着数据量的变化而不断改变的。固定的执行计划在某些时段并不一定是最优的执行计划。所以outline的使用是要根据具体情况来决定的。
  7. 第一次应用Outline (alter system )这个操作是会产生Library cache pin的,需谨慎。
  8. 10.2.0.4 outline bug 6455659
  9. use_stored_outlines参数重启后失效,需要重新设置
  10. 当outline依赖的对象被删除时,outline并不会自动删除

五、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;

示例:

SQLCREATE or replace OUTLINE  outline_dh_test  FOR CATEGORY
test on select * from dh_stat where id=11;

Outline created.

SQLset linesize 200 pagesize 999

SQLset long 30

SQLset long 50

SQLselect 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

SQLselect 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;

SQLalter system flush shared_pool;

系统已更改。

SQLexec 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

给运行的语句添加一个独特的注释,方便后续查找语句

SQLselect /* outlinetest1 */ * from dh_stat where id=771;

ID NAME                           TYPE

---------- ------------------------------ ---------------

771 APPLY$_CONF_HDLR_COLUMNS_UNQ1  INDEX

SQLselect 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行。

SQLexec DBMS_OUTLN.create_outline(hash_value=>3370343863,child_number => 0,category=>‘TEST‘);

PL/SQL 过程已成功完成。

SQL> col category format a10

SQLselect 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确实已经生成

SQLselect /* outlinetest1 */ * from dh_stat where id=771;

ID NAME                           TYPE

---------- ------------------------------ ---------------

771 APPLY$_CONF_HDLR_COLUMNS_UNQ1  INDEX

SQLselect 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!

SQLselect * 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

SQLalter session set use_stored_outlines=TEST;

会话已更改。

SQLselect /* outlinetest1 */ * from dh_stat where id=771;

ID NAME                           TYPE

---------- ------------------------------ ---------------

771 APPLY$_CONF_HDLR_COLUMNS_UNQ1  INDEX

SQLselect 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确实已经被使用过,通过下面的执行计划,更加可以证实这一点

SQLselect * 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)"

SQLexec dbms_outln.clear_used(name=>‘SYS_OUTLINE_14061209594622403‘);

PL/SQL 过程已成功完成。

---通过上面的过程,将outline的使用记录清理掉,USED状态再次转变为UNSED,方便我们测试

SQLselect 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

SQLselect /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=771;

ID NAME                           TYPE

---------- ------------------------------ ---------------

771 APPLY$_CONF_HDLR_COLUMNS_UNQ1  INDEX

SQLselect 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

SQLalter session set use_stored_outlines=true;

会话已更改。

SQLselect * 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行。

SQLexec DBMS_OUTLN.create_outline(hash_value=>1000483106,child_number => 0,category=>‘TEST‘);

PL/SQL 过程已成功完成。

SQLselect 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,确实已经成功

SQLalter session set use_stored_outlines=TEST;

会话已更改。

SQLselect /* outlinetest1 */ * from dh_stat where id=771;

ID NAME                           TYPE

---------- ------------------------------ ---------------

771 APPLY$_CONF_HDLR_COLUMNS_UNQ1  INDEX

SQLselect /* outlinetest2 */ /*+ full(dh_stat) */ * from dh_stat where id=771;

ID NAME                           TYPE

---------- ------------------------------ ---------------

771 APPLY$_CONF_HDLR_COLUMNS_UNQ1  INDEX

SQLselect 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都已经标记为已经使用过

SQLselect * 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行。

SQLselect * 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行。

SQLalter session set use_stored_outlines=TRUE;

会话已更改。

通过如下方式,我们调换两个outline里面的hints

SQLUPDATE 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行。

SQLcommit;

提交完成。

SQL> col hint_text format a50

SQLselect hint#,hint_text from outln.ol$hints a where ol_name=‘SYS_OUTLINE_14061209594622403‘;

HINT# HINT_TEXT

---------- --------------------------------------------------

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里面的执行计划已经调换

SQLselect /* outlinetest1 */ * from dh_stat where id=771;

ID NAME                           TYPE

---------- ------------------------------ ---------------

771 APPLY$_CONF_HDLR_COLUMNS_UNQ1  INDEX

SQLselect * 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

SQLalter session set use_stored_outlines=TEST;

会话已更改。

SQLexec dbms_outln.clear_used(name=>‘SYS_OUTLINE_14061209594622403‘);

PL/SQL 过程已成功完成。

SQLselect 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

SQLselect /* outlinetest1 */ * from dh_stat where id=771;

ID NAME                           TYPE

---------- ------------------------------ ---------------

771 APPLY$_CONF_HDLR_COLUMNS_UNQ1  INDEX

SQLselect 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

SQLselect * 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创建为一个私有的类别,等验证完毕且符合预期后,再正式发布出来,这样也不会需要修改数据库当前已有参数。

Oracle Outline总结,布布扣,bubuko.com

时间: 06-11

Oracle Outline总结的相关文章

【转】Oracle Outline的使用及注意事项

概述 Oracle Outline是用来保持SQL执行计划(execution plan)的一个工具.我们可以通过outline工具防止SQL执行计划在数据库环境变更(如统计信息,部分参数等)而引起变化. Outline的主要使用在以下情况: 1. 为避免在升级后某些sql出现严重性能下降而且在短时间内不能优化的情况, 我们可以使用outline的功能将原生产库中的sql执行计划实施在新的数据库上. 2. 为避免SQL的执行计划在统计数据不准确的情况(如未能及时收集表或索引的统计信息)下导致变化

查看Oracle执行计划的几种常用方法-系列1

SQL的执行计划实际代表了目标SQL在Oracle数据库内部的具体执行步骤,作为调优,只有知道了优化器选择的执行计划是否为当前情形下最优的执行计划,才能够知道下一步往什么方向. 执行计划的定义:执行目标SQL的所有步骤的组合. 我们首先列出查看执行计划的一些常用方法: 1. explain plan命令 PL/SQL Developer中通过快捷键F5就可以查看目标SQL的执行计划了.但其实按下F5后,实际后台调用的就是explain plan命令,相当于封装了该命令. explain plan

Oracle绑定变量窥探

随着具体输入值的不同,SQL的where条件的可选择率(Selectivity)和结果集的行数(Cardinality)可能会随之发生变化,而Selectivity和Cardinality的值会直接影响CBO对于相关执行步骤成本值的估算,进而影响CBO对SQL执行计划的选择.这就意味着随着具体输入值的不同,目标SQL执行计划可能会发生变化. 对于不使用绑定变量的SQL而言,具体输入值一量发生了变化,目标SQL的SQL文本就会随之发生变化,这样Oracle就能很容易地计算出对应Selectivit

ORACLE之常用FAQ V1.0

[B]第一部分.SQL&PL/SQL[/B][Q]怎么样查询特殊字符,如通配符%与_[A]select * from table where name like 'A\_%' escape '\' [Q]如何插入单引号到数据库表中[A]可以用ASCII码处理,其它特殊字符如&也一样,如 insert into t values('i'||chr(39)||'m');  -- chr(39)代表字符'或者用两个单引号表示一个or insert into t values('I''m');  

【问底】陈焕生:深入理解Oracle 的并行执行

摘要:Oracle并行执行是一种分而治之的方法.执行一个sql 时,分配多个并行进程同时执行数据扫描,连接以及聚合等操作,使用更多的资源,得到更快的sql 响应时间.并行执行是充分利用硬件资源,处理大量数据时的核心技术. Oracle并行执行是一种分而治之的方法.执行一个sql 时,分配多个并行进程同时执行数据扫描,连接以及聚合等操作,使用更多的资源,得到更快的sql 响应时间.并行执行是充分利用硬件资源,处理大量数据时的核心技术. 在本文中,在一个简单的星型模型上,我会使用大量例子和sql m

AIX 6.1 Oracle 10G 数据库GoldenGate实施

安装环境说明: 源端:AIX 6.1 10.190.1.215 目标端:Linux 10.191.1.10 1:源端创建goldengate 表空间. 表空间的要求:最小500m,大点3-5G,设置自动扩展. SQL>  set pagesize 9999 col tablespace_name for a15 col file_name for a45 select tablespace_name ,file_name from dba_data_files; 在源端创建GoldenGate表

Oracle SQL操作计划基线总结(SQL Plan Baseline)

一.基础概念 Oracle 11g開始,提供了一种新的固定运行计划的方法,即SQL plan baseline,中文名SQL运行计划基线(简称基线),能够觉得是OUTLINE(大纲)或者SQL PROFILE的改进版本号.基本上它的主要作用能够归纳为例如以下两个: 1.稳定给定SQL语句的运行计划.防止运行环境或对象统计信息等等因子的改变对SQL语句的运行计划产生影响! 2.降低数据库中出现SQL语句性能退化的概率.理论上不同意一条语句切换到一个比已经运行过的运行计划慢非常多的新的运行计划上!

oracle数据库性能影响之Sql parse

1,Sql parse的种类 Sql parse又通常分为硬解析和软解析,当sql第一次执行的时候,会发生硬解析,之后的执行如果在shared pool中能找到就是软解析.因此,为提高数据性能,尽可能的让每次执行的SQL在shared pool找到. 2,SQL在哪些情况下会发送硬解析? 1)统计信息改变  2)Sql中的表上有做ddl操作,包括grant和revoke. 3)执行计划被踢出shared pool 4)开启了trace 5)绑定变量长度变化 6)启用outlin

Oracle性能诊断艺术-读书笔记

create table test0605 as select * from dba_objects; select t1.owner,t1.object_name,t1.object_id from test0605 t1 where t1.object_id=3344; select t1.SQL_ID,t1.HASH_VALUE,t1.SQL_TEXT,t1.SQL_FULLTEXT,to_char(t1.LAST_ACTIVE_TIME,'yyyy-mm-dd hh24:mi:ss')