月份：2016年6月

Linux操作系统，oracle 11.2.0.4 启动实例时出现如下错误:

SQL> startup nomount pfile=/u03/app/oracle/11.2.0/db/dbs/initcssb.ora
ORA-00845: MEMORY_TARGET not supported on this system

查看错误帮助信息

[oracle11@oracle11g dbs]$ oerr ora 845
00845, 00000, "MEMORY_TARGET not supported on this system"
// *Cause: The MEMORY_TARGET parameter was not supported on this operating system or /dev/shm was not sized correctly on Linux.
// *Action: Refer to documentation for a list of supported operating systems. Or, size /dev/shm to be at least the SGA_MAX_SIZE on each Oracle instance running on the system.

错误原因是这个操作系统不支持MEMORY_TARGET参数或/dev/shm在Linux上的大小不正确造成的，这是该操作系统上的第二个实例，第一个实例设置了MEMORY_TARGET参数，所以并不是不支持这个参数，原因就只有/dev/shm大小不正确了，解决方法是要将/dev/shm的最小值设置为操作系统上运行实例SGA_MAX_SIZE所设置的大小。/dev/shm/是linux下一个目录，/dev/shm目录不在磁盘上，而是在内存里，因此使用linux /dev/shm/的效率非常高，直接写进内存。
tmpfs有以下特点：
1.tmpfs 是一个文件系统，而不是块设备；您只是安装它，它就可以使用了。
2.动态文件系统的大小。
3.tmpfs 的另一个主要的好处是它闪电般的速度。因为典型的 tmpfs 文件系统会完全驻留在 RAM 中，读写几乎可以是瞬间的。
4.tmpfs 数据在重新启动之后不会保留，因为虚拟内存本质上就是易失的。所以有必要做一些脚本做诸如加载、绑定的操作。

linux下/dev/shm的容量默认最大为内存的一半大小，使用df -h命令可以看到。但它并不会真正的占用这块内存，如果/dev/shm/下没有任何文件，它占用的内存实际上就是0字节；如果它最大为1G，里头放有100M文件，那剩余的900M仍然可为其它应用程序所使用，但它所占用的100M内存，是绝不会被系统回收重新划分的。

linux /dev/shm容量(大小)是可以调整，在有些情况下(如oracle数据库)默认的最大一半内存不够用，并且默认的inode数量很低一般都要调高些，这时可以用mount命令来管理它。
mount -o size=1500M -o nr_inodes=1000000 -o noatime,nodiratime -o remount /dev/shm
在2G的机器上，将最大容量调到1.5G，并且inode数量调到1000000，这意味着大致可存入最多一百万个小文件通过/etc/fstab文件来修改/dev/shm的容量(增加size选项即可),修改后，重新挂载即可。

这里该实例的SGA_MAX_SIZE为1G,下面的命令查看/dev/shm的大小。

[root@oracle11g ~]# df -lh
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda1              23G   20G  1.6G  93% /
/dev/sdb1             9.9G  5.8G  3.6G  62% /u02
tmpfs                 2G    1.3M  0.7G  65% /dev/shm

从上面结果可以看到/dev/shm可用大小只有0.7G,执行下面的命令来进行修改。

[root@oracle11g ~]# vi /etc/fstab
LABEL=/                 /                       ext3    defaults        1 1
/dev/sdb1               /u02                    ext3    defaults        1 2
tmpfs                   /dev/shm                tmpfs   defaults,size=4G        0 0
devpts                  /dev/pts                devpts  gid=5,mode=620  0 0
sysfs                   /sys                    sysfs   defaults        0 0
proc                    /proc                   proc    defaults        0 0
LABEL=SWAP-sda2         swap                    swap    defaults        0 0

"/etc/fstab" 7L, 540C written

卸载/dev/shm，但/dev/shm正被访问

[root@oracle11g ~]#  umount /dev/shm
umount: /dev/shm: device is busy
umount: /dev/shm: device is busy

用fuser处理,fuser命令,-k:kill processes accessing the named file(杀死所有正在访问指定文件的进程),-m 表示指定文件所在的文件系统或者块设备（处于 mount 状态）。所有访问该文件系统的进程都被列出。

[root@oracle11g ~]# fuser -km /dev/shm
/dev/shm:             3152m  3154m  3156m  3160m  3162m  3164m  3166m  3168m  3170m  3172m  3174m  3176m  3178m  3180m  3182m  3184m  3186m  3193m  3195m  3197m  3199m  3201m  3236m  3248m  3250m  3256m  3292m  4366m
[root@oracle11g ~]#  umount /dev/shm
[root@oracle11g ~]# mount /dev/shm
[root@oracle11g ~]# df -lh
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda1              23G   20G  1.6G  93% /
/dev/sdb1             9.9G  5.8G  3.6G  62% /u02
tmpfs                 4.0G     0  4.0G   0% /dev/shm

再重新启动实例
SQL> startup nomount pfile=/u03/app/oracle/11.2.0/db/dbs/initcssb.ora
ORACLE instance started.

Total System Global Area 1334786560 bytes
Fixed Size 1364480 bytes
Variable Size 171970048 bytes
Database Buffers 1155189248 bytes
Redo Buffers 6262784 bytes

小结:Oracle 11g的AMM内存管理模式就是使用/dev/shm，所以有时候修改MEMORY_TARGET或者MEMORY_MAX_TARGET会出现ORA-00845的错误，在安装配置实例内存时为了避免出现这个故障可以对Linux系统中的/dev/shm进行调整，让其可用大小至少等于实例的
sga_max_size。

存储概要(stored outline) 是固定执行计划的一种传统技术。在Oracle 11g以前要固定执行计划只能使用outline,Oracle11g中引入了SPM来固定执行计划，创建stored outline需要有create any outline权限，为了管理还需要有对dba_outlines的查询权限。
创建stored outline

在会话级outline
ALTER SESSION SET CREATE_STORED_OUTLINES = TRUE | FALSE |
如果将create_stored_outlines设置为true，当outlines的目录名设置为default时，Oracle会为该会话所执行的SQL语句创建outlines。

SQL> var x varchar2(20)
SQL> exec :x:='Kabab'

PL/SQL procedure successfully completed.

SQL> alter session set create_stored_outlines=true;

Session altered.

SQL> select * from t1 where t_meal=:x;

      T_ID T_MEAL
---------- --------------------
     79999 Kabab

SQL> select * from dba_outlines;
 
NAME                           OWNER                          CATEGORY                       USED   TIMESTAMP   VERSION                                                 

         SQL_TEXT                                                                         SIGNATURE                        COMPATIBLE   ENABLED  FORMAT
------------------------------ ------------------------------ ------------------------------ ------ ----------- 

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

-------------------------------- ------------ -------- ------
SYS_OUTLINE_16060109390284501  JY                             DEFAULT                        UNUSED 2016/6/1 9: 10.2.0.5.0                                              

         select * from t1 where t_meal=:x                                                 7DE4EC0CD8B1DA6C96B213F5D6DC349C COMPATIBLE   ENABLED  NORMAL

可以看到虽然创建了存储概要，但是并没有使用，因为USED的值为UNUSED

SQL> select * from dba_outline_hints where owner='JY' and name='SYS_OUTLINE_16060109390284501';
 
NAME                           OWNER                                NODE      STAGE   JOIN_POS HINT
------------------------------ ------------------------------ ---------- ---------- ---------- 

--------------------------------------------------------------------------------
SYS_OUTLINE_16060109390284501  JY                                      1          1          1 INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."T_MEAL"))
SYS_OUTLINE_16060109390284501  JY                                      1          1          0 OUTLINE_LEAF(@"SEL$1")
SYS_OUTLINE_16060109390284501  JY                                      1          1          0 ALL_ROWS
SYS_OUTLINE_16060109390284501  JY                                      1          1          0 OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
SYS_OUTLINE_16060109390284501  JY                                      1          1          0 IGNORE_OPTIM_EMBEDDED_HINTS


SQL> alter session set use_stored_outlines=true;

Session altered.

SQL> var x varchar2(20)
SQL> exec :x:='Pasta'

PL/SQL procedure successfully completed.

SQL> select * from t1 where t_meal=:x;

      T_ID T_MEAL
