oracle的存储提纲简介

原创: 2016-06-07 16:36:47 875浏览
从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))