欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页  >  数据库

oracle的存储提纲简介

程序员文章站 2023-12-29 13:39:04
...

从oracle 10g开始，oracle推出了存储提纲用来固定sql语句的执行计划，在oracle 11g后存储提纲被sql计划基线（sql plan baseline）取而代之，sql plan baseline也和存储提纲一样被用来提供稳定的执行计划，简单来讲也是固化sql语句执行计划的，而同样固化执行

从oracle 10g开始，oracle推出了存储提纲用来固定sql语句的执行计划，在oracle 11g后存储提纲被sql计划基线（sql plan baseline）取而代之，sql plan baseline也和存储提纲一样被用来提供稳定的执行计划，简单来讲也是固化sql语句执行计划的，而同样固化执行计划的还有hint、sql profile、存储提纲等，下面小鱼简单来对存储提纲和sql计划基线做一定的介绍。

Oracle stored outline（存储提纲）和sql计划基线最主要的作用就是用来保持固定sql语句的执行计划，让sql语句的执行计划在数据库环境变更（统计信息、优化器参数、表结果变更等）后还能保持原来的执行计划。

比如我们升降级数据库会由于优化器版本的不同造成执行计划差异、统计信息不准确导致执行计划可能出现波动、优化器的bug等造成执行计划不合理等，我们都可以采取oracle的存储提纲和sql计划基线来固化sql的执行计划。

下面来看看oracle的stored outline是如何工作的：
存储提纲是一个和sql语句相关的对象，存储在数据字典里的，优化器正是根据存储提纲关联的执行计划来固化sql语句执行计划。

首先对sql语句进行标准化，移除空白并转换非字段值的部分为大写，为标准化的sql语句计算一个签名。注意该签名是一个hash value，然后当发现相同签名的存储提纲时，就会去检查当前执行的sql语句和存储提纲记录的sql语句是否相同，如果相同则使用存储提纲内的执行计划。

关于创建存储提纲有两种方法，分别是自动创建和手动创建，自动创建需要结合初始化参数create_stored_outlines，手动创建可以用create outline或者dbms_outln.create_outline来创建。

这里小鱼主要来演示下手动创建存储提纲。

SQL> create table t_out01 as select * from dba_objects;

Table created.
SQL> create or replace outline outline01 for category outline on select object_name from t_out01 where object_id=1099;

Outline created.
SQL> select name,category,used,enabled,sql_text from user_outlines;

NAME CATEGORY USED ENABLED
------------------------------ ------------------------------ ------ --------
SQL_TEXT
--------------------------------------------------------------------------------
OUTLINE01 OUTLINE UNUSED ENABLED
select object_name from t_out01 where object_id=1099

SQL> select hint from user_outline_hints where name='OUTLINE01';