---------- --------------------
     80000 Pasta

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  7runhd24kgqsf, child number 0
-------------------------------------
select * from t1 where t_meal=:x

Plan hash value: 141743202

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

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |               |       |       |     2 (100)|          |

|   1 |  TABLE ACCESS BY INDEX ROWID| T1            |     1 |    11 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_T1_T_MEAL |     1 |       |     1   (0)| 00:00:01 |

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


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

   2 - access("T_MEAL"=:X)

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


23 rows selected.

从Note部分的outline “SYS_OUTLINE_16060109390284501” used for this statement可以确认语句使用了创建的outline

SQL> select * from dba_outlines;
 
NAME                           OWNER                          CATEGORY                       USED   TIMESTAMP   VERSION                                                 

         SQL_TEXT                                                                         SIGNATURE                        COMPATIBLE   ENABLED  FORMAT
------------------------------ ------------------------------ ------------------------------ ------ ----------- 

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

-------------------------------- ------------ -------- ------
SYS_OUTLINE_16060109390284501  JY                             DEFAULT                        USED   2016/6/1 9: 10.2.0.5.0                                              

         select * from t1 where t_meal=:x                                                 7DE4EC0CD8B1DA6C96B213F5D6DC349C COMPATIBLE   ENABLED  NORMAL

从dba_outlines.USED也可以看到现在变为了USED

删除outline
先将outline设置为UNUSED

SQL> exec dbms_outln.clear_used(name =>'SYS_OUTLINE_16060109390284501');
 
PL/SQL procedure successfully completed
 
SQL> select * from dba_outlines;
 
NAME                           OWNER                          CATEGORY                       USED   TIMESTAMP   VERSION                                                 

         SQL_TEXT                                                                         SIGNATURE                        COMPATIBLE   ENABLED  FORMAT
------------------------------ ------------------------------ ------------------------------ ------ ----------- 

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

-------------------------------- ------------ -------- ------
SYS_OUTLINE_16060109390284501  JY                             DEFAULT                        UNUSED 2016/6/1 9: 10.2.0.5.0                                              

         select * from t1 where t_meal=:x                                                 7DE4EC0CD8B1DA6C96B213F5D6DC349C COMPATIBLE   ENABLED  NORMAL

再删除

SQL> exec dbms_outln.drop_unused;
 
PL/SQL procedure successfully completed
 
SQL> select * from dba_outlines;
 
NAME                           OWNER                          CATEGORY                       USED   TIMESTAMP   VERSION                                                 

         SQL_TEXT                                                                         SIGNATURE                        COMPATIBLE   ENABLED  FORMAT
------------------------------ ------------------------------ ------------------------------ ------ ----------- 

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

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

在系统级创建outline

ALTER SYSTEM SET CREATE_STORED_OUTLINES = TRUE | FALSE | [NOOVERRIDE]

当设置为true时，Oracle将会对系统中执行的每个查询自动创建与存储outline。这些outline存储在default目录中。如果特定的查询在default目录中有了一个outline后，那么这个outline将仍然会被保留并且不会创建新的outline。当设置为nooverride时，指定系统级的设置是滞将会覆盖会话级的设置。

为指定的SQL语句创建outline
下面的命令为指定的SQL语句创建outline
CREATE OUTLINE [ for category ] ON select …. ;

要想使用所创建的outline，那么执行的SQL语句的SQL TEXT必须与创建命令所使用的SQL TEXT相同。

SQL> create outline outline_test for category outline_test on select * from t1 where t_meal=:x;

Outline created.

SQL> select * from dba_outlines;
 
NAME                           OWNER                          CATEGORY                       USED   TIMESTAMP   VERSION                                                 

         SQL_TEXT                                                                         SIGNATURE                        COMPATIBLE   ENABLED  FORMAT
------------------------------ ------------------------------ ------------------------------ ------ ----------- 

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

-------------------------------- ------------ -------- ------
OUTLINE_TEST                   JY                             OUTLINE_TEST                   UNUSED 2016/6/1 11 10.2.0.5.0                                              

         select * from t1 where t_meal=:x                                                 7DE4EC0CD8B1DA6C96B213F5D6DC349C COMPATIBLE   ENABLED  NORMAL
 
SQL> select * from dba_outline_hints where owner='JY' and name='OUTLINE_TEST';
 
NAME                           OWNER                                NODE      STAGE   JOIN_POS HINT
------------------------------ ------------------------------ ---------- ---------- ---------- 

--------------------------------------------------------------------------------
OUTLINE_TEST                   JY                                      1          1          1 FULL(@"SEL$1" "T1"@"SEL$1")
OUTLINE_TEST                   JY                                      1          1          0 OUTLINE_LEAF(@"SEL$1")
OUTLINE_TEST                   JY                                      1          1          0 ALL_ROWS
OUTLINE_TEST                   JY                                      1          1          0 OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
OUTLINE_TEST                   JY                                      1          1          0 IGNORE_OPTIM_EMBEDDED_HINTS




SQL> alter session set use_stored_outlines=OUTLINE_TEST;

Session altered.

SQL> var x varchar2(20)
SQL> exec :x:='Pasta'

PL/SQL procedure successfully completed.

SQL> select * from t1 where t_meal=:x;

      T_ID T_MEAL
---------- --------------------
     80000 Pasta

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  7runhd24kgqsf, child number 0
-------------------------------------
select * from t1 where t_meal=:x

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    47 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    11 |    47   (5)| 00:00:01 |

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

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

   1 - filter("T_MEAL"=:X)

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


22 rows selected.

从Note部分，可以看到outline “OUTLINE_TEST” used for this statement，说明使用了创建的outline。

当不使用outline时，优化器选择使用索引范围扫描，而不是使用outline时的全表扫描

SQL> alter session set use_stored_outlines=false;

Session altered.

SQL> select * from t1 where t_meal=:x;

      T_ID T_MEAL
---------- --------------------
     80000 Pasta

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  7runhd24kgqsf, child number 1
-------------------------------------
select * from t1 where t_meal=:x

Plan hash value: 141743202

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

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |               |       |       |     2 (100)|          |

|   1 |  TABLE ACCESS BY INDEX ROWID| T1            |     1 |    11 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_T1_T_MEAL |     1 |       |     1   (0)| 00:00:01 |

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


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

   2 - access("T_MEAL"=:X)


19 rows selected.

outline的使用
outline会覆盖所有其它优化器设置。可以在会话级与系统级进行设置
ALTER SESSION SET USE_STORED_OUTLINES = TRUE | FALSE |
ALTER SYSTEM SET USE_STORED_OUTLINES = TRUE | FALSE |
如果use_stored_outlines设置为true,那么将会使用default目录中的outline
如果use)stored_outlines设置为目录，那么将会使用这个目录中的outline,设置目录时不需要使用引号。

为了永久的启用outline可以通过创建一个数据库启动触发器来设置use_stored_outlines

create or replace trigger enable_outlines_trig 
after startup on database 
begin 
execute immediate('alter system set use_stored_outlines=true'); 
end;

使用现有游标来创建stored outline

SQL> select * from t1 where t_meal=:x;

      T_ID T_MEAL
---------- --------------------
     80000 Pasta

SQL> select hash_value, child_number, sql_text from v$sql where sql_text like 'select * from t1 where t_meal=:x'; 
 
HASH_VALUE CHILD_NUMBER SQL_TEXT
---------- ------------ --------------------------------------------------------------------------------
2301090574            0 select * from t1 where t_meal=:x

SQL> exec dbms_outln.create_outline(2301090574,0);
 
PL/SQL procedure successfully completed

SQL> select * from dba_outlines;
 
NAME                           OWNER                          CATEGORY                       USED   TIMESTAMP   VERSION                                                 

         SQL_TEXT                                                                         SIGNATURE                        COMPATIBLE   ENABLED  FORMAT
------------------------------ ------------------------------ ------------------------------ ------ ----------- 

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

-------------------------------- ------------ -------- ------
SYS_OUTLINE_16060112321977339  JY                             DEFAULT                        UNUSED 2016/6/1 12 10.2.0.5.0                                              

         select * from t1 where t_meal=:x                                                 7DE4EC0CD8B1DA6C96B213F5D6DC349C COMPATIBLE   ENABLED  NORMAL

如果是10.2.0.4在创建outline之前需要先进行以下设置

SQL> alter session set create_stored_outlines = true;   


-- This step is to avoid Bug:5454975 fixed 10.2.0.4

使用现在游标来创建stored outline的例子如下:

SQL> var x number
SQL> exec :x:=1

PL/SQL procedure successfully completed.

SQL> select * from t1 where c1=:x;

        C1
----------
         1

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  0m63029gwn10n, child number 0
-------------------------------------
select * from t1 where c1=:x

Plan hash value: 1369807930

---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |       |       |     1 (100)|          |
|*  1 |  INDEX RANGE SCAN| IDX_T1 |     1 |     4 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("C1"=:X)


18 rows selected.

SQL> select hash_value, child_number, sql_text,sql_id from v$sql where sql_text like 'select * from t1%'; 
 
HASH_VALUE CHILD_NUMBER SQL_TEXT                                                                         SQL_ID
---------- ------------ -------------------------------------------------------------------------------- -------------
1607074836            0 select * from t1 where c1=:x                                                     0m63029gwn10n

SQL> exec dbms_outln.create_outline(1607074836,0);

PL/SQL procedure successfully completed.

SQL> select name,owner,category,used from dba_outlines;

NAME                           OWNER                          CATEGORY                       USED
------------------------------ ------------------------------ ------------------------------ ------
SYS_OUTLINE_16060115345355101  JY                             DEFAULT                        UNUSED


SQL> select * from dba_outline_hints where owner='JY' and name='SYS_OUTLINE_16060115345355101';

NAME                           OWNER                                NODE   STAGE      JOIN_POS   HINT
------------------------------ ------------------------------ ----------   ---------- ---------- 

--------------------------------------------------------------------------------
SYS_OUTLINE_16060115345355101  JY                                      1         1          1    INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."C1"))

SYS_OUTLINE_16060115345355101  JY                                      1         1          0    OUTLINE_LEAF(@"SEL$1")

SYS_OUTLINE_16060115345355101  JY                                      1         1          0    ALL_ROWS

SYS_OUTLINE_16060115345355101  JY                                      1         1          0    DB_VERSION('11.2.0.4')

SYS_OUTLINE_16060115345355101  JY                                      1         1          0    OPTIMIZER_FEATURES_ENABLE('11.2.0.4')

SYS_OUTLINE_16060115345355101  JY                                      1         1          0    IGNORE_OPTIM_EMBEDDED_HINTS


6 rows selected.

SQL> alter session set use_stored_outlines=true;

Session altered.

SQL> select * from t1 where c1=:x;

        C1
----------
         1

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  0m63029gwn10n, child number 1
-------------------------------------
select * from t1 where c1=:x

Plan hash value: 1369807930

---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |       |       |     1 (100)|          |
|*  1 |  INDEX RANGE SCAN| IDX_T1 |     1 |     4 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("C1"=:X)

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


22 rows selected.

使用另一个存储概要的执行计划来编辑存储概要

SQL> alter session set create_stored_outlines=true;

Session altered.

SQL> var n number
SQL> exec :n:=499990;

PL/SQL procedure successfully completed.

SQL> select count(*) from t1 where c1>:n;

  COUNT(*)
----------
        10

SQL> select count(*) from t1 where c1>1;

  COUNT(*)
----------
    499999

SQL> alter session set create_stored_outlines=false;

Session altered.

查询自动创建outline是否成功，从下面的查询结果可以看到创建了两个outline

SQL> select name,owner,category,used from dba_outlines;
 
NAME                           OWNER                          CATEGORY                       USED
------------------------------ ------------------------------ ------------------------------ ------
SYS_OUTLINE_16060210103585707  JY                             DEFAULT                        UNUSED
SYS_OUTLINE_16060210120211108  JY                             DEFAULT                        UNUSED

测试使用了outline的执行计划是否是使用了绑定变量的使用索引扫描，使用literal值的使用全表扫描

SQL> alter session set use_stored_outlines=true;

Session altered.

SQL> select count(*) from t1 where c1>:n;

  COUNT(*)
----------
        10

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  fxm7fcktguhvb, child number 1
-------------------------------------
select count(*) from t1 where c1>:n

Plan hash value: 1339937034

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE   |           |     1 |     4 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_T1_C1 |    10 |    40 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   2 - access("C1">:N)

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


23 rows selected.

可以看到使用了绑定变量的SQL使用outline “SYS_OUTLINE_16060210103585707”,执行计划为索引范围扫描

SQL> select count(*) from t1 where c1>1;

  COUNT(*)
----------
    499999

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  dt7wfh731mssj, child number 1
-------------------------------------
select count(*) from t1 where c1>1

Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   205 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |   499K|  1953K|   205   (6)| 00:00:03 |
---------------------------------------------------------------------------

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

   2 - filter("C1">1)

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


23 rows selected.

可以看到使用了literal值的SQL使用outline “SYS_OUTLINE_16060210120211108” ,执行计划为全表扫描。

下面将两个outline进行重命名,SYS_OUTLINE_16060210103585707(索引范围扫描)修改为OL1,SYS_OUTLINE_16060210120211108(全表扫描)修改为OL2.

SQL> alter outline SYS_OUTLINE_16060210103585707 rename to OL1;

Outline altered.

SQL> alter outline SYS_OUTLINE_16060210120211108 rename to OL2;

Outline altered.

SQL> select name,owner,category,used from dba_outlines;
 
NAME                           OWNER                          CATEGORY                       USED
------------------------------ ------------------------------ ------------------------------ ------
OL1                            JY                             DEFAULT                        USED
OL2                            JY                             DEFAULT                        USED

再次重新执行两个SQL语句，看是否能使用重命名之后的outline

SQL> select count(*) from t1 where c1>:n;

  COUNT(*)
----------
        10

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  fxm7fcktguhvb, child number 1
-------------------------------------
select count(*) from t1 where c1>:n

Plan hash value: 1339937034

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE   |           |     1 |     4 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_T1_C1 |    10 |    40 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   2 - access("C1">:N)

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

23 rows selected.

使用绑定变量的语句使用outline(OL1)，执行计划为索引范围扫描

SQL> select count(*) from t1 where c1>1;

  COUNT(*)
----------
    499999

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  dt7wfh731mssj, child number 0
-------------------------------------
select count(*) from t1 where c1>1

Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   205 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |   499K|  1953K|   205   (6)| 00:00:03 |
---------------------------------------------------------------------------

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

   2 - filter("C1">1)

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


23 rows selected.

使用literal值的语句使用outline(OL2)，执行计划为全表扫描

如果两个outline属于不同的目录，可以执行以下语句修改为相同目录

SQL> alter outline OL1 change category to DEFAULT;

Outline altered.

SQL> alter outline OL2 change category to DEFAULT;

Outline altered.

使用outline:OL1,OL2来创建两个私有outline:OLF,OLT,这里用outline(OL2/OLT)的信息来更新outline(OL1,OLF)，最终的目的是当执行原来使用绑定变是的语句是使用的outline将变成OL2(全表扫描)

SQL> create private outline OLF from OL1;

Outline created.

SQL> create private outline OLT from OL2;

Outline created.

SQL> select count(*) from ol$ where ol_name='OLF';

  COUNT(*)
----------
         1

SQL> select count(*) from ol$ where ol_name='OLT';

  COUNT(*)
----------
         1


SQL> update ol$ set hintcount=(select hintcount from ol$ where ol_name='OLT') where ol_name='OLF';

1 row updated.

SQL> delete from ol$ where ol_name='OLT';

1 row deleted.

SQL> update ol$ set ol_name='OLT' where ol_name='OLF';

1 row updated.


SQL> commit;

Commit complete.

重新同步私有outlien(OLT)

SQL> execute dbms_outln_edit.refresh_private_outline('OLT');

PL/SQL procedure successfully completed.

测试私有outline，查看使用绑定变量的语句的执行计划是不是变为全表扫描。

SQL> alter session set use_private_outlines=true;

Session altered.

SQL> var n number
SQL> exec :n:=499990;

PL/SQL procedure successfully completed.

SQL> select count(*) from t1 where c1>:n;

  COUNT(*)