HINT
--------------------------------------------------------------------------------
FULL(@"SEL$1" "T_OUT01"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPT_PARAM('_push_join_union_view' 'false')
OPT_PARAM('_push_join_predicate' 'false')
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS

7 rows selected.

SQL> alter system set use_stored_outlines=OUTLINE;

System altered.

SQL> select object_name from t_out01 where object_id=1099;

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
V$PARAMETER

SQL> set linesize 140
SQL> select * from table(dbms_xplan.display_cursor(null,null));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID a5fdfsf61dk3f, child number 0
-------------------------------------
select object_name from t_out01 where object_id=1099

Plan hash value: 43222384

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 234 (100)| |
|* 1 | TABLE ACCESS FULL| T_OUT01 | 1 | 26 | 234 (1)| 00:00:03 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_ID"=1099)

18 rows selected.

SQL> create index ind_obj_id on t_out01(object_id);

Index created.
SQL> select object_name from t_out01 where object_id=1099;

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
V$PARAMETER

Execution Plan
----------------------------------------------------------
Plan hash value: 2483087502

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_OUT01 | 1 | 26 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_OBJ_ID | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID"=1099)

这个数据库版本是linux的10.2.0.4.0的版本，又尝试了windows的一个10.2.0.4.0版本的数据库，结果还是如此，就是创建的outline优化器并不去使用它，刚开始小鱼觉得可能是否和bug有关的，于是换了一个11.2.0.1的库还是如此，群里的一个热情的朋友测试存储提纲也没问题，人品真的很有点差啊，不过对于这类问题我们可不能就直接扔到一边去，小鱼一直坚信能遇见就是幸运的。

对于上述测试小鱼均都是使用的sys用户测试的，我们换一个用户来试试看
SQL> show user;
USER is "XIAOYU"
SQL> create table t_out01 as select * from dba_objects;

Table created.
SQL> create or replace outline outline1 for category cate on select object_name
from t_out01 where object_id=1009;

Outline created.
SQL> set autotrace on exp
SQL> set linesize 140;
SQL> select object_name from t_out01 where object_id=1009;

OBJECT_NAME
--------------------------------------------------------------------------------
------------------------------------------------
V$LOG

Execution Plan
----------------------------------------------------------
Plan hash value: 43222384

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 632 | 158 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T_OUT01 | 8 | 632 | 158 (1)| 00:00:02 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_ID"=1009)

Note
-----
- dynamic sampling used for this statement

SQL> set autotrace off
SQL> select name,category,used,sql_text from user_outlines;

NAME CATEGORY USED
------------------------------ ------------------------------ ------
SQL_TEXT
--------------------------------------------------------------------------------

OUTLINE1 CATE UNUSED
select object_name from t_out01 where object_id=1009

SQL> alter session set use_stored_outlines=cate;

Session altered.
SQL> set autotrace on exp
SQL> select object_name from t_out01 where object_id=1009;

OBJECT_NAME
--------------------------------------------------------------------------------
------------------------------------------------
V$LOG

Execution Plan
----------------------------------------------------------
Plan hash value: 43222384

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 581 | 45899 | 158 (1)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T_OUT01 | 581 | 45899 | 158 (1)| 00:00:02 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_ID"=1009)

Note
-----
- outline "OUTLINE1" used for this statement

看出这里的note表示已经使用了outline OUTLINE1，下面我们建立索引
SQL> create index indid on t_out01(object_id);

Index created.
SQL> select object_name from t_out01 where object_id=1009;

OBJECT_NAME
--------------------------------------------------------------------------------
------------------------------------------------
V$LOG

Execution Plan
----------------------------------------------------------
Plan hash value: 1679505401

--------------------------------------------------------------------------------
-------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim
e |
--------------------------------------------------------------------------------
-------
| 0 | SELECT STATEMENT | | 581 | 45899 | 4 (0)| 00:
00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_OUT01 | 581 | 45899 | 4 (0)| 00:
00:01 |
|* 2 | INDEX RANGE SCAN | INDID | 232 | | 1 (0)| 00:
00:01 |
--------------------------------------------------------------------------------
-------
Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID"=1009)

Note
-----
- outline "OUTLINE1" used for this statement
但是建议索引后，执行计划好像还是变化了，从之前的全表扫描变为了索引扫描。
SQL> set autotrace off
SQL> select name,category,used,sql_text from user_outlines;

NAME CATEGORY USED
------------------------------ ------------------------------ ------
SQL_TEXT
--------------------------------------------------------------------------------

OUTLINE1 CATE USED
select object_name from t_out01 where object_id=1009

SQL> select hint from dba_outline_hints where name='OUTLINE1';

HINT
--------------------------------------------------------------------------------
FULL(@"SEL$1" "T_OUT01"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS

防止set autotrace的执行计划不准，我们用dbms_xplan.display_cursor看看真实的执行计划
SQL> select * from table(dbms_xplan.display_cursor(null,null));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
------------------------------------------------------------
SQL_ID 7k0h3uvq0xufh, child number 0
-------------------------------------
select object_name from t_out01 where object_id=1009

Plan hash value: 1679505401

--------------------------------------------------------------------------------
-------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim
e |
--------------------------------------------------------------------------------
-------
| 0 | SELECT STATEMENT | | | | 4 (100)|
|
| 1 | TABLE ACCESS BY INDEX ROWID| T_OUT01 | 581 | 45899 | 4 (0)| 00:
00:01 |
|* 2 | INDEX RANGE SCAN | INDID | 232 | | 1 (0)| 00:
00:01 |
--------------------------------------------------------------------------------
-------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID"=1009)

Note
-----
- outline "OUTLINE1" used for this statement
Note是显示使用了outline outline1，但是执行计划依然还是索引扫描，而我们查看outline outline1对应的hint中还是原来的全表扫描的执行计划，对于这个问题问了一些朋友，都没有合适的答案。

首先这里我们确定了stored outline对于sys用户无效，这个还没有找到合适的资料和文档来说明，第二个是10.2.0.4版本的数据库，虽然执行计划提示使用了存储提纲，但是执行计划确没有使用stored outline的执行计划，找了半天在mos上发现了一篇关于stored outline的bug ，有兴趣的朋友可以试试打patch看看！
Bug 6455659 - Stored outlines do not work in multibyte DB

而在oracle性能诊断艺术那本书中也提到了即使是使用了存储提纲，执行计划依然可能变化，也正是这个原因，存储提纲很少在生产环境中大批量的使用。

小鱼再找来一个11.2.0.1的数据库来进行存储提纲的测试：
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> create table t as select * from dba_objects;

Table created.

SQL> create or replace outline outline01 for category cate on select object_name from t where object_id=1090;

Outline created.

SQL> show user;
USER is "TEST"
SQL> set autotrace on exp
SQL> set linesize 140
SQL> select object_name from t where object_id=1090;

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
SYS_LOB0000001087C00012$$

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 948 | 299 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 12 | 948 | 299 (1)| 00:00:04 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_ID"=1090)

Note
-----
- dynamic sampling used for this statement (level=2)

SQL> create index ind_id on t(object_id);

Index created.

SQL> select object_name from t where object_id=1090;

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
SYS_LOB0000001087C00012$$

Execution Plan
----------------------------------------------------------
Plan hash value: 4043158466

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 79 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_ID | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID"=1090)

Note
-----
- dynamic sampling used for this statement (level=2)

SQL> alter system set use_stored_outlines=cate;

System altered.

SQL> select object_name from t where object_id=1090;

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
SYS_LOB0000001087C00012$$

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 895 | 70705 | 300 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 895 | 70705 | 300 (1)| 00:00:04 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_ID"=1090)

Note
-----
- outline "OUTLINE01" used for this statement

SQL> alter session set use_stored_outlines=false;

Session altered.

SQL> select object_name from t where object_id=1090;

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
SYS_LOB0000001087C00012$$

Execution Plan
----------------------------------------------------------
Plan hash value: 4043158466

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 79 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_ID | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID"=1090)

Note
-----
- dynamic sampling used for this statement (level=2)

此时我们发现同样的测试在11.2.0.1上面则优化器使用了outline记录的hint 全表扫描，小鱼又找了几个10.2.0.4的库都是上述的情况，而这个测试更加给我们一个清晰的认识，就是一般而言我们不要在生产环境中大量使用存储提纲，即使设置了优化器也可能稀里糊涂的不去用outline的执行计划。

还有一种手动创建存储提纲的办法就是利用dbms_outln.create_outline 来创建存储提纲，dbms_outln包还有很多关于outline的procedure和function，有兴趣的同学可以倒腾下

SQL> desc dbms_outln;
PROCEDURE CREATE_OUTLINE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
HASH_VALUE NUMBER IN
CHILD_NUMBER NUMBER IN
CATEGORY VARCHAR2 IN DEFAULT

SQL> drop table t;

Table dropped.

SQL> create table t as select * from dba_objects;

Table created.

SQL> select object_name,object_type from t where object_id=1099;

OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-------------------
LOGMNR_SESSION_PK
INDEX

SQL> select hash_value from v$sql where sql_text like 'select object_name,object_type from t where object_id=1099%';

HASH_VALUE
----------
300252238

SQL> declare
2 begin
3 dbms_outln.create_outline(
4 hash_value=>300252238,
5 child_number=>0);
6 end;
7 /
declare
*
ERROR at line 1:
ORA-16953: Type of SQL statement not supported.
ORA-06512: at "SYS.OUTLN_PKG", line 324
ORA-06512: at "SYS.OUTLN_PKG", line 368
ORA-06512: at line 3

这里又报出错误了，查了半天资料没有发现一个合理的说法，现在测试的版本是11.2.0.1,而在别的版本中dbms_outln.create_outline是可以创建cache cursor的stored outline的，小鱼个人推断这个又可能是个bug，测试这个运气确实有点背

上面介绍了手动收集，下面简单来讲下自动收集，自动收集outline需要一个很重要的参数就是create_stored_outlines

SQL> create table t_auto01 as select * from dba_objects;

Table created.

SQL> alter session set create_stored_outlines=true;

Session altered.

SQL> select object_name from t_auto01 where object_id=1000;

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
EXPACT$

SQL> select object_name from t_auto01 where object_id=1001;

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
NOEXP$

SQL> alter session set create_stored_outlines=false;

Session altered.

SQL> create index ind_auto_id on t_auto01(object_id);

Index created.

SQL> select name,category,used,sql_text from user_outlines;

NAME CATEGORY USED
------------------------------ ------------------------------ ------
SQL_TEXT
--------------------------------------------------------------------------------
SYS_OUTLINE_14061917391589506 DEFAULT UNUSED
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB

SYS_OUTLINE_14061917391589305 DEFAULT UNUSED
select object_name from t_auto01 where object_id=1000

SYS_OUTLINE_14061917393895607 DEFAULT UNUSED
select object_name from t_auto01 where object_id=1001

这里设置create_stored_outlines=true后，默认这个session就开始收集sql语句并创建对应的outlines
SQL> set autotrace on exp
SQL> select object_name from t_auto01 where object_id=1000;

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
EXPACT$

Execution Plan
----------------------------------------------------------
Plan hash value: 2314754062

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_AUTO01 | 1 | 79 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_AUTO_ID | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID"=1000)

Note
-----
- dynamic sampling used for this statement (level=2)

SQL> alter session set use_stored_outlines=true;

Session altered.

SQL> select object_name from t_auto01 where object_id=1000;

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
EXPACT$

Execution Plan
----------------------------------------------------------
Plan hash value: 795571617

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 895 | 70705 | 300 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T_AUTO01 | 895 | 70705 | 300 (1)| 00:00:04 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_ID"=1000)

Note
-----
- outline "SYS_OUTLINE_14061917391589305" used for this statement

SQL> select object_name from t_auto01 where object_id=1001;

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
NOEXP$

Execution Plan
----------------------------------------------------------
Plan hash value: 795571617

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 895 | 70705 | 300 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T_AUTO01 | 895 | 70705 | 300 (1)| 00:00:04 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_ID"=1001)

Note
-----
- outline "SYS_OUTLINE_14061917393895607" used for this statement

SQL> select object_name from t_auto01 where object_id=1002;

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
EXPPKGOBJ$

Execution Plan
----------------------------------------------------------
Plan hash value: 2314754062

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_AUTO01 | 1 | 79 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_AUTO_ID | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID"=1002)

Note
-----
- dynamic sampling used for this statement (level=2)

这里最后一个sql语句由于sql_text不相同，所以没有配对的outline hint可供使用。

而如果是针对一类sql语句，我们不太可能每个去创建outline，可以针对该sql改成bind value然后直接对bind后的sql创建outline
SQL> variable v_id number;
SQL> exec :v_id:=10000;

PL/SQL procedure successfully completed.

SQL> create outline outline02 on select object_name,object_type from t where object_id=:v_id;

Outline created.
SQL> select name,category,used,sql_text from user_outlines where name='OUTLINE02';

NAME CATEGORY USED
------------------------------ ------------------------------ ------
SQL_TEXT
--------------------------------------------------------------------------------
OUTLINE02 DEFAULT UNUSED
select object_name,object_type from t where object_id=:v_id

下面创建了object_id上的索引
SQL> create index ind_id on t(object_id);

Index created.
SQL> alter session set use_stored_outlines=false;

Session altered.

SQL> select object_name,object_type from t where object_id=:v_id;

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
OBJECT_TYPE
-------------------
KU$_EQNTABLE_BYTES_ALLOC_VIEW
VIEW

Execution Plan
----------------------------------------------------------
Plan hash value: 4043158466

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 855 | 76950 | 6 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 855 | 76950 | 6 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_ID | 342 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID"=TO_NUMBER(:V_ID))

Note
-----
- dynamic sampling used for this statement (level=2)

当设置use_stored_outlines=true后，优化器采取的outline中的执行计划。
SQL> alter session set use_stored_outlines=true;

Session altered.

SQL> select object_name,object_type from t where object_id=:v_id;

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
OBJECT_TYPE
-------------------
KU$_EQNTABLE_BYTES_ALLOC_VIEW
VIEW

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 895 | 80550 | 300 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 895 | 80550 | 300 (1)| 00:00:04 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_ID"=TO_NUMBER(:V_ID))

Note
-----
- outline "OUTLINE02" used for this statement

原文地址：oracle的存储提纲简介, 感谢原作者分享。

相关标签： oracle 存储 提纲 简介 oracle 10g 开始

上一篇：

下一篇：

推荐阅读