----------
        10

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
SQL_ID  fxm7fcktguhvb, child number 1
-------------------------------------
select count(*) from t1 where c1>:n

Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   205 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |    10 |    40 |   205   (6)| 00:00:03 |
---------------------------------------------------------------------------

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

   2 - filter("C1">:N)

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


23 rows selected.

使用私有outline(OLT)来替换公有outline(OL1)

SQL> create or replace outline OL1 from private OLT;

Outline created.

当outline(OL1)使用私有outline(OLT)替之后，OL1变为UNUSED

SQL> select name,owner,category,used from dba_outlines;
 
NAME                           OWNER                          CATEGORY                       USED
------------------------------ ------------------------------ ------------------------------ ------
OL1                            JY                             DEFAULT                        UNUSED
OL2                            JY                             DEFAULT                        USED

查询OL1所包含的执行计划确实就变成了OL2的全表扫描了。

SQL> select * from dba_outline_hints where owner='JY' and name='OL1';
 
NAME                           OWNER                                NODE      STAGE   JOIN_POS HINT
------------------------------ ------------------------------ ---------- ---------- ---------- 

--------------------------------------------------------------------------------
OL1                            JY                                      1          1          0 OUTLINE_LEAF(@"SEL$1")
OL1                            JY                                      1          1          0 ALL_ROWS
OL1                            JY                                      1          1          0 OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
OL1                            JY                                      1          1          0 IGNORE_OPTIM_EMBEDDED_HINTS
OL1                            JY                                      1          1          1 FULL(@"SEL$1" "T1"@"SEL$1")

执行查询(select count(*) from t1 where c1>:n)来测试outline(OL1)的执行计划是否为全表扫描

SQL> var n number
SQL> exec :n:=499990;

PL/SQL procedure successfully completed.

SQL> select count(*) from t1 where c1>:n;

  COUNT(*)
----------
        10

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
SQL_ID  fxm7fcktguhvb, child number 2
-------------------------------------
select count(*) from t1 where c1>:n

Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   205 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |    10 |    40 |   205   (6)| 00:00:03 |
---------------------------------------------------------------------------

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

   2 - filter("C1">:N)

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


23 rows selected.

编辑outline(适用于10g与11g)
语句(select count(*) from t1 where c1>1)已经创建了outline(OL2)，其执行计划为全表扫描，通过编辑方式将其修改为索引范围扫描。

SQL> select count(*) from t1 where c1>1;

  COUNT(*)
----------
    499999

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
SQL_ID  dt7wfh731mssj, child number 0
-------------------------------------
select count(*) from t1 where c1>1

Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   205 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |   499K|  1953K|   205   (6)| 00:00:03 |
---------------------------------------------------------------------------

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

   2 - filter("C1">1)

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


23 rows selected.

使用outline(OL2)来创建私有outline(JYOL)

SQL> create private outline JYOL from OL2;

Outline created.

SQL> select count(*) from ol$ where ol_name='JYOL';

  COUNT(*)
----------
         1

通过更新ol$hints表来更新私有outline(JYOL)的执行计划，将全表扫描更新为索引范围扫描

SQL> select ol_name,hint#,hint_text,category from ol$hints where ol_name='JYOL'; 
                                                                                 
OL_NAME                             HINT# HINT_TEXT                              CATEGORY
------------------------------ ---------- -------------------------------------- ----------------
JYOL                                    1 OUTLINE_LEAF(@"SEL$1")                 DEFAULT
JYOL                                    2 ALL_ROWS                               DEFAULT
JYOL                                    3 OPTIMIZER_FEATURES_ENABLE('10.2.0.5')  DEFAULT
JYOL                                    4 IGNORE_OPTIM_EMBEDDED_HINTS            DEFAULT
JYOL                                    5 FULL(@"SEL$1" "T1"@"SEL$1")            DEFAULT
                                                                                 
SQL> update ol$hints set hint_text='INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."C1"))' where hint#=5 and ol_name='JYOL';

1 row updated.

SQL> select ol_name,hint#,hint_text,category from ol$hints where ol_name='JYOL';     
                                                                                     
OL_NAME                             HINT# HINT_TEXT                                  CATEGORY     
------------------------------ ---------- ------------------------------------------ -------------
JYOL                                    1 OUTLINE_LEAF(@"SEL$1")                     DEFAULT
JYOL                                    2 ALL_ROWS                                   DEFAULT
JYOL                                    3 OPTIMIZER_FEATURES_ENABLE('10.2.0.5')      DEFAULT
JYOL                                    4 IGNORE_OPTIM_EMBEDDED_HINTS                DEFAULT
JYOL                                    5 INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."C1"))   DEFAULT
                                                                                     
SQL> commit;                                                                         
                                                                                     
Commit complete.                                                                     

使用编辑后的执行计划重新同步私有outline(JYOL)

SQL> execute dbms_outln_edit.refresh_private_outline('JYOL');

PL/SQL procedure successfully completed.

测试将全表扫描修改为索引范围扫描后的私有outline能否使用

SQL> alter session set use_private_outlines=true;

Session altered.

SQL> select count(*) from t1 where c1>1;

  COUNT(*)
----------
    499999

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
SQL_ID  dt7wfh731mssj, child number 1
-------------------------------------
select count(*) from t1 where c1>1

Plan hash value: 1339937034

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |  1128 (100)|          |
|   1 |  SORT AGGREGATE   |           |     1 |     4 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_T1_C1 |   499K|  1953K|  1128   (2)| 00:00:14 |
-------------------------------------------------------------------------------

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

   2 - access("C1">1)

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


23 rows selected.

使用将全表扫描修改为索引范围扫描的私有outline(JYOL)来更新公有outline(OL2)，让OL2的执行计划从全表扫描变为索引范围扫描

SQL> create or replace outline OL2 from private JYOL;

Outline created.

测试outline(OL2)的执行计划是否变为了索引范围扫描

SQL> alter session set use_private_outlines=false;

Session altered.

SQL> alter session set use_stored_outlines=true;

Session altered.

SQL> select count(*) from t1 where c1>1;

  COUNT(*)
----------
    499999

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
SQL_ID  dt7wfh731mssj, child number 0
-------------------------------------
select count(*) from t1 where c1>1

Plan hash value: 1339937034

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |  1128 (100)|          |
|   1 |  SORT AGGREGATE   |           |     1 |     4 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_T1_C1 |   499K|  1953K|  1128   (2)| 00:00:14 |
-------------------------------------------------------------------------------

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

   2 - access("C1">1)

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


23 rows selected.

从结果可以看到，OL2的执行计划确实从原来的全表扫描变为了索引范围扫描，说明修改执行计划是成功的。

传输stored outline(从一个数据库传输到另一个数据库,适用于9i及以版本)
1.将outline(OL2)的目录从DEFAULT修改为JY

SQL> alter outline OL2 change category to JY;

Outline altered.

2.使用exp工具导入stored outlines
使用带query参数的exp命令是为了只导为目录JY中的outline数据。可以一次传输多个stored outlines

windows平台的导出命令如下:

exp system/ file=myoutln.dmp tables=(outln.ol$,outln.ol$hints,outln.ol$nodes) query=\”where category=’MYCAT’\” statistics=none)

unix平台的导出命令如下:
exp system/ file=myoutln.dmp tables=\(outln.ol\$,outln.ol\$hints,outln.ol\$nodes\) query=\”where category=\’MYCAT\’\” statistics=none

OpenVMS平台的导出命令如下:
exp system/ file=myoutln.dmp tables=(outln.ol$,outln.ol$hints,outln.ol$nodes) query=”””where category=’MYCAT'””” statistics=none

这里测试平台是linux,导出命令如下:

[oracle@weblogic28 ~]$ exp jy/jy  file=/home/oracle/myoutln.dmp log=/home/oracle/myoutln.log tables=\(outln.ol\$,outln.ol\$hints,outln.ol\$nodes\) query=\"where 

category=\'JY\'\" statistics=none

Export: Release 10.2.0.5.0 - Production on Thu Jun 2 12:06:04 2016

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
Current user changed to OUTLN
. . exporting table                            OL$          1 rows exported
. . exporting table                       OL$HINTS          5 rows exported
. . exporting table                       OL$NODES          1 rows exported
Export terminated successfully without warnings.

将导出的outline数据文件传输到目标数据库服务器上

[oracle@jyrac1 ~]$ scp oracle@10.138.130.28:/home/oracle/myoutln.dmp /home/oracle/
The authenticity of host '10.138.130.28 (10.138.130.28)' can't be established.
RSA key fingerprint is 25:39:c3:5e:d8:b7:fc:5f:54:b5:a1:ed:17:a1:a0:90.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '10.138.130.28' (RSA) to the list of known hosts.
oracle@10.138.130.28's password: 
myoutln.dmp                                                                                                                          100%   16KB  16.0KB/s   00:00    

使用imp工具将导出的outline数据导入到目标数据库

[oracle@jyrac1 ~]$ imp jy/jy file=/home/oracle/myoutln.dmp  full=y ignore=y

Import: Release 11.2.0.4.0 - Production on Thu Jun 2 11:35:53 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing o

Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
export client uses ZHS16GBK character set (possible charset conversion)
. importing JY's objects into JY
. importing OUTLN's objects into OUTLN
. . importing table                          "OL$"          1 rows imported
. . importing table                     "OL$HINTS"          5 rows imported
. . importing table                     "OL$NODES"          1 rows imported
Import terminated successfully without warnings.

启用stored outline

SQL> alter session set use_stored_outlines=JY;

Session altered.

SQL> select count(*) from t1 where c1>1;

  COUNT(*)
----------
    499999

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  dt7wfh731mssj, child number 1
-------------------------------------
select count(*) from t1 where c1>1

Plan hash value: 1970818898

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |       |       |    12 (100)|          |
|   1 |  SORT AGGREGATE   |        |     1 |     4 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_T1 |  4999 | 19996 |    12   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   2 - access("C1">1)

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

23 rows selected.

对于10g及以版本也可以使用datapump来导出与导入outline数据
导出:
expdp outln/password directory=DUMP_DIR dumpfile=testoutln.dmp tables=OL$,OL$HINTS,OL$NODES query=\”where category=\’JY\’\”
或
expdp jy/jy directory=DUMP_DIR dumpfile=testoutln.dmp query=\”where category=\’JY\’\” schemas=outln

[oracle@jyrac1 ~]$ expdp jy/jy directory=DUMP_DIR dumpfile=testoutln.dmp  query=\"where category=\'JY\'\"  schemas=outln

Export: Release 11.2.0.4.0 - Production on Thu Jun 2 11:53:41 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "JY"."SYS_EXPORT_SCHEMA_01":  jy/******** directory=DUMP_DIR dumpfile=testoutln.dmp query="where category='JY'" schemas=outln 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION
. . exported "OUTLN"."OL$HINTS"                          13.49 KB       5 rows
. . exported "OUTLN"."OL$"                               10.32 KB       1 rows
. . exported "OUTLN"."OL$NODES"                          7.820 KB       1 rows
Master table "JY"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for JY.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/testoutln.dmp
Job "JY"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Jun 2 12:05:08 2016 elapsed 0 00:10:38

导入:
impdp outln/password directory=DUMP_DIR dumpfile=testoutln.dmp tables=OL$,OL$HINTS,OL$NODES table_exists_action=truncate
或
impdp jy/jy directory=DUMP_DIR dumpfile=testoutln.dmp remap_schema=outln:outln table_exists_action=truncate

[oracle@jy1 ~]$ impdp jy/jy directory=DUMP_DIR dumpfile=testoutln.dmp   remap_schema=outln:outln table_exists_action=truncate

Import: Release 11.2.0.4.0 - Production on Thu Jun 2 12:09:08 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "JY"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "JY"."SYS_IMPORT_FULL_01":  jy/******** directory=DUMP_DIR dumpfile=testoutln.dmp remap_schema=outln:outln table_exists_action=truncate 
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"OUTLN" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Table "OUTLN"."OL$" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Table "OUTLN"."OL$NODES" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Table "OUTLN"."OL$HINTS" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "OUTLN"."OL$HINTS"                          13.49 KB       5 rows
. . imported "OUTLN"."OL$"                               10.32 KB       1 rows
. . imported "OUTLN"."OL$NODES"                          7.820 KB       1 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
ORA-31684: Object type PROCEDURE:"OUTLN"."ORA$GRANT_SYS_SELECT" already exists
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION
Job "JY"."SYS_IMPORT_FULL_01" completed with 2 error(s) at Thu Jun 2 12:15:44 2016 elapsed 0 00:06:14

在Oracle11g中可以使用TSPITR来对被删除的表空间执行表空间按时间点恢复，下面通过一个实例来演示这个功能。
1.创建测试表空间test

SQL> create tablespace test datafile '/u03/app/oracle/oradata/db/test01.dbf' size 50M autoextend off extent management local segment space management auto;

Tablespace created.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u03/app/oracle/oradata/db/system01.dbf
/u03/app/oracle/oradata/db/sysaux01.dbf
/u03/app/oracle/oradata/db/undotbs01.dbf
/u03/app/oracle/oradata/db/users01.dbf
/u03/app/oracle/oradata/db/example01.dbf
/u03/app/oracle/oradata/db/test01.dbf

6 rows selected.

2.创建测试用户test与测试表t1

SQL> create user test identified by "test" default tablespace test temporary tablespace temp;

User created.

SQL> create table test.t1 as select * from dba_objects;

Table created.

SQL> select count(*) from test.t1;

  COUNT(*)
----------
     86031

3.对整个数据库的执行完全备份

RMAN> backup as backupset database plus archivelog;


Starting backup at 2015-06-01 22:05:12
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=5 RECID=1 STAMP=880996327
input archived log thread=1 sequence=6 RECID=2 STAMP=880996438
input archived log thread=1 sequence=7 RECID=3 STAMP=881014383
input archived log thread=1 sequence=8 RECID=4 STAMP=881014612
input archived log thread=1 sequence=9 RECID=5 STAMP=881015165
input archived log thread=1 sequence=10 RECID=13 STAMP=881233508
input archived log thread=1 sequence=11 RECID=14 STAMP=881233508
channel ORA_DISK_1: starting piece 1 at 2015-06-01 22:05:14
channel ORA_DISK_1: finished piece 1 at 2015-06-01 22:05:21
piece handle=/u02/backup/0kq8ea8q_1_1 tag=TAG20150601T220514 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=12 STAMP=881233507
channel ORA_DISK_1: starting piece 1 at 2015-06-01 22:05:21
channel ORA_DISK_1: finished piece 1 at 2015-06-01 22:05:22
piece handle=/u02/backup/0lq8ea91_1_1 tag=TAG20150601T220514 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=12 RECID=15 STAMP=881233508
channel ORA_DISK_1: starting piece 1 at 2015-06-01 22:05:23
channel ORA_DISK_1: finished piece 1 at 2015-06-01 22:05:26
piece handle=/u02/backup/0mq8ea92_1_1 tag=TAG20150601T220514 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=2 RECID=21 STAMP=881233663
input archived log thread=1 sequence=3 RECID=22 STAMP=881233941
input archived log thread=1 sequence=4 RECID=23 STAMP=881234587
input archived log thread=1 sequence=5 RECID=24 STAMP=881235045
input archived log thread=1 sequence=6 RECID=25 STAMP=881235180
input archived log thread=1 sequence=7 RECID=26 STAMP=881272559
channel ORA_DISK_1: starting piece 1 at 2015-06-01 22:05:26
channel ORA_DISK_1: finished piece 1 at 2015-06-01 22:05:27
piece handle=/u02/backup/0nq8ea96_1_1 tag=TAG20150601T220514 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=13 RECID=16 STAMP=881233508
input archived log thread=1 sequence=14 RECID=17 STAMP=881233508
input archived log thread=1 sequence=15 RECID=18 STAMP=881233508
input archived log thread=1 sequence=16 RECID=19 STAMP=881233508
input archived log thread=1 sequence=17 RECID=20 STAMP=881233508
input archived log thread=1 sequence=18 RECID=11 STAMP=881232587
input archived log thread=1 sequence=19 RECID=9 STAMP=881232587
input archived log thread=1 sequence=20 RECID=10 STAMP=881232587
channel ORA_DISK_1: starting piece 1 at 2015-06-01 22:05:27
channel ORA_DISK_1: finished piece 1 at 2015-06-01 22:05:28
piece handle=/u02/backup/0oq8ea97_1_1 tag=TAG20150601T220514 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=8 RECID=27 STAMP=881273112
channel ORA_DISK_1: starting piece 1 at 2015-06-01 22:05:28
channel ORA_DISK_1: finished piece 1 at 2015-06-01 22:05:29
piece handle=/u02/backup/0pq8ea98_1_1 tag=TAG20150601T220514 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2015-06-01 22:05:29

Starting backup at 2015-06-01 22:05:29
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u03/app/oracle/oradata/db/system01.dbf
input datafile file number=00002 name=/u03/app/oracle/oradata/db/sysaux01.dbf
input datafile file number=00003 name=/u03/app/oracle/oradata/db/undotbs01.dbf
input datafile file number=00005 name=/u03/app/oracle/oradata/db/test01.dbf
input datafile file number=00004 name=/u03/app/oracle/oradata/db/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2015-06-01 22:05:31
channel ORA_DISK_1: finished piece 1 at 2015-06-01 22:09:16
piece handle=/u02/backup/0qq8ea9b_1_1 tag=TAG20150601T220529 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:03:45
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 2015-06-01 22:09:20
channel ORA_DISK_1: finished piece 1 at 2015-06-01 22:09:21
piece handle=/u02/backup/0rq8eagd_1_1 tag=TAG20150601T220529 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2015-06-01 22:09:21

Starting backup at 2015-06-01 22:09:21
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=9 RECID=28 STAMP=881273363
channel ORA_DISK_1: starting piece 1 at 2015-06-01 22:09:27
channel ORA_DISK_1: finished piece 1 at 2015-06-01 22:09:30
piece handle=/u02/backup/0sq8eagn_1_1 tag=TAG20150601T220926 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 2015-06-01 22:09:30

4.删除表空间test，并记录删除操作执行之前的系统SCN与时间

SQL> select to_char(scn_to_timestamp(current_scn),'yyyy-mm-dd hh24:mi:ss'),current_scn from v$database;

TO_CHAR(SCN_TO_TIME CURRENT_SCN
------------------- -----------
2015-06-01 22:11:45      751203


SQL> drop tablespace test including contents and datafiles;

Tablespace dropped.

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE

SQL> host ls -lrt /u03/app/oracle/oradata/db/
total 1548000
-rw-r----- 1 oracle11 oinstall  30416896 Jun  1 22:01 temp01.dbf
-rw-r----- 1 oracle11 oinstall  52429312 Jun  1 22:05 redo02.log
-rw-r----- 1 oracle11 oinstall  52429312 Jun  1 22:09 redo03.log
-rw-r----- 1 oracle11 oinstall   5251072 Jun  1 22:14 users01.dbf
-rw-r----- 1 oracle11 oinstall 775954432 Jun  1 22:14 system01.dbf
-rw-r----- 1 oracle11 oinstall  73408512 Jun  1 22:14 undotbs01.dbf
-rw-r----- 1 oracle11 oinstall 545267712 Jun  1 22:14 sysaux01.dbf
-rw-r----- 1 oracle11 oinstall  52429312 Jun  1 22:14 redo01.log
-rw-r----- 1 oracle11 oinstall   9748480 Jun  1 22:14 control01.ctl

从上面的查询可以看到表空间test已经被删除了。

5.现在执行TSPITR将表空间test恢复到被删除之前的时间点

RMAN> recover tablespace "TEST" until time '2015-06-01 22:11:45' auxiliary destination '/u02/auxiliary';

Starting recover at 2015-06-01 22:22:25
starting full resync of recovery catalog
full resync complete
using channel ORA_DISK_1

Creating automatic instance, with SID='jAvb'

initialization parameters used for automatic instance:
db_name=DB
db_unique_name=jAvb_tspitr_DB
compatible=11.2.0.4.0
db_block_size=8192
db_files=200
sga_target=1G
processes=80
db_create_file_dest=/u02/auxiliary
log_archive_dest_1='location=/u02/auxiliary'
#No auxiliary parameter file used


starting up automatic instance DB

Oracle instance started

Total System Global Area    1071333376 bytes

Fixed Size                     1369420 bytes
Variable Size                281021108 bytes
Database Buffers             784334848 bytes
Redo Buffers                   4608000 bytes
Automatic instance created

contents of Memory Script:
{
# set requested point in time
set until  time "2015-06-01 22:11:45";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
# resync catalog
resync catalog;
}
executing Memory Script

executing command: SET until clause

Starting restore at 2015-06-01 22:22:40
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u02/backup/0rq8eagd_1_1
channel ORA_AUX_DISK_1: piece handle=/u02/backup/0rq8eagd_1_1 tag=TAG20150601T220529
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u02/auxiliary/DB/controlfile/o1_mf_bprtokmc_.ctl
Finished restore at 2015-06-01 22:22:42

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

starting full resync of recovery catalog
full resync complete

contents of Memory Script:
{
# set requested point in time
set until  time "2015-06-01 22:11:45";
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile  1 to new;
set newname for clone datafile  3 to new;
set newname for clone datafile  2 to new;
set newname for clone tempfile  1 to new;
set newname for datafile  5 to
 "/u03/app/oracle/oradata/db/test01.dbf";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 3, 2, 5;
switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u02/auxiliary/DB/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 2015-06-01 22:22:51
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u02/auxiliary/DB/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u02/auxiliary/DB/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u02/auxiliary/DB/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u03/app/oracle/oradata/db/test01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u02/backup/0qq8ea9b_1_1
channel ORA_AUX_DISK_1: piece handle=/u02/backup/0qq8ea9b_1_1 tag=TAG20150601T220529
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:15
Finished restore at 2015-06-01 22:25:07

datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=881274307 file name=/u02/auxiliary/DB/datafile/o1_mf_system_bprtowv6_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=881274307 file name=/u02/auxiliary/DB/datafile/o1_mf_undotbs1_bprtowxf_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=881274307 file name=/u02/auxiliary/DB/datafile/o1_mf_sysaux_bprtowxc_.dbf

contents of Memory Script:
{
# set requested point in time
set until  time "2015-06-01 22:11:45";
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone "alter database datafile  3 online";
sql clone "alter database datafile  2 online";
sql clone "alter database datafile  5 online";
# recover and open resetlogs
recover clone database tablespace  "TEST", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  1 online

sql statement: alter database datafile  3 online

sql statement: alter database datafile  2 online

sql statement: alter database datafile  5 online

Starting recover at 2015-06-01 22:25:08
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 9 is already on disk as file /u03/archivelog/1_9_881232587.dbf
archived log for thread 1 with sequence 10 is already on disk as file /u03/archivelog/1_10_881232587.dbf
archived log file name=/u03/archivelog/1_9_881232587.dbf thread=1 sequence=9
archived log file name=/u03/archivelog/1_10_881232587.dbf thread=1 sequence=10
media recovery complete, elapsed time: 00:00:16
Finished recover at 2015-06-01 22:25:25

database opened

contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace  "TEST" read only';
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u02/auxiliary''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u02/auxiliary''";
}
executing Memory Script

sql statement: alter tablespace  "TEST" read only

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u02/auxiliary''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u02/auxiliary''

Performing export of metadata...
   EXPDP> Starting "SYS"."TSPITR_EXP_jAvb":
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   EXPDP> Master table "SYS"."TSPITR_EXP_jAvb" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_jAvb is:
   EXPDP>   /u02/auxiliary/tspitr_jAvb_29236.dmp
   EXPDP> ******************************************************************************
   EXPDP> Datafiles required for transportable tablespace TEST:
   EXPDP>   /u03/app/oracle/oradata/db/test01.dbf
   EXPDP> Job "SYS"."TSPITR_EXP_jAvb" successfully completed at Mon Jun 1 22:27:28 2015 elapsed 0 00:01:12
Export completed


contents of Memory Script:
{
# shutdown clone before import
shutdown clone immediate
}
executing Memory Script

database closed
database dismounted
Oracle instance shut down

Performing import of metadata...
   IMPDP> Master table "SYS"."TSPITR_IMP_jAvb" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_jAvb":
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   IMPDP> Job "SYS"."TSPITR_IMP_jAvb" successfully completed at Mon Jun 1 22:28:23 2015 elapsed 0 00:00:14
Import completed


contents of Memory Script:
{
# make read write and offline the imported tablespaces
sql 'alter tablespace  "TEST" read write';
sql 'alter tablespace  "TEST" offline';
# enable autobackups after TSPITR is finished
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
# resync catalog
resync catalog;
}
executing Memory Script

sql statement: alter tablespace  "TEST" read write

sql statement: alter tablespace  "TEST" offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

starting full resync of recovery catalog
full resync complete

Removing automatic instance
Automatic instance removed
auxiliary instance file /u02/auxiliary/DB/datafile/o1_mf_temp_bprtv5bo_.tmp deleted
auxiliary instance file /u02/auxiliary/DB/onlinelog/o1_mf_3_bprttwvr_.log deleted
auxiliary instance file /u02/auxiliary/DB/onlinelog/o1_mf_2_bprttt0k_.log deleted
auxiliary instance file /u02/auxiliary/DB/onlinelog/o1_mf_1_bprttp4g_.log deleted
auxiliary instance file /u02/auxiliary/DB/datafile/o1_mf_sysaux_bprtowxc_.dbf deleted
auxiliary instance file /u02/auxiliary/DB/datafile/o1_mf_undotbs1_bprtowxf_.dbf deleted
auxiliary instance file /u02/auxiliary/DB/datafile/o1_mf_system_bprtowv6_.dbf deleted
auxiliary instance file /u02/auxiliary/DB/controlfile/o1_mf_bprtokmc_.ctl deleted
Finished recover at 2015-06-01 22:28:45

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
TEST                           OFFLINE

6 rows selected.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u03/app/oracle/oradata/db/system01.dbf
/u03/app/oracle/oradata/db/sysaux01.dbf
/u03/app/oracle/oradata/db/undotbs01.dbf
/u03/app/oracle/oradata/db/users01.dbf
/u03/app/oracle/oradata/db/test01.dbf

SQL> host ls -lrt /u03/app/oracle/oradata/db/
total 1599328
-rw-r----- 1 oracle11 oinstall  52429312 Jun  1 22:09 redo03.log
-rw-r----- 1 oracle11 oinstall   5251072 Jun  1 22:22 users01.dbf
-rw-r----- 1 oracle11 oinstall  52429312 Jun  1 22:22 redo01.log
-rw-r----- 1 oracle11 oinstall  30416896 Jun  1 22:28 temp01.dbf
-rw-r----- 1 oracle11 oinstall  52436992 Jun  1 22:28 test01.dbf
-rw-r----- 1 oracle11 oinstall 775954432 Jun  1 22:28 system01.dbf
-rw-r----- 1 oracle11 oinstall  73408512 Jun  1 22:28 undotbs01.dbf
-rw-r----- 1 oracle11 oinstall 545267712 Jun  1 22:28 sysaux01.dbf
-rw-r----- 1 oracle11 oinstall  52429312 Jun  1 22:29 redo02.log
-rw-r----- 1 oracle11 oinstall   9814016 Jun  1 22:30 control01.ctl

从上面的查询结果可以看到表空间test已经恢复，但现在test表空间是脱机状态。

6.将表空间test联机，并查询表test.t1来验证恢复是否真正成功.

RMAN> sql 'alter tablespace test online';

sql statement: alter tablespace test online
starting full resync of recovery catalog
full resync complete

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
TEST                           ONLINE

6 rows selected.

SQL> select count(*) from test.t1;

  COUNT(*)
----------
     86031

从上面的查询结果可以看到被删除的表空间test已经恢复到被删除之前的时间点，表test.t1的记录已经恢复回来。

Oracle 10g中想要固定执行计划只能使用outline,sql profile不能起固定sql执行的效果，但是在Oracle 10.2.0.5中使用dbms_outln.create_outline通过使用共享池中的游标来创建outline,发现创建的outline与游标中的执行计划并不一致，而在oracle 10.2.0.4与oracle 11.2.0.4中是通过游标来创建的outline与cursor的实际执行计划是一致的。这应该是BUG.

Oracle 10.2.0.5中的测试如下:
定义绑定变量

SQL> var x varchar2(20)
SQL> exec :x:='Kabab';

PL/SQL procedure successfully completed.

执行查询

SQL> select * from t1 where t_meal=:x;

      T_ID T_MEAL
---------- --------------------
     79999 Kabab

查看实际的执行计划

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  7runhd24kgqsf, child number 0
-------------------------------------
select * from t1 where t_meal=:x

Plan hash value: 141743202

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

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |               |       |       |     2 (100)|          |

|   1 |  TABLE ACCESS BY INDEX ROWID| T1            |     1 |    11 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_T1_T_MEAL |     1 |       |     1   (0)| 00:00:01 |

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


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

   2 - access("T_MEAL"=:X)


19 rows selected.

可以看到执行计划使用的是索引范围扫描

查询SQL语句的SQL_ID.hash_value,child_number

SQL> select hash_value, child_number, sql_text,sql_id from v$sql where sql_text like 'select * from t1%';

HASH_VALUE CHILD_NUMBER SQL_TEXT                                                                         SQL_ID
---------- ------------ -------------------------------------------------------------------------------- -------------
2301090574            0 select * from t1 where t_meal=:x                                                 7runhd24kgqsf

使用游标来创建outline

SQL> exec dbms_outln.create_outline(2301090574,0);

PL/SQL procedure successfully completed.

SQL> select name,owner,category,used from dba_outlines;

NAME                           OWNER                             CATEGORY                       USED
------------------------------ ------------------------------    -----------------------------  ------
SYS_OUTLINE_16060116155127504  JY                                DEFAULT                        UNUSED

查询outline的hint信息，可以看到没有index hint而是full这说明是全表扫描

SQL> select * from dba_outline_hints where owner='JY' and name='SYS_OUTLINE_16060116155127504';

NAME                           OWNER                                NODE   STAGE      JOIN_POS   HINT
------------------------------ ------------------------------ ----------   ---------- ---------- -------------------------------------------------------------------------------

SYS_OUTLINE_16060116155127504  JY                                      1         1          1    FULL(@"SEL$1" "T1"@"SEL$1")
SYS_OUTLINE_16060116155127504  JY                                      1         1          0    OUTLINE_LEAF(@"SEL$1")
SYS_OUTLINE_16060116155127504  JY                                      1         1          0    ALL_ROWS
SYS_OUTLINE_16060116155127504  JY                                      1         1          0    OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
SYS_OUTLINE_16060116155127504  JY                                      1         1          0    IGNORE_OPTIM_EMBEDDED_HINTS

启用outline,并重新执行sql语句

SQL> alter session set use_stored_outlines=true;

Session altered.
SQL> select * from t1 where t_meal=:x
  2  ;

      T_ID T_MEAL
---------- --------------------
     79999 Kabab

查询使用了outline的执行计划发现却是全表扫描，并不是游标中的索引范围扫描

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  0c2v6n4c0sj6v, child number 0
-------------------------------------
select * from t1 where t_meal=:x

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    47 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    11 |    47   (5)| 00:00:01 |

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

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

   1 - filter("T_MEAL"=:X)

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


22 rows selected.

在oracle 10.2.0.5中如果是使用自动创建outline，那么outline所包含的执行计划与游标中的执行计划是一致的，测试如下:
在会话级启用自动为查询语句创建outline

SQL> alter session set create_stored_outlines=true;

Session altered.

执行查询

SQL> select * from t1 where t_meal=:x;

      T_ID T_MEAL
---------- --------------------
     79999 Kabab

禁用自动创建outline

SQL> alter session set create_stored_outlines=false;

Session altered.

查看语句的执行计划，使用了索引范围扫描

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  7runhd24kgqsf, child number 1
-------------------------------------
select * from t1 where t_meal=:x

Plan hash value: 141743202

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

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |               |       |       |     2 (100)|          |

|   1 |  TABLE ACCESS BY INDEX ROWID| T1            |     1 |    11 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_T1_T_MEAL |     1 |       |     1   (0)| 00:00:01 |

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


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

   2 - access("T_MEAL"=:X)


19 rows selected.

查询自动创建outline是否成功

SQL> select name,owner,category,used from dba_outlines;

NAME                           OWNER                          CATEGORY                       USED
------------------------------ ------------------------------ ------------------------------ ------
SYS_OUTLINE_16060117095505105  JY                             DEFAULT                        UNUSED

SQL> select * from dba_outline_hints where owner='JY' and name='SYS_OUTLINE_16060117095505105';

NAME                           OWNER                                NODE      STAGE   JOIN_POS HINT
------------------------------ ------------------------------ ---------- ---------- ---------- --------------------------------------------------------------------------------
SYS_OUTLINE_16060117095505105  JY                                      1          1          1 INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."T_MEAL"))
SYS_OUTLINE_16060117095505105  JY                                      1          1          0 OUTLINE_LEAF(@"SEL$1")
SYS_OUTLINE_16060117095505105  JY                                      1          1          0 ALL_ROWS
SYS_OUTLINE_16060117095505105  JY                                      1          1          0 OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
SYS_OUTLINE_16060117095505105  JY                                      1          1          0 IGNORE_OPTIM_EMBEDDED_HINTS

启用outline

SQL> alter session set use_stored_outlines=true;

Session altered.

重新执行查询

SQL> select * from t1 where t_meal=:x;

      T_ID T_MEAL
---------- --------------------
     79999 Kabab

查看使用outline的执行计划使用了索引范围扫描

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  0c2v6n4c0sj6v, child number 0
-------------------------------------
select * from t1 where t_meal=:x

Plan hash value: 141743202

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

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |               |       |       |     2 (100)|          |

|   1 |  TABLE ACCESS BY INDEX ROWID| T1            |     1 |    11 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_T1_T_MEAL |     1 |       |     1   (0)| 00:00:01 |

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


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

   2 - access("T_MEAL"=:X)


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note
-----
   - outline "SYS_OUTLINE_16060117095505105" used for this statement


23 rows selected.

Oracle 10.2.0.4中的测试如下:
定义绑定变量

SQL> var x varchar2(20)
SQL> exec :x:='1';

PL/SQL procedure successfully completed.

执行查询

SQL> select * from t1 where c1=:x;

C1
--------------------
1

查看语句的执行计划

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  0m63029gwn10n, child number 0
-------------------------------------
select * from t1 where c1=:x

Plan hash value: 1629967410

---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |       |       |     1 (100)|          |
|*  1 |  INDEX RANGE SCAN| IDX_T1 |     1 |     4 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("C1"=:X)


18 rows selected.

查询语句的hash_value与sql_id

SQL> select hash_value, child_number, sql_text,sql_id from v$sql where sql_text like 'select * from t1%';

HASH_VALUE CHILD_NUMBER  SQL_TEXT                                                                          SQL_ID
---------- ------------  --------------------------------------------------------------------------------  -------------
1607074836            0  select * from t1 where c1=:x                                                      0m63029gwn10n

使用游标来创建outline

SQL> exec dbms_outln.create_outline(1607074836,0);

PL/SQL procedure successfully completed.

查看outline是否创建成功

SQL> select name,owner,category,used from dba_outlines;

NAME                           OWNER                          CATEGORY                       USED
------------------------------ ------------------------------ ------------------------------ ------
SYS_OUTLINE_16060115381869401  INSUR_CHANGDE                  DEFAULT                        UNUSED

查询outline的hint可以看到有index hint，这说明使用了索引

SQL> select * from dba_outline_hints where name='SYS_OUTLINE_16060115381869401';

NAME                           OWNER                                NODE  STAGE      JOIN_POS   HINT
------------------------------ ------------------------------ ----------  ---------- ---------- --------------------------------------------------------------------------------
SYS_OUTLINE_16060115381869401  INSUR_CHANGDE                           1         1          1   INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."C1"))

SYS_OUTLINE_16060115381869401  INSUR_CHANGDE                           1         1          0   OUTLINE_LEAF(@"SEL$1")

SYS_OUTLINE_16060115381869401  INSUR_CHANGDE                           1         1          0   ALL_ROWS

SYS_OUTLINE_16060115381869401  INSUR_CHANGDE                           1         1          0   OPT_PARAM('optimizer_index_caching' 90)

SYS_OUTLINE_16060115381869401  INSUR_CHANGDE                           1         1          0   OPT_PARAM('optimizer_index_cost_adj' 20)

SYS_OUTLINE_16060115381869401  INSUR_CHANGDE                           1         1          0   OPTIMIZER_FEATURES_ENABLE('10.2.0.4')

SYS_OUTLINE_16060115381869401  INSUR_CHANGDE                           1         1          0   IGNORE_OPTIM_EMBEDDED_HINTS


7 rows selected.

启用outline并重新执行sql语句

SQL> alter session set use_stored_outlines=true;

Session altered.

SQL> select * from t1 where c1=:x;

C1
--------------------
1

查询使用outline后的执行计划，确实是使用的索引范围扫描与游标中的执行计划一致

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  0m63029gwn10n, child number 1
-------------------------------------
select * from t1 where c1=:x

Plan hash value: 1629967410

---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |       |       |     1 (100)|          |
|*  1 |  INDEX RANGE SCAN| IDX_T1 |     1 |     4 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("C1"=:X)

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


22 rows selected.

Oracle 11.2.0.4的测试如下:
定义绑定变量

SQL> var x number
SQL> exec :x:=1

PL/SQL procedure successfully completed.

执行查询

SQL> select * from t1 where c1=:x;

        C1
----------
         1

查看执行计划

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  0m63029gwn10n, child number 0
-------------------------------------
select * from t1 where c1=:x

Plan hash value: 1369807930

---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |       |       |     1 (100)|          |
|*  1 |  INDEX RANGE SCAN| IDX_T1 |     1 |     4 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("C1"=:X)


18 rows selected.

查询语句的hash_value,child_number,sql_id

SQL> select hash_value, child_number, sql_text,sql_id from v$sql where sql_text like 'select * from t1%';

HASH_VALUE CHILD_NUMBER SQL_TEXT                                                                         SQL_ID
---------- ------------ -------------------------------------------------------------------------------- -------------
1607074836            0 select * from t1 where c1=:x                                                     0m63029gwn10n

使用游标来创建outline

SQL> exec dbms_outln.create_outline(1607074836,0);

PL/SQL procedure successfully completed.

查看outline是否创建成功

SQL> select name,owner,category,used from dba_outlines;

NAME                           OWNER                          CATEGORY                       USED
------------------------------ ------------------------------ ------------------------------ ------
SYS_OUTLINE_16060115345355101  JY                             DEFAULT                        UNUSED

查询outline的hint信息可以看到index hint信息这说明使用了索引

SQL> select * from dba_outline_hints where owner='JY' and name='SYS_OUTLINE_16060115345355101';

NAME                           OWNER                                NODE   STAGE      JOIN_POS   HINT
------------------------------ ------------------------------ ----------   ---------- ---------- --------------------------------------------------------------------------------
SYS_OUTLINE_16060115345355101  JY                                      1         1          1    INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."C1"))

SYS_OUTLINE_16060115345355101  JY                                      1         1          0    OUTLINE_LEAF(@"SEL$1")

SYS_OUTLINE_16060115345355101  JY                                      1         1          0    ALL_ROWS

SYS_OUTLINE_16060115345355101  JY                                      1         1          0    DB_VERSION('11.2.0.4')

SYS_OUTLINE_16060115345355101  JY                                      1         1          0    OPTIMIZER_FEATURES_ENABLE('11.2.0.4')

SYS_OUTLINE_16060115345355101  JY                                      1         1          0    IGNORE_OPTIM_EMBEDDED_HINTS


6 rows selected.

启用outline并重新执行SQL语句

SQL> alter session set use_stored_outlines=true;

Session altered.

SQL> select * from t1 where c1=:x;

        C1
----------
         1

查询使用outline后的执行计划使用了索引，与游标中的执行计划一致

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  0m63029gwn10n, child number 1
-------------------------------------
select * from t1 where c1=:x

Plan hash value: 1369807930

---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |       |       |     1 (100)|          |
|*  1 |  INDEX RANGE SCAN| IDX_T1 |     1 |     4 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("C1"=:X)

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


22 rows selected.

从测试结果来看，要在10.2.0.5中创建outline固定执行计划不要使用dbms_outln.create_outline这种方法，因为这种方法生成了outline所包含的执行计划并不正确。