分类：oracle

存储概要(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所包含的执行计划并不正确。

有时SQL语句在没有明显原因的情况下，突然变得执行缓慢,SQL语句所引用的对象的统计信息，对象数据，SQL都没有改变。

SQL语句的游标由于某些原因生成了新的执行计划。出现这种情况的原因至少为以下一种:
.数据库重新启动
.由于任何原因游标没有被使用，并且因为LRU算法被aged out
.SQL语句所引用的任何对象的统计信息发生改变
.SQL语句所引用的任何对象的结构发生改变
.对SQL语句所引用的对象授予/回收权限

如果游标被pinned(正被使用)，那么清空共享池也不会将该游标删除。

可能造成SQL语句突然执行缓慢的原因一个可能主要的原因是使用了oracle 9i所引入的绑定变量窥视。使用这个功能，查询优化器将会在第一次调用一个游标时窥视用户定义绑定变量的值。这个功能能让优化器像使用literal值一样对待绑定变量的值来计算任何where子句条件的选择率。在后续调用游标时，不会执行绑定变量窥视，并且游标是共享,基于标准游标共享的标准，即使后续调用游标时使用不同的绑定变量值，还是会使用共享标。换句话说，这个游标被解析一次被多次使用。如果在SQL第一次被解析时所使用的绑定变量值不能代表大多数数据时，那么对于不同的绑定变量来说第一次生成的执行计划就很有可能不是最优的。

在SQL语句中使用绑定变量，并且游标是共享的，对不同的调用都将使用相同的执行计划。如果不同的调用使用不同的执行计划更有效，那么在SQL语句中使用绑定变量就不合适。

另外，绑定变量窥视已经被熟知在RAC的不同节点中会导致生成不同的执行计划，这是因为每个节点都有属于它自己的共享池。尽管相同的SQL，数据与统计信息，在SQL语句使用不同的绑定变量第一次在每个节点被解析时，对于每个节点将会选择不同的执行计划。

隐含参数_optim_peek_user_binds用来控制绑定变量窥视这个功能是否启用，它的缺省值是true。虽然禁用绑定变量窥视可以得到一个稳定的执行计划，但必须要了解对于所有绑定变量使用一种稳定的执行计划是否是最佳的选择。比如一个表有10000行记录并且col1列存在索引。
SELECT
FROM tablex
WHERE col1 BETWEEN :bind1 AND :bind2;

如果执行这个SQL，使用值123与124来从10000行记录中检索2行记录，那么使用索引绝对是最明智的选择。然而，如果使用绑定变量值123与9999来执行相同的SQL，那么查询将要检索表中绝大多数记录并且选择全表扫描应该是最合适的，但优化器不能意识到这一点，不会因此改变执行计划。

解决方法
要解决因绑定变量窥视而造成的SQL变慢的问题最好的方法是使用Oracle 11g引入的自适就游标共享，这个技术允许优化器对于不同的绑定变量值使用不同的执行计划。如果自适应游标不可以使用，一种可能的解决方法是修改应用程序并且对于上面的语句有两个单独的
模块/部分来表示，但这种改变(使用hint来修改)将会生成你预期的执行计划。因此编码绑定变量或可以使用cursor_sharing设置为similar或force是适合的，但必须要认识到绑定变量窥视基于在硬解析时提供给优化器的一组绑定变量值而生成非你预期的执行计划。使用hint与编码应用程序来允许使用合适版本的SQL或使用literal值来获得更优的执行计划。

为了禁用绑定变量窥视将_optim_peek_user_binds在spfile/pfile或会话中设置为false:
alter session set “_OPTIM_PEEK_USER_BINDS”=FALSE;

下面通过一个例子来理解绑定变量窥视

SQL> create table t1(t_id number(20),t_meal varchar2(20));

Table created.

SQL>begin
    for i in 1 .. 79998 loop
     insert into t1 values(i,'Mansaf');
    end loop;
     insert into t1 values(79999,'Kabab');
     insert into t1 values(80000,'Pasta');
     commit;
    end;
    /
PL/SQL procedure successfully completed.


SQL> create index idx_t1 on t1(t_meal);

Index created.

SQL> exec dbms_stats.gather_table_stats

(ownname=>null,tabname=>'T1',estimate_percent=>100,method_opt=>'for all indexed columns 

size auto',cascade=>true);

PL/SQL procedure successfully completed.

SQL> show parameter cursor_sharing

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT

cursor_sharing参数为exact，这意味着只要查询的literal值不同游标就不能共享

SQL> select count(*) from t1 where t_meal='Mansaf';

  COUNT(*)
----------
     79998

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  41wjq1qnk92wd, child number 0
-------------------------------------
An uncaught error happened in prepare_sql_statement : ORA-01403: no data found

Plan hash value: 2101382132

--------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |       |       |    44 (100)|          |
|   1 |  SORT AGGREGATE       |        |     1 |     7 |            |          |
|*  2 |   INDEX FAST FULL SCAN| IDX_T1 | 79998 |   546K|    44   (5)| 00:00:01 |
--------------------------------------------------------------------------------

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

   2 - filter("T_MEAL"='Mansaf')


19 rows selected.

从上面的执计划可以看到执行计划使用了index fast full scan。

下面使用绑定变量来执行

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

PL/SQL procedure successfully completed.

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

  COUNT(*)
----------
     79998

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  42s7s1vzsrbwk, child number 0
-------------------------------------
select count(*) from t1 where t_meal=:x

Plan hash value: 2101382132

--------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |       |       |    44 (100)|          |
|   1 |  SORT AGGREGATE       |        |     1 |     7 |            |          |
|*  2 |   INDEX FAST FULL SCAN| IDX_T1 | 79998 |   546K|    44   (5)| 00:00:01 |
--------------------------------------------------------------------------------

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

   2 - filter("T_MEAL"=:X)


19 rows selected.

当使用绑定变量后语义相同的语句的SQL_ID发生了改变。执行计划是使用index fast full scan

SQL> exec :x:='Pasta';

PL/SQL procedure successfully completed.

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

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

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  42s7s1vzsrbwk, child number 0
-------------------------------------
An uncaught error happened in prepare_sql_statement : ORA-01403: no data found

Plan hash value: 2101382132

--------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE       |        |     1 |     7 |            |          |
|*  2 |   INDEX FAST FULL SCAN| IDX_T1 |     1 |     7 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

   2 - access("T_MEAL"=:X)


19 rows selected.

当只检索一条记录时，因为查询语句相同，游标共享并且即使绑定变量值不同还是使用了第一次生成的执行计划，这时该执行计划对于这个绑定变量值来说不是最佳执行计划。

如果想要使游标失效并重新生成执行计划，有以下几种方法使游标失效。
1.执行alter system flush shared_pool;
2.删除或修改游标所引用对象的统计信息
3.对游标所引用的对象授予或回收相关权限
4.修改游标所引用对象
5.重启实例
6.使用dummy hint来改变语句的文本

这里使用dummy hint来改变语句的文本

SQL> select /*+ Hard parse me please */ count(*) from t1 where t_meal=:x;

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

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  crggg37d7jmrg, child number 0
-------------------------------------
select /*+ Hard parse me please */ count(*) from t1 where t_meal=:x

Plan hash value: 1970818898

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

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

   2 - access("T_MEAL"=:X)


19 rows selected.

从执行计划可以看到SQL_ID也发生了改变,确实重新生成了执行计划

这篇文章主要介绍通过dbms_sqldiag来解决与SQL相关的各种问题。dbms_sqldiag是与标准版本数据库软件一起发布，使用它并不需要额外的许可。
dbms_sqldiag可以用于以下问题类型的诊断:
.problem_type_performance 怀疑是性能问题
.problem_type_wrong_results 怀疑查询返回了不一致的结果
.problem_type_compilation_error 在编译时的错误
.problem_type_execution_error 在执行时的错误

诊断problem_type_performance
执行查询并将该语句来作为诊断的SQL语句

SQL> set timing on
SQL> set autotrace traceonly
SQL> select * from t1 where c1=500000;

Elapsed: 00:00:03.43

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

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

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

   1 - filter(TO_NUMBER("C1")=500000)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1646  consistent gets
          0  physical reads
          0  redo size
        420  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


SQL> select sql_text,sql_id from v$sqlarea where sql_text like 'select * from t1 where c1=500000';
 
SQL_TEXT                                                                         SQL_ID
-------------------------------------------------------------------------------- -------------
select * from t1 where c1=500000                                                 456naq6s2fcpq

创建诊断任务

SQL> set echo on
SQL> set linesize 132
SQL> set pagesize 999
SQL> set long 999999
SQL> set serveroutput on
SQL> declare
  2  v_sql_diag_task_id varchar2(100);
  3  begin
  4  --
  5  -- create diagnostic task
  6  --
  7  v_sql_diag_task_id := dbms_sqldiag.create_diagnosis_task (
  8  sql_id=>'456naq6s2fcpq',
  9  problem_type => dbms_sqldiag.problem_type_performance,
 10  time_limit => 900,
 11  task_name => 'problem_type_performance_task' );
 12  --
 13  -- setup parameters for the task to give verbose output
 14  --
 15  dbms_sqltune.set_tuning_task_parameter(v_sql_diag_task_id,'_sqldiag_finding_mode',dbms_sqldiag.sqldiag_findings_filter_plans);
 16  end;
 17  /

PL/SQL procedure successfully completed.

有时sql_id因为一些原因可能在v$sql视图中找不到，因此在这时就需要使用sql_text来代替sql_id,用户想要执行诊断任务必须至少有advisor权限。task_name作为唯一键使用并且在相同用户使用相同任务名之前必须要删除。

检查任务是否创建成功

SQL> select distinct owner, task_name, advisor_name
from dba_advisor_tasks where advisor_name ='SQL Repair Advisor' and task_name like '%perf%'order by 1;  

OWNER                          TASK_NAME                      ADVISOR_NAME
------------------------------ ------------------------------ ------------------------------
SYS                            problem_type_performance_task  SQL Repair Advisor      

执行论断任务

SQL> exec dbms_sqldiag.execute_diagnosis_task (task_name => 'problem_type_performance_task' );

PL/SQL procedure successfully completed.

生成报告

SQL> select dbms_sqldiag.report_diagnosis_task ('problem_type_performance_task' ) as recommendations from dual;

RECOMMENDATIONS
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : problem_type_performance_task
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 900
Completion Status  : COMPLETED
Started at         : 05/30/2016 10:20:17
Completed at       : 05/30/2016 10:20:22

-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID     : 456naq6s2fcpq
SQL Text   : select * from t1 where c1=500000

-------------------------------------------------------------------------------
No SQL patch was found to resolve the problem.

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

这上面的这个例子中，没有得到任何patch建议。然而，如果得到了patch，可以执行以下命令来接受patch

begin
 dbms_sqldiag.accept_sql_patch(
   task_name =>'problem_type_performance_task', 
   task_owner => 'sys', 
   replace => true);
end;

验证SQL Patch是否启用

SQL> SELECT name, status FROM dba_sql_patches WHERE name LIKE '%SYS%';
 
NAME                           STATUS
------------------------------ --------
SYS_SQLPTCH_                   ENABLED

删除任务

SQL> exec dbms_sqldiag.drop_diagnosis_task('problem_type_performance_task');

PL/SQL procedure successfully completed.

SQL> select distinct owner, task_name, advisor_name
  2  from dba_advisor_tasks where advisor_name ='SQL Repair Advisor' and task_name like '%perf%'order by 1;
 
OWNER                          TASK_NAME                      ADVISOR_NAME
------------------------------ ------------------------------ ------------------------------

删除SQL Patch

--- find the name of the sql patch
---
select name, status from dba_sql_patches where name like '%sys%';


---drop the sql patch.
---replace following patch name with actual name of the sql patch
--- from previous query output.
exec  dbms_sqldiag.drop_sql_patch (name=> 'sys_sqlptch_');
--- verify that the sql patch has been dropped.

select name, status from dba_sql_patches where name like '%sys%';

诊断problem_type_wrong_results
创建两个测试表

SQL> create table a_test
  2  (
  3  id number not null,
  4  clss number not null
  5  );

Table created.

SQL> create table as_test
  2  (
  3  as_id number
  4  );

Table created.

SQL> insert into a_test values(11,5);

1 row created.

SQL> insert into a_test values(1,5);

1 row created.

SQL> insert into as_test values(11);

1 row created.

SQL> commit;

Commit complete.

错误结果–返回0行记录

SQL> select 'working' as is_working,id
from   a_test a,
  2    3         as_test asi
  4  where  a.id=asi.as_id(+)
  5  and    a.clss in (1,3,4,5)
  6  and    a.clss = '5';

IS_WORK         ID
------- ----------
                                                                                               
SQL> select sql_id, sql_text from v$sql where sql_text like 'select%as is_working%';
 
SQL_ID        SQL_TEXT
------------- --------------------------------------------------------------------------------
9a15z3d14krcm select 'working' as is_working,id from   a_test a,        as_test asi where  a.i

正确结果–返回2行记录

SQL> select 'working' as is_working,id
from   a_test a,
  2    3         as_test asi
  4  where  a.id=asi.as_id(+)
  5  and    a.clss in (1,3,4,5)
  6  and    a.clss = '5';

IS_WORK         ID
------- ----------
working         11
working          1

SQL> set echo on lines 132 pages 999 long 20000 serveroutput on;
SQL> declare
  2  l_sql_diag_task_id  varchar2(100); 
  3         
  4  begin
  5  --
  6  -- create diagnostic task
  7  --
  8      l_sql_diag_task_id :=  dbms_sqldiag.create_diagnosis_task (
  9        sql_id => '9a15z3d14krcm',
 10        problem_type => dbms_sqldiag.problem_type_wrong_results, 
 11        task_name => 'test_wr_diagnostic_task' );
 12   
 13  --
 14  -- setup parameters for the task to give verbose output
 15  --
 16      dbms_sqltune.set_tuning_task_parameter(
 17        l_sql_diag_task_id,
 18        '_sqldiag_finding_mode',
 19        dbms_sqldiag.sqldiag_findings_filter_plans);
 20  end;
 21  /

PL/SQL procedure successfully completed.

SQL> exec dbms_sqldiag.execute_diagnosis_task ( task_name  => 'test_wr_diagnostic_task');

PL/SQL procedure successfully completed.


SQL> select dbms_sqldiag.report_diagnosis_task ('test_wr_diagnostic_task') as recommendations  from dual;


RECOMMENDATIONS
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : test_wr_diagnostic_task
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 05/30/2016 10:46:22
Completed at       : 05/30/2016 10:46:24

-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID     : 9a15z3d14krcm
SQL Text   : select 'working' as is_working,id
             from   a_test a,
                    as_test asi
             where  a.id=asi.as_id(+)
             and    a.clss in (1,3,4,5)
             and    a.clss = '5'

...省略...

诊断PROBLEM_TYPE_COMPILATION_ERROR & PROBLEM_TYPE_EXECUTION_ERROR
创建诊断任务

set echo on
set linesize 132
set pagesize 999
set long 999999
set serveroutput on

declare

v_sql_diag_task_id varchar2(100);


begin
---
--- create a diagnostic task. use any name you want under task_name argument.
---
v_sql_diag_task_id := dbms_sqldiag.create_diagnosis_task (
sql_text => 'select distinct min(src.cf_table_group)
from ( select pc.cf_table_group, min(pc.cf_prg_next_run)
from testcase1 pc where nvl(pc.cf_prg_next_run, sysdate) < = sysdate
group by pc.cf_table_group
order by nvl(min(pc.cf_prg_next_run),sysdate) ) src',
problem_type => dbms_sqldiag.problem_type_execution_error,
time_limit => 3600,
task_name => 'error_diagnostic_task' );
--
-- setup parameters for the task to give verbose output
--
dbms_sqltune.set_tuning_task_parameter(v_sql_diag_task_id,'_sqldiag_finding_mode',dbms_sqldiag.sqldiag_findings_filter_plans);


end;

执行诊断任务

exec dbms_sqldiag.execute_diagnosis_task ( task_name => 'error_diagnostic_task' );

生成报告

set long 9999999
select dbms_sqldiag.report_diagnosis_task ('error_diagnostic_task' ) as recommendations from dual;

recommendations
--------------------------------------------------------------------------------
general information section
-------------------------------------------------------------------------------
tuning task name : error_diagnostic_task
tuning task owner : tc2533
workload type : single sql statement
scope : comprehensive
time limit(seconds): 3600
completion status : completed
started at : 10/27/2011 22:35:07
completed at : 10/27/2011 22:35:07

-------------------------------------------------------------------------------
schema name: tc2533
sql id : 4k1tdq940wvpk
sql text : select distinct min(src.cf_table_group)
from ( select pc.cf_table_group, min(pc.cf_prg_next_run)
from testcase1 pc where nvl(pc.cf_prg_next_run, sysdate) < =
sysdate
group by pc.cf_table_group
order by nvl(min(pc.cf_prg_next_run),sysdate) ) src

-------------------------------------------------------------------------------
findings section (1 finding)
-------------------------------------------------------------------------------

1- sql patch finding (see explain plans section below)
------------------------------------------------------
a potentially better execution plan was found for this statement.

recommendation
--------------
- consider accepting the recommended sql patch.
execute dbms_sqldiag.accept_sql_patch(task_name =>
'error_diagnostic_task', task_owner => 'tc2533', replace => true);

rationale
---------
recommended plan with hash value 3673393522 has number of rows 1, check
sum 2342552567, execution time 0 and 6 buffer gets

接受建议

execute dbms_sqldiag.accept_sql_patch(task_name =>'error_diagnostic_task', task_owner => 'tc2533', replace => true);

删除诊断任务

exec dbms_sqldiag.drop_diagnosis_task ( task_name => 'error_diagnostic_task' );

基数是CBO通过行资源或行资源组合评估出来的操作将产生的行数。在有些情况下，结果集的基数可能被评估错误。最常见的是使用复杂谓词且统计信息不能精确反映出谓词相关联的数据。例如:select ename from emp where sal >= 25000 and job = ‘PRESIDENT’;
在这个例子中，在sal与job列中的数据存在一种隐藏的关联。只有董事长才会挣的比$25,000多。没有其它的员工满足sal>=25000。优化器没有办法检查这种关联且会将这两个列单独对待。这将会对谓词产生一种不理解的选择率以及不精确的基数评估。

在Oracle 11g及以上版本通过对一组列创建扩展统计信息来解决这种问题。

SQL> create table t1(t_name varchar2(20) not null,t_country varchar2(20) not null,t_town varchar2(20) not null);

Table created.

SQL> desc t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 T_NAME                                    NOT NULL VARCHAR2(20)
 T_COUNTRY                                 NOT NULL VARCHAR2(20)
 T_TOWN                                    NOT NULL VARCHAR2(20)


SQL> begin
  2   for i in 1..50 loop
  3       insert into t1 values('JY','CHINA','QIHE');  
  4   end loop;
  5   for r in 51 .. 400 loop
  6     insert into t1 values('A'||r,'USA','NewYork');  
  7   end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

收集表t1的统计信息

SQL> exec dbms_stats.gather_table_stats(ownname=>'JY',tabname=>'T1',estimate_percent=>100,method_opt=>'for all columns sizeauto',cascade=>true);

PL/SQL procedure successfully completed.

查询t_name=’JY’且t_country=’CHINA’的记录为50

SQL> select count(*) from t1 where t_name='JY' and t_country='CHINA';

  COUNT(*)
----------
        50

执行查询发现优化器评估的基数为7,与50相差很远

SQL> select * from t1 where t_name='JY' and t_country='CHINA';

50 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     7 |   119 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     7 |   119 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

创建组列

SQL> declare
  2  cg_name varchar2(30);
  3  begin
  4   cg_name :=  sys.dbms_stats.create_extended_stats(null,'t1', '(t_name,t_country)');
  5  end;
  6  /  

PL/SQL procedure successfully completed.

对创建的组列收集统计信息

SQL> exec dbms_stats.gather_table_stats(null,'t1',estimate_percent=>100,method_opt=>'for columns(t_name,t_country) size skewonly');

PL/SQL procedure successfully completed.


SQL> SELECT extension_name, extension FROM user_stat_extensions WHERE table_name='T1';


EXTENSION_NAME                 EXTENSION
------------------------------ --------------------------------------------------------------------------------
SYS_STUVJ3HB84$ZIMSQJE_3AMWZ_9 ("T_NAME","T_COUNTRY")


SQL> SELECT e.extension col_group, t.num_distinct, t.histogram
  2  FROM user_stat_extensions e, user_tab_col_statistics t
  3  WHERE e.extension_name=t.column_name
  4  AND e.table_name = t.table_name
  5  AND t.table_name='T1';

COL_GROUP                                                                        NUM_DISTINCT HISTOGRAM
-------------------------------------------------------------------------------- ------------ ---------------
("T_NAME","T_COUNTRY")                                                                    351 HEIGHT BALANCED

创建组列收集扩展统计信息后，其评估基数为47与实际的50相差很小，对于估算已经是很准确了

SQL> select * from t1 where t_name='JY' and t_country='CHINA';

50 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

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

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

   1 - filter("T_COUNTRY"='CHINA' AND "T_NAME"='JY')


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
         12  consistent gets
          0  physical reads
          0  redo size
       1224  bytes sent via SQL*Net to client
        452  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         50  rows processed

对于列之间存在关联关系的列创建Column Group Statistics可以让优化器进行更精确的基数评估从而选择最优执行计划来提高性能

SQL Profiles是在Oracle 10g中就引入的，并且通过dbms_sqltune包或EM来进行管理是自动SQL调整进程的一部分。

自动SQL调整
查询优化器会有时会因为缺少信息而对语句的一个属性产生不精确的评估，进而导致低效的执行计划传统上来说，用户不得不通过手动增加hint到代码中来修改这个剖从而指导优化器产生正确的决定。对于第三方应用程序，改变应用代码是不现实的。

自动SQL调整使用SQL Profile来处理这个问题。自动调整优化器将会为SQL语句创建一个SQL Profile，它是由语句相关的辅助统计信息组成。查询优化器在正常模式下对基数，选择率与成本的评估有时会有严重的差异而导致生成低效的执行计划。SQL Profile通过收集额外的信息，比如使用抽样与特定的执行技术来调整评估可以用来解决这个问题。

在自动SQL调整时，优化器也会使用SQL语句的历史执行信息来合理设置优化器参数，比如改变optimizer_mode，将其参数从all_rows改成first_rows。

分析报告的输出建议接受SQL Profile。一旦接受SQL Profile它将会永久存储在数据字典中。一个SQL Profile被指定到一个特定的查询。如果接受，优化器在正常模式下使用SQL Profile中的信息与常规的数据库统计信息来对SQL进行解析。SQL Profile所提供的额外信息可以用来生成更好的执行计划。

SQL Profile
SQL Profile是存储在数据字典中的信息集合能让查询优化器为SQL语句创建一个最优执行计划。SQL Profile包含了自动SQL调整所发现的低效优化器评估的修正。这种信息能提高优化器对基数与选择率评估精确度，从而导致优化器选择一个更好的执行计划。

SQL Profile不包含单独的执行计划，当选择执行计划时优化器有以下信息源:
1.环境，包含数据库配置，绑定变量值，统计信息，数据集等等
2.SQL Profile所提供了附加统计信息

重要的是SQL Profile不会冻结一个SQL语句的执行计划，这一点与stored outlines不一样。当表记录增加或增加与删除索引时，使用相同的SQL Profile执行计划也会发生改变。当数据分布或相关语句的访问路径发生改变，SQL Profile中存储的信息仍然与SQL语关联。然而，随着时间的推移，SQL Profile的内容将会过时并且不得不重新生成。可以再次执行自动SQL调整来重新生成SQL Profile。

如何控制SQL Profile的使用范围
SQL Profile的使用范围可以由CATEGORY属性来进行控制。这个属性决定那个用户会话可以应用这个SQL Profile。可以通过查询dba_sql_profiles视图的category列来查看SQL Profile的category属性

SQL> select name,category from dba_sql_profiles;
 
NAME                           CATEGORY
------------------------------ ------------------------------
SYS_SQLPROF_0152b233d518c007   DEFAULT
SYS_SQLPROF_015470e31c248001   DEFAULT
coe_bcyatm4910qb1_725332378    DEFAULT
coe_3yy1wbuvsxm93_1849931106   DEFAULT
SYS_SQLPROF_0152b11b33e6c006   DEFAULT
coe_a69pw2vj989zm_3709683508   DEFAULT
SYS_SQLPROF_0151ed60f3d28000   DEFAULT
coe_6rfqq1bjwcdx9_1360313219   DEFAULT
SYS_SQLPROF_0152b33048a8c009   DEFAULT
coe_36cbabzyq13gy_1849931106   DEFAULT
SYS_SQLPROF_015470e298fd0000   DEFAULT
SYS_SQLPROF_0152b0a82393c003   DEFAULT
SYS_SQLPROF_0152ba15c21e800b   DEFAULT
coe_6rfqq1bjwcdx9_1360313219_1 DEFAULT

缺省情况下，所有SQL Profile都是创建在DEFAULT目录中。这意味着当sqltune_category设置为default时所有的用户会话都能使用这个SQL Profile。

SQL> show parameter sqltune_category
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sqltune_category                     string      DEFAULT

通过修改SQL Profile的category属性，可以决定那个会话将使用所创建的SQL Profile。例如，通过将一个SQL Profile的category属性设置为DEV，那么只有当sqltune_category设置为DEV时这些会话才能使用这个SQL Profile。所有其它的会话将不能访问这个SQL Profile并且SQL语句的执行计划将不会受这个SQL Profile的影响。这种技术能在SQL Profile被其它会话使用之前让你在一个受限的环境下测试SQL Profile。

SQL Profile可以应用的语句类型
.select语句
.update语句
.insert语句(只包含select子句)
.delete语句
.create table语句(只包含as select子句)
.merge语句(update或insert操作)

SQL Profile的管理
SQL Profile可以通过EM或dbms_sqltune来进行管理

为了使用dbms_sqltune来管理SQL Profile，用户必须有create any sql_profile,drop any sql_profile与alter any sql_profile系统权限。

接受SQL Profile
使用dbms_sqltune.accept_sql_profile过程来接受由SQL调整指导所创建的SQL Profile。

DECLARE
 my_sqlprofile_name VARCHAR2(30);
 BEGIN
 my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE ( 
 task_name => 'my_sql_tuning_task',
 name => 'my_sql_profile');
 END;

my_sql_tuning_task是SQL调整任务的名称，可以查询dba_sql_profiles视图来查看SQL Profile的信息

修改SQL Profile
使用dbms_sqltune.alter_sql_profile过程可以用来修改现有SQL Profile的status,name,description与category属性

BEGIN
 DBMS_SQLTUNE.ALTER_SQL_PROFILE(
 name => 'my_sql_profile', 
 attribute_name => 'STATUS', 
 value => 'DISABLED');
 END;
 /

在这个例子中，要修改名为my_sql_profile的SQL Profile，将它的status属性修改为disable这将意味着这个SQL Profile将不能在SQL编译时使用了。

删除SQL Profile
可以使用dbms_sqltune.drop_sql_profile过程来删除SQL Profile

begin
 DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'my_sql_profile');
 end;
 /

下面介绍使用SQL Profile来优化SQL语句的例子
会话1

SQL> create table test(n number);

Table created.

SQL> declare
  2             begin
  3              for i in 1 .. 10000 loop
  4                  insert into test values(i);
  5                  commit;
  6              end loop;
  7             end;
  8   /

PL/SQL procedure successfully completed.

SQL> create index test_idx on test(n);

Index created.

SQL> exec dbms_stats.gather_table_stats('','TEST');

PL/SQL procedure successfully completed.

SQL> set autotrace on
SQL> select /*+ no_index(test test_idx) */ * from test where n=1;

         N
----------
         1


Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     4 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |     4 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("N"=1)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         23  consistent gets
          0  physical reads
          0  redo size
        415  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

会话2
创建一个SQL自动调整任务并运行报告调整任务并接受建议的SQL Profile

SQL> declare
  2     my_task_name VARCHAR2(30);
  3     my_sqltext CLOB;
  4     begin
  5        my_sqltext := 'select /*+ no_index(test test_idx) */ * from test where n=1';
  6        my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
  7        sql_text => my_sqltext,
  8        user_name => 'SCOTT',
  9        scope => 'COMPREHENSIVE',
 10        time_limit => 60,
 11        task_name => 'my_sql_tuning_task_1',
 12        description => 'Task to tune a query on a specified table');
 13   end;
 14   /

PL/SQL procedure successfully completed.

SQL>  begin
  2   DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task_1');
  3   end;
  4   /

PL/SQL procedure successfully completed.

SQL> set long 10000
SQL> set longchunksize 1000
SQL> set linesize 100
SQL> set heading off
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_1') from DUAL;
set heading on
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : my_sql_tuning_task_1
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 05/27/2016 16:58:11
Completed at       : 05/27/2016 16:58:28

-------------------------------------------------------------------------------
Schema Name: SCOTT

SQL ID     : d4wgpc5g0s0vu
SQL Text   : select /*+ no_index(test test_idx) */ * from test where n=1

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 90.91%)
  ------------------------------------------

  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name =>
            'my_sql_tuning_task_1', task_owner => 'SYS', replace => TRUE);

  Validation results
  ------------------
  The SQL profile was tested by executing both its plan and the original plan
  and measuring their respective execution statistics. A plan may have been
  only partially executed if the other could be run to completion in less time.

                           Original Plan  With SQL Profile  % Improved
                           -------------  ----------------  ----------
  Completion Status:            COMPLETE          COMPLETE

  Elapsed Time (s):             .003464           .000405       88.3 %
  CPU Time (s):                 .003399           .000299       91.2 %
  User I/O Time (s):                  0                 0
  Buffer Gets:                       22                 2       90.9 %
  Physical Read Requests:             0                 0
  Physical Write Requests:            0                 0
  Physical Read Bytes:                0                 0
  Physical Write Bytes:               0                 0
  Rows Processed:                     1                 1
  Fetches:                            1                 1
  Executions:                         1                 1

  Notes

  -----
  1. Statistics for the original plan were averaged over 10 executions.
  2. Statistics for the SQL profile plan were averaged over 10 executions.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original With Adjusted Cost
------------------------------
Plan hash value: 1357081020

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     4 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |     4 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("N"=1)

2- Using SQL Profile
--------------------

Plan hash value: 2882402178

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

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

   1 - access("N"=1)


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

SQL> DECLARE
  2   my_sqlprofile_name VARCHAR2(30);
  3   begin
  4   my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
  5   task_name => 'my_sql_tuning_task_1',
  6   name => 'my_sql_profile',
  7   force_match => true,
  8   replace =>true );
  9   end;
 10  /

PL/SQL procedure successfully completed.

会话1

SQL> set autotrace on
SQL> select /*+ no_index(test test_idx) */ * from test where n=1;

         N
----------
         1


Execution Plan
----------------------------------------------------------
Plan hash value: 2882402178

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

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

   1 - access("N"=1)

Note
-----
   - SQL profile "my_sql_profile" used for this statement


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
         13  consistent gets
          1  physical reads
          0  redo size
        415  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

可以看到虽然我们指定了no_index来让优化器不使用索引test_idx,但由于使用了SQL Profile还是使用索引test_idx,通过SQL Profile改变了SQL语句的执行计划。

朋友客户的数据库由于磁盘损坏，从存储级别抽取出了RMAN的备份文件，但没有抽取出数据文件，联机重做日志，控制文件，参数文件等数据库文件。但抽取出来的备份文件中有数据文件，控制文件，参数文件的备份，归档重做日志备份因为没有磁盘空间没有执行完成。新安装Oracle软件后创建参数文件来使用RMAN备份来还原和恢复数据库。但在还原数据文件时出错，错误如下所示:

RMAN> run
2> {
3> allocate channel t1 device type disk;
4> restore controlfile from 'E:\ORABACK\ORCLBPM_9BQ7K30M_20150522.BAK';
5> alter database mount;
6> release channel t1;
7> }

使用目标数据库控制文件替代恢复目录
分配的通道: t1
通道 t1: SID=572 设备类型=DISK

启动 restore 于 29-5月 -15

通道 t1: 正在还原控制文件
通道 t1: 还原完成, 用时: 00:00:03
输出文件名=D:\ORACLE\ORADATA\ORCLBPM\CONTROL01.CTL
输出文件名=D:\ORACLE\ORADATA\ORCLBPM\CONTROL02.CTL
输出文件名=D:\ORACLE\ORADATA\ORCLBPM\CONTROL03.CTL
完成 restore 于 29-5月 -15

数据库已装载

释放的通道: t1

RMAN> run
2> {
3> allocate channel t1 device type disk;
4> restore database;
5> recover database;
6> release channel t1;
7> }

分配的通道: t1
通道 t1: SID=572 设备类型=DISK

启动 restore 于 29-5月 -15
启动 implicit crosscheck backup 于 29-5月 -15
已交叉检验的 200 对象
完成 implicit crosscheck backup 于 29-5月 -15

启动 implicit crosscheck copy 于 29-5月 -15
完成 implicit crosscheck copy 于 29-5月 -15

搜索恢复区中的所有文件
正在编制文件目录...
没有为文件编制目录


通道 t1: 正在开始还原数据文件备份集
通道 t1: 正在指定从备份集还原的数据文件
通道 t1: 将数据文件 00001 还原到 D:\ORACLE\ORADATA\ORCLBPM\SYSTEM01.DBF
通道 t1: 将数据文件 00002 还原到 D:\ORACLE\ORADATA\ORCLBPM\SYSAUX01.DBF
通道 t1: 将数据文件 00003 还原到 D:\ORACLE\ORADATA\ORCLBPM\UNDOTBS01.DBF
通道 t1: 将数据文件 00004 还原到 D:\ORACLE\ORADATA\ORCLBPM\USERS01.DBF
通道 t1: 将数据文件 00005 还原到 D:\ORACLE\ORADATA\ORCLBPM\HB_SY01.DBF
通道 t1: 将数据文件 00006 还原到 D:\ORACLE\ORADATA\ORCLBPM\HB_SY02.DBF
通道 t1: 将数据文件 00007 还原到 D:\ORACLE\ORADATA\ORCLBPM\HB_SY03.DBF
通道 t1: 将数据文件 00008 还原到 D:\ORACLE\ORADATA\ORCLBPM\BPMSY01.DBF
通道 t1: 将数据文件 00009 还原到 D:\ORACLE\ORADATA\ORCLBPM\HMBPM01.DBF
通道 t1: 将数据文件 00010 还原到 D:\ORACLE\ORADATA\ORCLBPM\BLOCK01.DBF
通道 t1: 正在读取备份片段 E:\ORABACK\ORCLBPM_9AQ7K1NM_20150522.BAK
通道 t1: ORA-19870: 还原备份片段 E:\ORABACK\ORCLBPM_9AQ7K1NM_20150522.BAK 时出错
ORA-19612: 数据文件 1 没有还原, 因为 missing or corrupt data

故障转移到上一个备份

释放的通道: t1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: restore 命令 (在 05/29/2015 17:03:44 上) 失败
RMAN-06026: 有些目标没有找到 - 终止还原
RMAN-06023: 没有找到数据文件1的副本来还原

从错误信息： ORA-19870: 还原备份片段 E:\ORABACK\ORCLBPM_9AQ7K1NM_20150522.BAK 时出错，ORA-19612: 数据文件 1 没有还原, 因为 missing or corrupt data可以知道在执行还原数据文件1时备份片段丢失或有坏块，这里明显是有坏块，因为其它数据文件使用该备份片段已经还原成功了。这里通过设置事件让RMAN跳过坏块来还原数据文件。
C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期五 5月 29 18:02:22 2015

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

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system set event=’19548 trace name context forever’, ‘19549 trace name
context forever’ scope=spfile;

系统已更改。

SQL> shutdown immediate
ORA-01109: 数据库未打开

已经卸载数据库。
ORACLE 例程已经关闭。

SQL> startup mount
ORACLE 例程已经启动。

Total System Global Area 7666352128 bytes
Fixed Size 2190736 bytes
Variable Size 3942646384 bytes
Database Buffers 3707764736 bytes
Redo Buffers 13750272 bytes
数据库装载完毕。

RMAN> restore database;

启动 restore 于 29-5月 -15
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=1141 设备类型=DISK

正在略过数据文件 2; 已还原到文件 D:\ORACLE\ORADATA\ORCLBPM\SYSAUX01.DBF
正在略过数据文件 3; 已还原到文件 D:\ORACLE\ORADATA\ORCLBPM\UNDOTBS01.DBF
正在略过数据文件 4; 已还原到文件 D:\ORACLE\ORADATA\ORCLBPM\USERS01.DBF
正在略过数据文件 5; 已还原到文件 D:\ORACLE\ORADATA\ORCLBPM\HB_SY01.DBF
正在略过数据文件 6; 已还原到文件 D:\ORACLE\ORADATA\ORCLBPM\HB_SY02.DBF
正在略过数据文件 8; 已还原到文件 D:\ORACLE\ORADATA\ORCLBPM\BPMSY01.DBF
正在略过数据文件 9; 已还原到文件 D:\ORACLE\ORADATA\ORCLBPM\HMBPM01.DBF
正在略过数据文件 10; 已还原到文件 D:\ORACLE\ORADATA\ORCLBPM\BLOCK01.DBF
通道 ORA_DISK_1: 正在开始还原数据文件备份集
通道 ORA_DISK_1: 正在指定从备份集还原的数据文件
通道 ORA_DISK_1: 将数据文件 00001 还原到 D:\ORACLE\ORADATA\ORCLBPM\SYSTEM01.DBF
通道 ORA_DISK_1: 将数据文件 00007 还原到 D:\ORACLE\ORADATA\ORCLBPM\HB_SY03.DBF
通道 ORA_DISK_1: 正在读取备份片段 E:\ORABACK\ORCLBPM_9AQ7K1NM_20150522.BAK
通道 ORA_DISK_1: 段句柄 = E:\ORABACK\ORCLBPM_9AQ7K1NM_20150522.BAK 标记 = TAG2015052
通道 ORA_DISK_1: 已还原备份片段 1
通道 ORA_DISK_1: 还原完成, 用时: 00:02:45
完成 restore 于 29-5月 -15

下面对之前还原报错的数据文件SYSTEM01.DBF，HB_SY03.DBF进行验证
C:\Users\Administrator>dbv file=D:\ORACLE\ORADATA\ORCLBPM\SYSTEM01.DBF blocksize
=8192

DBVERIFY: Release 11.2.0.1.0 – Production on 星期五 5月 29 18:29:19 2015

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

DBVERIFY – 开始验证: FILE = D:\ORACLE\ORADATA\ORCLBPM\SYSTEM01.DBF

DBVERIFY – 验证完成

检查的页总数: 280064
处理的页总数 (数据): 242009
失败的页总数 (数据): 0
处理的页总数 (索引): 13233
失败的页总数 (索引): 0
处理的页总数 (其他): 3322
处理的总页数 (段) : 1
失败的总页数 (段) : 0
空的页总数: 21500
标记为损坏的总页数: 0
流入的页总数: 0
加密的总页数 : 0
最高块 SCN : 87501555 (0.87501555)

C:\Users\Administrator>dbv file=D:\ORACLE\ORADATA\ORCLBPM\HB_SY03.DBF blocksize=
8192

DBVERIFY: Release 11.2.0.1.0 – Production on 星期五 5月 29 18:30:35 2015

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

DBVERIFY – 开始验证: FILE = D:\ORACLE\ORADATA\ORCLBPM\HB_SY03.DBF

DBVERIFY – 验证完成

检查的页总数: 524288
处理的页总数 (数据): 256890
失败的页总数 (数据): 0
处理的页总数 (索引): 52837
失败的页总数 (索引): 0
处理的页总数 (其他): 196657
处理的总页数 (段) : 0
失败的总页数 (段) : 0
空的页总数: 17904
标记为损坏的总页数: 0
流入的页总数: 0
加密的总页数 : 0
最高块 SCN : 87500838 (0.87500838)

从上面的验证可以看到没有坏块，幸运哈哈。我们来检查一个控制文件中记录的数据文件检查点SCN与数据文件头SCN是否相同，如果不同可能需要恢复。

SQL> select file#,checkpoint_change#,to_char(checkpoint_time,'yyyy-mm-dd hh24:mi
:ss') checkpoint_time from v$datafile;

     FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME
---------- ------------------ -------------------
         1           87500284 2015-05-22 23:00:42
         2           87500284 2015-05-22 23:00:42
         3           87500284 2015-05-22 23:00:42
         4           87500284 2015-05-22 23:00:42
         5           87500284 2015-05-22 23:00:42
         6           87500284 2015-05-22 23:00:42
         7           87500284 2015-05-22 23:00:42
         8           87500284 2015-05-22 23:00:42
         9           87500284 2015-05-22 23:00:42
        10           87500284 2015-05-22 23:00:42

已选择10行。

SQL> select file#,checkpoint_change#,name from v$datafile_header;

     FILE# CHECKPOINT_CHANGE# NAME
---------- ------------------ ----------------------------------
         1           87499953 D:\ORACLE\ORADATA\ORCLBPM\SYSTEM01.DBF
         2           87499953 D:\ORACLE\ORADATA\ORCLBPM\SYSAUX01.DBF
         3           87499953 D:\ORACLE\ORADATA\ORCLBPM\UNDOTBS01.DBF
         4           87499953 D:\ORACLE\ORADATA\ORCLBPM\USERS01.DBF
         5           87499953 D:\ORACLE\ORADATA\ORCLBPM\HB_SY01.DBF
         6           87499953 D:\ORACLE\ORADATA\ORCLBPM\HB_SY02.DBF
         7           87499953 D:\ORACLE\ORADATA\ORCLBPM\HB_SY03.DBF
         8           87499953 D:\ORACLE\ORADATA\ORCLBPM\BPMSY01.DBF
         9           87499953 D:\ORACLE\ORADATA\ORCLBPM\HMBPM01.DBF
        10           87499953 D:\ORACLE\ORADATA\ORCLBPM\BLOCK01.DBF


已选择10行。

可以看到所有数据文件头记录的检查点scn都一样，控制文件中记录的所有数据文件检查点scn都一样，但数据文件头检查点scn与控制文件中记录的数据文件检查点scn不一样，所以选择重建控制文件来执行恢复，这里将控制文件备份到跟踪文件中。

SQL> oradebug setmypid
已处理的语句
SQL> alter database backup controlfile to trace
  2  ;

数据库已更改。

SQL> oradebug tracefile_name
d:\app\administrator\diag\rdbms\orclbpm\orclbpm\trace\orclbpm_ora_1704.trc

下面用跟踪文件的控制文件创建语句来重新创建控制文件，这里要使用resetlogs方式来创建，因为RMAN的备份文件中没有联机重做日志文件。

SQL> shutdown immediate
ORA-01109: ??????


已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup nomount
ORACLE 例程已经启动。

Total System Global Area 7666352128 bytes
Fixed Size                  2190736 bytes
Variable Size            3942646384 bytes
Database Buffers         3707764736 bytes
Redo Buffers               13750272 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "ORCLBPM" RESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 'D:\ORACLE\ORADATA\ORCLBPM\REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
  9    GROUP 2 'D:\ORACLE\ORADATA\ORCLBPM\REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
 10    GROUP 3 'D:\ORACLE\ORADATA\ORCLBPM\REDO03.LOG'  SIZE 50M BLOCKSIZE 512
 11  -- STANDBY LOGFILE
 12  DATAFILE
 13    'D:\ORACLE\ORADATA\ORCLBPM\SYSTEM01.DBF',
 14    'D:\ORACLE\ORADATA\ORCLBPM\SYSAUX01.DBF',
 15    'D:\ORACLE\ORADATA\ORCLBPM\UNDOTBS01.DBF',
 16    'D:\ORACLE\ORADATA\ORCLBPM\USERS01.DBF',
 17    'D:\ORACLE\ORADATA\ORCLBPM\HB_SY01.DBF',
 18    'D:\ORACLE\ORADATA\ORCLBPM\HB_SY02.DBF',
 19    'D:\ORACLE\ORADATA\ORCLBPM\HB_SY03.DBF',
 20    'D:\ORACLE\ORADATA\ORCLBPM\BPMSY01.DBF',
 21    'D:\ORACLE\ORADATA\ORCLBPM\HMBPM01.DBF',
 22    'D:\ORACLE\ORADATA\ORCLBPM\BLOCK01.DBF'
 23  CHARACTER SET ZHS16GBK
 24  ;

控制文件已创建。

检查一个控制文件中记录的数据文件检查点SCN与数据文件头SCN是否相同，数据文件头检查点scn与控制文件中记录的数据文件检查点scn一样，在没有联机重做日志和归档重做日志的情况下，我们只能将数据库恢复到scn:87499953时间点，并以open resetlogs选项来open数据库

SQL> select FILE#,CHECKPOINT_CHANGE# from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1           87499953
         2           87499953
         3           87499953
         4           87499953
         5           87499953
         6           87499953
         7           87499953
         8           87499953
         9           87499953
        10           87499953

已选择10行。

SQL> select FILE#, CHECKPOINT_CHANGE#,LAST_CHANGE# from v$datafile;

     FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
         1           87499953
         2           87499953
         3           87499953
         4           87499953
         5           87499953
         6           87499953
         7           87499953
         8           87499953
         9           87499953
        10           87499953

已选择10行。

这里需要设置隐含参数_allow_resetlogs_corruption=true来open数据库。

SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;

系统已更改。

SQL> shutdown immediate
ORA-01109: ??????


已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount;
ORACLE 例程已经启动。

Total System Global Area 7666352128 bytes
Fixed Size                  2190736 bytes
Variable Size            3942646384 bytes
Database Buffers         3707764736 bytes
Redo Buffers               13750272 bytes
数据库装载完毕。
SQL> alter database open resetlogs;

数据库已更改。

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\ORACLE\ORADATA\ORCLBPM\TEMP01.DBF' R
EUSE;

表空间已更改。

SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area 7666352128 bytes
Fixed Size                  2190736 bytes
Variable Size            3942646384 bytes
Database Buffers         3707764736 bytes
Redo Buffers               13750272 bytes
数据库装载完毕。
数据库已经打开。

直此将数据库恢复到备份生成的时间点，但没有归档重做日志和联机重做日志丢失了半天的数据。

任何数据库应用程序的性能都严重依赖于它的查询执行。当没有任何用户介入时Oracle优化器是完全能够评估最优执行计划的,SQL语句的执行计划因为各种原因，比如：重新收集优化器统计信息，改变优化器参数或方案/元数据定义而可能会出现改变。但是不能保证执行计划的改变总是会提高性能，因此会导致有些管理员会锁定SQL的执行计划(Stored Outline)或锁定统计信息。然而，这样做会导致不能使用新版优化器为了提供高SQL性能所提供的一些新特性或新的访问路径。

Oracle的每一个新版本中都包含了一些新特性用来提高查询的性能。然而某些应用（或部分应用）是静态的并且比起在特定情况下提高性能来说可预测的性能是更可取的。Oracle11G提供了SPM来完全透时的控制执行计划的演变。使用SPM优化器自动管理执行计划并且确保只有已经或被验证的执行计划才会被使用。当找到SQL语句的新执行计划时，但直到它被数据库进行验证或其性能比当前执行计划高才会被使用。

SQL Plan Management是一种预防机制，记录与评估SQL语句的执行计划。这种机制可以创建一个SQL执行计划基线，它是由一组已知性能良好的执行计划组成。不管系统发生何种改变,SQL执行计划基线被用来维护相关SQL的性能，提供了执行计划的稳定性。

SPM确保SQL的性能不会因为执行计划的改变而降低。为了保证这一点，只有SQL执行计划ACCEPTED为YES才会被优化器所使用。

SPM有三个主要组件:
1.SQL执行计划基线捕获
创建SQL执行计划基线这代表对所有相关SQL语句授受这个执行计划。SQL执行计划基线被存储在SYSAUX表空间中的SQL Management BASE中的plan history中，可以通过dba_sql_plan_baselines视图进行查询。

2.SQL执行计划基线选择
确保只有ACCEPTED为YES的执行计划才会被使用，使用SQL执行计划基线并在plan history中跟踪所有新的执行计划。plan history由ACCEPTED为NO与ACCEPTED为YES的执行计划组成。ACCEPTED为NO的执行计划可能是没有被验证或者被拒绝(验证了但性能比当前使用的执行计划要差)

3.SQL执行计划基线演进
对plan history中的所有没有被验证的执行计划进行评估让其成为接受的或拒绝的执行计划

SQL执行计划基线捕获
为了让SPM起作用必须首先使用当前基于成本的执行计划来作为SQL Management Base，这将成为每个SQL语句的SQL执行计划基线。有两种不同的方法来创建SQL Management Base:
.自动捕获执行计划
.批量加载执行计划

自动捕获执行计划
自动捕获执行计划是由参数optimizer_capture_sql_plan_baselines=true来开启的(缺省值为false)当自动捕获执行计划被启用时，SPM档案库将会自动存储任何重复执行的SQL语句的执行计划。为了识别重复执行的SQL语句，优化器将会在每个SQL语句第一次被编译时进行标识，如果这个SQL语句再次被执行或编译，那么就会将这个SQL语句标记为重复执行的SQL，将会为这个SQL语句创建SQL plan history，它将包含生成的执行计划，比如:SQL文本，存储概要，绑当定变量与编译环境等信息。当前基于成本的执行计划将会作为SQL语句的第一个SQL执行计划基线并且这个执行计划的ACCEPTED会标记为YES，只有ACCEPTED为YES的执行计划才会被使用，如果将来对于这个SQL语句产生了新的执行计划，这个执行计划将会增加到plan history中并且将会被标记为验证过的。只有当它的性能比当前所使用的执行计划更好时才会将ACCEPTED标记为YES。

批量加载执行计划
批量加载执行计划在数据库从之前的版本升级致电Oracle 11g或部署新应用程序时很有用。批量加载可以与自动加载执行计划结合使用或者代替自动加载执行计划。执行计划被批量加载时会自动接受创建的新SQL执行计划基线或者向已经存在的增加一个。SQL anagement BASE可以使用三种不同的方法来批量加载执行计划。
1.从指定的SQL Tuning Set(STS)中加载执行计划

SQL> BEGIN
  2  DBMS_SQLTUNE.CREATE_SQLSET(
  3  sqlset_name => 'my_sql_tuning_set',
  4  description => 'TEST');
  5  END;
  6  /
 
PL/SQL procedure successfully completed

SQL> DECLARE
  2    l_cursor  DBMS_SQLTUNE.sqlset_cursor;
  3  BEGIN
  4    OPEN l_cursor FOR
  5      SELECT VALUE(p)
  6      FROM   TABLE (DBMS_SQLTUNE.select_cursor_cache (
  7                      NULL, -- basic_filter
  8                      NULL, -- object_filter
  9                      NULL, -- ranking_measure1
 10                      NULL, -- ranking_measure2
 11                      NULL, -- ranking_measure3
 12                      NULL, -- result_percentage
 13                      2,    -- result_limit
                         'all') --attribute_list    
 14                    ) p;
 15  
 16    DBMS_SQLTUNE.load_sqlset (
 17      sqlset_name     => 'my_sql_tuning_set',
 18      populate_cursor => l_cursor);
 19  END;
 20  /
 
PL/SQL procedure successfully completed

或者从awr中加载

SQL> DECLARE
  2    l_cursor  DBMS_SQLTUNE.sqlset_cursor;
  3  BEGIN
  4    OPEN l_cursor FOR
  5      SELECT VALUE(p)
  6      FROM   TABLE (DBMS_SQLTUNE.select_workload_repository (
  7                      29027,  -- begin_snap
  8                      29028,  -- end_snap
  9                      NULL, -- basic_filter
 10                      NULL, -- object_filter
 11                      NULL, -- ranking_measure1
 12                      NULL, -- ranking_measure2
 13                      NULL, -- ranking_measure3
 14                      NULL, -- result_percentage
 15                      2,   -- result_limit
 16                      'all'--attribute_list
 17                      )
 18                    ) p;
 19  
 20    DBMS_SQLTUNE.load_sqlset (
 21      sqlset_name     => 'my_sql_tuning_set',
 22      populate_cursor => l_cursor);
 23  END;
 24  /
 
PL/SQL procedure successfully completed

SQL> select * from dba_sqlset where name='my_sql_tuning_set';
 
        ID NAME                           OWNER                          DESCRIPTION                                                                      CREATED     LAST_MODIFIED STATEMENT_COUNT
---------- ------------------------------ ------------------------------ -------------------------------------------------------------------------------- ----------- ------------- ---------------
        15 my_sql_tuning_set              INSUR_CHANGDE                  TEST                                                                             2016/5/26 1 2016/5/26 12:               2
 
SQL> select * from dba_sqlset_statements where sqlset_name='my_sql_tuning_set';
 
SQLSET_NAME                    SQLSET_OWNER                    SQLSET_ID SQL_ID        FORCE_MATCHING_SIGNATURE SQL_TEXT                                                                         PARSING_SCHEMA_NAME            PARSING_SCHEMA_ID PLAN_HASH_VALUE BIND_DATA                                                                        BINDS_CAPTURED MODULE                                           ACTION                           ELAPSED_TIME   CPU_TIME BUFFER_GETS DISK_READS DIRECT_WRITES ROWS_PROCESSED    FETCHES EXECUTIONS END_OF_FETCH_COUNT OPTIMIZER_COST OPTIMIZER_ENV                                                                      PRIORITY COMMAND_TYPE FIRST_LOAD_TIME     STAT_PERIOD ACTIVE_STAT_PERIOD OTHER                                                                            PLAN_TIMESTAMP    SQL_SEQ
------------------------------ ------------------------------ ---------- ------------- ------------------------ -------------------------------------------------------------------------------- ------------------------------ ----------------- --------------- -------------------------------------------------------------------------------- -------------- ------------------------------------------------ -------------------------------- ------------ ---------- ----------- ---------- ------------- -------------- ---------- ---------- ------------------ -------------- -------------------------------------------------------------------------------- ---------- ------------ ------------------- ----------- ------------------ -------------------------------------------------------------------------------- -------------- ----------
my_sql_tuning_set              INSUR_CHANGDE                          15 2823agph489xc        1.702412129134E19 select id,name,password from gl_czy where name ='系统管理'                       ZW4001                                       248      2543410975                                                                                                 R9_AppSrv.EXE                                                                           124908      32105         324         48             0             21         42         21                 21              3 E289FB89A4E49800CE001000AEF9C3E2CFFA3310564145555195A110555555154554555859155544                       3                                                                                                                                     2016/2/16 10:5        717
 
my_sql_tuning_set              INSUR_CHANGDE                          15 1hfffsrmgqhwp      1.11129101236943E19 select nvl(catalog_center,center_id) as  catalog_center from bs_hospital_collate INSUR_CHANGDE                                211      2429242715                                                                                                 JDBC Thin Client                                                                        141372     141372       11475          0             0           3825       3825       3825               3825              1 E289FB89A4E49800CE001000AEF9C3E2CFFA3310564145555195A110555555154554555859155544                       3                                                                                                                                     2016/5/23 14:2        718
 

SQL> exec dbms_sqltune.create_stgtab_sqlset(table_name => ‘TABLE_SQLSET’,schema_name 

=> ‘JY’);

PL/SQL procedure successfully completed

SQL> exec dbms_sqltune.pack_stgtab_sqlset(sqlset_name => 

‘my_sql_tuning_set’,staging_table_name => ‘TABLE_SQLSET’,staging_schema_owner =>’JY

’);

PL/SQL procedure successfully completed

导出存储STS

[IBMP740-1:oracle:/yb_oradata/RLZYbak]$expdp jy/jy directory=dump_RLZY dumpfile=stgtab_sqlset.dmp logfile=stgtab_sqlset.log tables=TABLE_SQLSET                         

                                < Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 26 May, 2016 16:57:34

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "INSUR_CHANGDE"."SYS_EXPORT_TABLE_01":  insur_changde/******** directory=dump_RLZY dumpfile=stgtab_sqlset.dmp logfile=stgtab_sqlset.log tables=TABLE_SQLSET 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 576 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
. . exported "INSUR_CHANGDE"."TABLE_SQLSET"              16.91 KB       2 rows
. . exported "INSUR_CHANGDE"."TABLE_SQLSET_CPLANS"       24.10 KB       5 rows
. . exported "INSUR_CHANGDE"."TABLE_SQLSET_CBINDS"       9.507 KB       0 rows
Master table "INSUR_CHANGDE"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for INSUR_CHANGDE.SYS_EXPORT_TABLE_01 is:
  /bak/dpdump/stgtab_sqlset.dmp
Job "INSUR_CHANGDE"."SYS_EXPORT_TABLE_01" successfully completed at 16:59:25

导入STS

[oracle@jyrac1 ~]$ impdp jy/jy directory=dump_dir dumpfile=stgtab_sqlset.dmp remap_schema=insur_changde:jy remap_tablespace=hygeia:users

Import: Release 11.2.0.4.0 - Production on Thu May 26 17:53: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=stgtab_sqlset.dmp remap_schema=insur_changde:jy remap_tablespace=hygeia:users 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "JY"."TABLE_SQLSET"                         16.91 KB       2 rows
. . imported "JY"."TABLE_SQLSET_CPLANS"                  24.10 KB       5 rows
. . imported "JY"."TABLE_SQLSET_CBINDS"                  9.507 KB       0 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Job "JY"."SYS_IMPORT_FULL_01" successfully completed at Thu May 26 17:59:33 2016 elapsed 0 00:06:05

unpack STS

SQL> begin
  2    DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(sqlset_name          => 'my_sql_tuning_set',
  3                                      sqlset_owner         => 'JY',
  4                                      replace              => true,
  5                                      staging_table_name   => 'TABLE_SQLSET',
  6                                      staging_schema_owner => 'JY');
  7  END;
  8  /
PL/SQL procedure successfully completed

从STS加载SQL执行计划基线

SQL> declare
  2  ret number;
  3  begin
  4  ret := dbms_spm.load_plans_from_sqlset(sqlset_name => 'my_sql_tuning_set',sqlset_owner => 'INSUR_CHANGDE');
  5  end;
  6  /
  
 
PL/SQL procedure successfully completed

SQL> select sql_handle, plan_name,origin, enabled, accepted,sql_text from dba_sql_plan_baselines where parsing_schema_name in('ZW4001','INSUR_CHANGDE');
 
SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENABLED ACCEPTED SQL_TEXT
------------------------------ ------------------------------ -------------- ------- -------- 

--------------------------------------------------------------------------------
SQL_c4a3ce0d598be4e4           SQL_PLAN_c98yf1pcsrt74ebcd9fdf MANUAL-LOAD    YES     YES      select id,name,password from gl_czy where name ='系统管理'
 
SQL_b9bc48036c46e780           SQL_PLAN_bmg280dq4dtw0e85be7cc MANUAL-LOAD    YES     YES      select nvl(catalog_center,center_id) as  catalog_center from 

bs_hospital_collate
 

2.使用Cursor Cache中的执行计划
可以从cursor cache中直接加载SQL语句的执行计划到SQL Management Base中。可以通过module名，方案或SQL_ID来进过滤，可以用来标记想要捕获的SQL语句或一组SQL语句。

SQL> set autotrace traceonly
SQL> select * from t1 where c1>4076;

924 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

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



SQL> select sql_text,sql_id,plan_hash_value from v$sqlarea where sql_text like '%c1>4076%';
 
SQL_TEXT                                                                         SQL_ID        PLAN_HASH_VALUE
-------------------------------------------------------------------------------- ------------- ---------------
select * from t1 where c1>4076                                                   0006gg4zsmmcg      3617692013


SQL> var n number
SQL> begin
  2  :n:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'0006gg4zsmmcg', plan_hash_value=>'3617692013', fixed =>'NO', enabled=>'YES');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select sql_handle, plan_name,origin, enabled, accepted,sql_text  from dba_sql_plan_baselines  where sql_text like 'select %c1>4076';
 
SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENABLED ACCEPTED SQL_TEXT
------------------------------ ------------------------------ -------------- ------- -------- 

--------------------------------------------------------------------------------
SQL_67b129b37635284b           SQL_PLAN_6gc99qdv3aa2bdbd90e8e MANUAL-LOAD    YES     YES      select * from t1 where c1>4076

3.从staging table中unpack SQL执行计划基线
在原系统中创建staing表

SQL> set autotrace traceonly 
SQL> select * from t1 where c1>4076;

924 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1369807930

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

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

   1 - access("C1">4076)

Note
-----
   - SQL plan baseline "SQL_PLAN_6gc99qdv3aa2bbeaed07c" used for this statement


SQL> exec dbms_spm.create_stgtab_baseline(table_name => 'BASELINE_TABLE',table_owner => 'JY');
 
PL/SQL procedure successfully completed

将SQL执行计划基线从SQL Management Base中加载到staing table中

SQL> declare
  2  x number;
  3  begin
  4   x:=dbms_spm.pack_stgtab_baseline(table_name => 'BASELINE_TABLE',table_owner => 'JY',creator =>'JY');
  5  end;
  6  /
  
PL/SQL procedure successfully completed

SQL> declare
  2  cursor c is select sql_handle, plan_name,origin, enabled, accepted,sql_text from dba_sql_plan_baselines where parsing_schema_name in('JY');
  3  x number;
  4  begin
  5   for i in c loop
  6    x:=dbms_spm.drop_sql_plan_baseline(sql_handle => ''||i.sql_handle,plan_name => ''||i.plan_name);
  7   end loop;
  8  end;
  9  /
 
PL/SQL procedure successfully completed

SQL> select * from t1 where c1>4076;

924 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1369807930

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

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

   1 - access("C1">4076)

导出staing table并在目标数据库中导入，这里省略了相关操作。在目标数据库将SQL执行计划基线从staging table中加载到SQL Management Base中

SQL> declare
  2  x number;
  3  begin
  4   x:=dbms_spm.unpack_stgtab_baseline(table_name => 'BASELINE_TABLE',table_owner => 'JY');
  5  end;
  6  /
 
PL/SQL procedure successfully completed


SQL> select * from t1 where c1>4076;

924 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1369807930

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

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

   1 - access("C1">4076)

Note
-----
   - SQL plan baseline "SQL_PLAN_6gc99qdv3aa2bbeaed07c" used for this statement

SQL执行计划基线选择
每次SQL被编译时，优化器首先使用传统的基于成本的方法来创建一个最优执行计划。如果参数optimizer_use_plan_baselines设置为true(缺省值)，那么在基于成本的执行计划被执行之前，优化器将尝试在SQL语句的SQL执行计划基线中找到一个匹配的执行计划，这是作为内存操作来执行的，如果找到了匹配的执行计划将使用该执行计划。否则，将生成一个新的执行计划并增加到plan history中，在执行计划被授受之前必须被验证。优化器将从该SQL所关联的所有执行计划中选择一个成本最低的来执行。然而，如果系统的改变造成所有已经接受的执行计划不能重现，那么优化器将会使用新的执行计划。

SQL执行计划基线也可能影响优化器对执行计划的选择。SQL执行计划基线可以被标记为固定(fixed),Fixed的SQL执行计划基线指示优化器会优先选择。如果优化器正在评估SQL执行计划基线的成本并且有一个执行计划是fixed，并且fixed的执行计划可以重现，那么优化器只会评估fixed执行计划的成本。如果fixed的执行计划不可以重现，那么优化器将会评估其它的SQL执行计划基线并从其中选择一个成本最低的执行计划。评估一个执行计划的成本远不及硬解析那样昂贵。优化器不会考虑所有可能的访问方法，只会考虑特定的访问方法。

SQL执行计划的演进
当优化器为SQL语句找到一个新执行计划时，这个新的执行计划会作为未被接受的执行计划被增加到plan history中，在它成为接受(ACCEPTED=YES)的执行计划之前需要被验证。可以通过使用EM或执行dbms_spm.evolve_sql_plan_baseline来演进。使用这些方法有三种选择:
1.只有新执行计划比现有的SQL执行计划基线性能更好才将ACCEPTED设置为YES
2.不进行性能验证就将ACCEPTED设置为YES
3.运行性能比较并生成一个报告但不演进新的执行计划

如果选择选项1,它将触发新的执行计划被评估来比较是否它的性能要比被选择的执行计划好。如果是，那么新的执行计划会被增加到SQL执行计划基线并且ACCEPTED设置为YES。否则将新执行计划的ACCEPTED设置为NO并增加到plan history中，但它的last_verified属性将更新为当前时间戳，并且会返回一个格式化的文本报告，它包含了新执行计划与原执行计划的性能统计数据。

如果选择选项2,新执行计划将会增加到SQL执行计划基线中，而不对性能进行验证并将ACCEPTED设置为YES，同样也会生成报告。

如果选择选项3,将会验证新执行计划的性能是否比已选择的执行计划好，但是就算性能更好也会不自动将新执行计划的ACCEPTED设置为YES，在评估之后只会生成报告。

SQL Management Base的使用与管理
有两个参数用来控制SPM
1.optimizer_capture_sql_plan_baselines:对重复执行的SQL语句是否自动捕获新的SQL执行计划基线。缺省值是false。

2.optimizer_use_sql_plan_baselines:控制优化器是否使用SQL执行计划基线。当启用时，优化器在编译SQL语句时会查找SQL执行计划基线中的执行计划。如果找到，优化器将会从SQL执行计划基线中选择一个成本最低的执行计划。缺省值是true。

SQL Management Base空间消耗的管理
statment log,plan histories与SQL执行计划基线都被存储在SQL Management Base中。SQL Management Base是数据库数据字典的一部分存储在SYSAUX表空间中。缺省情况下，SQL Management Base的空间限制不会超过SYSAUX表空间的10%。但是也可以使用dbms_spm.configure来进行修改让其空间限制处于SYSAUX表空间的1%到50%之间。每周后台进程都会检测SQL Management Base所占用的空间大小，并且当使用空间超过设置的限制，进程就会向alert.log记录一条警告信息。也有一个每周调度清除作业来管理SQL Management Base所占用的空间。在维护窗口内这个任务会自动运行并且任何超过53周没有被使用过的执行计划将会被清除，因此确保只有SQL语句一旦运行就能保证一年之内相关执行计划是可以使用的。可以使用dbms_spm.configure来将没使用的执行计划保留周期修改为5到523周这个范围之内。

因为SQL Management Base使用SYSAUX表侬间来存储执行计划，如果SYSAUX表空间不能使用，那么SPM也将不能使用。

通过DBA视图监控SPM
dbs_sql_plan_baselines视图显示了关于当前为特定SQL语句所创建的SQL执行计划基线。

SQL> select sql_handle, plan_name,origin, enabled, accepted,sql_text,fixed,autopurge from dba_sql_plan_baselines where parsing_schema_name in('JY') and 

sql_handle='SQL_67b129b37635284b';
 
SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENABLED ACCEPTED SQL_TEXT                                                                  

       FIXED AUTOPURGE
------------------------------ ------------------------------ -------------- ------- -------- 

-------------------------------------------------------------------------------- ----- ---------
SQL_67b129b37635284b           SQL_PLAN_6gc99qdv3aa2bbeaed07c AUTO-CAPTURE   YES     YES      select * from t1 where c1>4076                                            

       NO    YES
SQL_67b129b37635284b           SQL_PLAN_6gc99qdv3aa2bdbd90e8e MANUAL-LOAD    NO      YES      select * from t1 where c1>4076                                            

       NO    YES

为了查看任何SQL执行计划基线的详细执行计划可以执行dbms_xplan.display_sql_plan_baseline

SQL> select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle => 'SQL_67b129b37635284b',plan_name => 'SQL_PLAN_6gc99qdv3aa2bdbd90e8e'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SQL_67b129b37635284b
SQL text: select * from t1 where c1>4076
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_6gc99qdv3aa2bdbd90e8e         Plan id: 3688435342
Enabled: NO      Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   924 |  3696 |     5   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   924 |  3696 |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("C1">4076)
 
24 rows selected
 
SQL> select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle => 'SQL_67b129b37635284b',plan_name => 'SQL_PLAN_6gc99qdv3aa2bbeaed07c'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SQL_67b129b37635284b
SQL text: select * from t1 where c1>4076
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_6gc99qdv3aa2bbeaed07c         Plan id: 3199127676
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 1369807930
---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |   924 |  3696 |     4   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_T1 |   924 |  3696 |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("C1">4076)
 
24 rows selected

还可以通过使用v$sql视图来查看SQL语句是否使用了SQL执行计划基线。如果SQL语句正使用SQL执行计划基线，那么dba_sql_plan_baselines中的plan_name就会与v$sql中的sql_plan_baseline有相同的值。

select sql_handle, plan_name,origin, enabled, accepted,a.sql_text,fixed,autopurge from dba_sql_plan_baselines a,v$sql b 
where a.parsing_schema_name in('JY') and  a.plan_name=b.sql_plan_baseline and a.signature=b.exact_matching_signature
and a.sql_text like '%select * from t1 where c1>4076%';

SQL执行计划在以下情况可以用来提高或维护SQL性能:
1.数据库升级
数据库升级会安装新版的优化器，这通常会改变一小部分SQL的执行计划，大部分发生改变的执行计划性能没有变化或者有所提高。然而，特定的执行计划发生改变可能造成性能的衰减。使用SQL执行计划基线可以有效的最小化由数据库升级而引起的性能衰减。

2.系统/数据发生变化
当系统/数据发生变化时可能会影响某些SQL的执行计划，潜在造成性能衰减。使用SQL执行计划能够最小化性能衰减并且稳定SQL性能。

3.应用程序升级
开发新的应用程序意味着引入新的SQL语句。在标准测试环境中对于新SQL语句应用程序可能会使用合适的执行计划。如果你的系统配置不同于测试环境，SQL执行计划基线可以被用来产生更好的性能。

如果在cursor缓存中有性能良好的执行计划，那么可以将其加载到SPM中，因此你可以使用SQL执行计划基数来维护SQL的性能。

SQL> set autotrace traceonly
SQL> select * from t1 where c1>4076;

924 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

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



SQL> select sql_text,sql_id,plan_hash_value from v$sqlarea where sql_text like '%c1>4076%';
 
SQL_TEXT                                                                         SQL_ID        PLAN_HASH_VALUE
-------------------------------------------------------------------------------- ------------- ---------------
select * from t1 where c1>4076                                                   0006gg4zsmmcg      3617692013


SQL> var n number
SQL> begin
  2  :n:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'0006gg4zsmmcg', plan_hash_value=>'3617692013', fixed =>'NO', enabled=>'YES');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> select sql_handle, plan_name,origin, enabled, accepted,sql_text  from dba_sql_plan_baselines  where sql_text like 'select %c1>4076';
 
SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENABLED ACCEPTED SQL_TEXT
------------------------------ ------------------------------ -------------- ------- -------- 

--------------------------------------------------------------------------------
SQL_67b129b37635284b           SQL_PLAN_6gc99qdv3aa2bdbd90e8e MANUAL-LOAD    YES     YES      select * from t1 where c1>4076



SQL> select * from t1 where c1>4076;

924 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

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

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

   1 - filter("C1">4076)

Note
-----
   - SQL plan baseline "SQL_PLAN_6gc99qdv3aa2bdbd90e8e" used for this statement


Statistics
----------------------------------------------------------
         26  recursive calls
         17  db block gets
         89  consistent gets
          0  physical reads
       3060  redo size
      13042  bytes sent via SQL*Net to client
       1091  bytes received via SQL*Net from client
         63  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        924  rows processed

可以看到SQL plan baseline “SQL_PLAN_6gc99qdv3aa2bdbd90e8e” used for this statement信息，说明对于该SQL语句以后的执行都将会使用所创建的SQL执行计划基线。

在启用SPM后，对于这些有了SQL执行计划基线的SQL_ID，数据库将不会再收集新的执行计划(即使在SPM被禁用的情况下也不会再收集新的执行计划)。

SPM缺省值就是启用，自动捕获SQL执行计划基线是禁用的

SQL> show parameter sql_plan_baselines

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE

启用自动捕获SQL执行计划基线

SQL> alter system set optimizer_capture_sql_plan_baselines=true scope=both sid='*';

System altered.


SQL> create index idx_t1 on t1(c1);

Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>null,tabname=>'t1',estimate_percent=>100,method_opt=>'for all columns size repeat',cascade=>true);

PL/SQL procedure successfully completed.


SQL> select * from t1 where c1>4076;

924 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

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

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

   1 - filter("C1">4076)

Note
-----
   - SQL plan baseline "SQL_PLAN_6gc99qdv3aa2bdbd90e8e" used for this statement



SQL> select sql_handle, plan_name,origin, enabled, accepted,sql_text  from dba_sql_plan_baselines  where sql_text like 'select %c1>4076';
 
SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENABLED ACCEPTED SQL_TEXT
------------------------------ ------------------------------ -------------- ------- -------- 

--------------------------------------------------------------------------------
SQL_67b129b37635284b           SQL_PLAN_6gc99qdv3aa2bdbd90e8e MANUAL-LOAD    YES     YES      select * from t1 where c1>4076
 

可以看到优化器还是使用原来手动创建的SQL执行计划基线，并没有生成新的执行计划，还是执行的全表扫描，并没有使用我们创建的索引。

禁用SPM,启用自动捕获SQL执行计划基线

SQL> alter system set optimizer_use_sql_plan_baselines=false scope=both sid='*';

System altered.

SQL> select * from t1 where c1>4076;

924 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1369807930

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

可以看到执行计划使用了对索引idx_t1的范围扫描，而不是之前的全表扫描了。

SQL> select sql_handle, plan_name,origin, enabled, accepted,sql_text  from dba_sql_plan_baselines  where sql_text like 'select %c1>4076';
 
SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENABLED ACCEPTED SQL_TEXT
------------------------------ ------------------------------ -------------- ------- -------- 

--------------------------------------------------------------------------------
SQL_67b129b37635284b           SQL_PLAN_6gc99qdv3aa2bbeaed07c AUTO-CAPTURE   YES     NO       select * from t1 where c1>4076
SQL_67b129b37635284b           SQL_PLAN_6gc99qdv3aa2bdbd90e8e MANUAL-LOAD    YES     YES      select * from t1 where c1>4076

可以看到ORIGN为AUTO-CAPTURE的SQL执行计划基线就是自动捕获的，ENABLED为YES，但是ACCEPTED为NO，这说明虽然创建了新的SQL执行计划基线但是优化器不能使用。下面启用SPM，看优化器是否使用新创建的SQL执行计划基线。

SQL> alter system set optimizer_use_sql_plan_baselines=true scope=both sid='*';

System altered.

SQL> show parameter sql_plan_baselines

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     TRUE
optimizer_use_sql_plan_baselines     boolean     TRUE

SQL> select * from t1 where c1>4076;

924 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

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

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

   1 - filter("C1">4076)

Note
-----
   - SQL plan baseline "SQL_PLAN_6gc99qdv3aa2bdbd90e8e" used for this statement

从SQL plan baseline “SQL_PLAN_6gc99qdv3aa2bdbd90e8e” used for this statement信息可以看到优化器还是使用我们手动创建的SQL执行计划基线，对表t1执行全表扫描。想要使用执行索引扫描的SQL执行计划基线就要将其对应的ACCEPTED设置为YES并将使用全表扫描的SQL执行计划基线的ACCEPTED设置为NO。但是11gr1与11gr2在修改SQL执行计划基线的ACCEPTED属性的方法是不同的。另外，一旦在11gr2中将SQL执行计划基线的ACCEPTED设置为YES，就不能再将其设置为NO。为了不使用某个SQL执行计划基线，只能将该SQL执行计划基线的ENABLED设置为NO。

在11gr1中修改SQL执行计划基线的ACCEPTED属性值的方法如下:

SQL> DECLARE
  2 x number;
  3  BEGIN
  4  x := dbms_spm.alter_sql_plan_baseline('SQL_67b129b37635284b','SQL_PLAN_6gc99qdv3aa2bbeaed07c',attribute_name=> 'ACCEPTED',attribute_value=> 'YES');
  5  END;
  6  / 
  PL/SQL procedure successfully completed.

如果在11gr2中使用这种方法，将会得到错误信息:

SQL> DECLARE
  2 x number;
  3  BEGIN
  4  x := dbms_spm.alter_sql_plan_baseline('SQL_67b129b37635284b','SQL_PLAN_6gc99qdv3aa2bbeaed07c',attribute_name=> 'ACCEPTED',attribute_value=> 'YES');
  5  END;
  6  / 

ERROR at line 1:
ORA-38136: invalid attribute name ACCEPTED specified
ORA-06512: at "SYS.DBMS_SPM", line 2532
ORA-06512: at line 4

那么如何在11gr2中修改SQL执行计划基线的ACCEPTED值，可以使用dbms_spm.evolve_sql_plan_baseline与dbms_spm.alter_sql_plan_baseline。

SQL> SET SERVEROUTPUT ON
SQL> SET LONG 10000
SQL> DECLARE
  2      x clob;
  3  BEGIN
  4      x := dbms_spm.evolve_sql_plan_baseline('SQL_67b129b37635284b', 'SQL_PLAN_6gc99qdv3aa2bbeaed07c',verify=>'NO' ,commit=>'YES');
  5      DBMS_OUTPUT.PUT_LINE(x);
  6  END;
  7  /
-------------------------------------------------------------------------------
                        Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------

Inputs:
-------
  SQL_HANDLE = SQL_67b129b37635284b
  PLAN_NAME  = SQL_PLAN_6gc99qdv3aa2bbeaed07c
  TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
  VERIFY     = NO
  COMMIT     = YES

Plan: SQL_PLAN_6gc99qdv3aa2bbeaed07c
------------------------------------
  Plan was changed to an accepted plan.

-------------------------------------------------------------------------------
                                 Report Summary
-------------------------------------------------------------------------------
Number of plans verified: 0
Number of plans accepted: 1


PL/SQL procedure successfully completed.

这里的verify参数在修改ACCEPTED为YES之前是否需要执行相应的执行计划并与当前可以使用的执行计划比较性能。如果ACCEPTED为NO的执行计划的性能有所提高，那么将执行计划基线的ACCEPTED设置为YES。当这个参数设置为”YES”时，如果执行计划的性能有所提高就将ACCEPTED修改为YES。当这个参数设置为”NO”时，直接将ACCEPTED修改为YES。

参数commit指定是否将ACCEPTED值从NO修改为YES。当设置为YES时，执行计划基线会将ACCEPTED设置为YES并生成一个报告。当设置为NO时，一个报告会生成但不会改变ACCEPTED值。

SQL> select sql_handle, plan_name,origin, enabled, accepted,sql_text  from dba_sql_plan_baselines  where sql_text like 'select %c1>4076';
 
SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENABLED ACCEPTED SQL_TEXT
------------------------------ ------------------------------ -------------- ------- -------- 

--------------------------------------------------------------------------------
SQL_67b129b37635284b           SQL_PLAN_6gc99qdv3aa2bbeaed07c AUTO-CAPTURE   YES     YES      select * from t1 where c1>4076
SQL_67b129b37635284b           SQL_PLAN_6gc99qdv3aa2bdbd90e8e MANUAL-LOAD    YES     YES      select * from t1 where c1>4076
 

SQL> DECLARE
  2      x number;
  3  BEGIN
  4      x := dbms_spm.alter_sql_plan_baseline(sql_handle =>'SQL_67b129b37635284b',plan_name =>'SQL_PLAN_6gc99qdv3aa2bdbd90e8e',attribute_name 

=>'ENABLED',attribute_value =>'NO');
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL> select sql_handle, plan_name,origin, enabled, accepted,sql_text  from dba_sql_plan_baselines  where sql_text like 'select %c1>4076';
 
SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENABLED ACCEPTED SQL_TEXT
------------------------------ ------------------------------ -------------- ------- -------- 

--------------------------------------------------------------------------------
SQL_67b129b37635284b           SQL_PLAN_6gc99qdv3aa2bbeaed07c AUTO-CAPTURE   YES     YES      select * from t1 where c1>4076
SQL_67b129b37635284b           SQL_PLAN_6gc99qdv3aa2bdbd90e8e MANUAL-LOAD    NO      YES      select * from t1 where c1>4076

现在手动创建的使用全表扫描的SQL执行计划基线的ENABLED为NO，新生成的使用索引范围扫描的SQL执行计划基线的ENABLED与ACCEPTED值为YES，说明优化器可以使用。

SQL> select * from t1 where c1>4076;

924 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1369807930

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

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

   1 - access("C1">4076)

Note
-----
   - SQL plan baseline "SQL_PLAN_6gc99qdv3aa2bbeaed07c" used for this statement

可以看到优化器确实使用了使用索引范围扫描的SQL执行计划基线。

今天在使用dbms_sqltune.create_stgtab_sqlset来创建staging table时由于表名使用的是小写，发现创建之后，并不能查询到该表，但通过视图进行查看发现又确实存在这个表，这个问题在oracle 10.2..0.5与11.2.0.4中都存在，其它的版本没有测试过。

SQL> exec dbms_sqltune.create_stgtab_sqlset(table_name => 'stgtab_sqlset',schema_name => 

'JY');
 
PL/SQL procedure successfully completed

SQL> exec dbms_sqltune.pack_stgtab_sqlset(sqlset_name => 

'my_sql_tuning_set',staging_table_name => 'stgtab_sqlset',staging_schema_owner =>'JY');
 
PL/SQL procedure successfully completed


SQL> select * from jy.stgtab_sqlset;
 
select * from stgtab_sqlset
 
ORA-00942: table or view does not exist

SQL> desc jy.stgtab_sqlset
Object jy.stgtab_sqlset does not exist.

查询dba_objects视图确能找到该表stgtab_sqlset

SQL> select * from dba_objects where owner='JY' and OBJECT_NAME in('stgtab_sqlset');
 
OWNER                          OBJECT_NAME                                                                      SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED     LAST_DDL_TIME TIMESTAMP           STATUS  TEMPORARY GENERATED SECONDARY  NAMESPACE EDITION_NAME
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------------- ----------- ------------- ------------------- ------- --------- --------- --------- ---------- ------------------------------
JY                             stgtab_sqlset                                                                                                        92747          92747 TABLE               2016/5/26 1 2016/5/26 16: 2016-05-26:16:04:49 VALID   N         N         N                  1 

如是表名使用大写

SQL> exec dbms_sqltune.create_stgtab_sqlset(table_name => ‘TABLE_SQLSET’,schema_name 

=> ‘JY’);

PL/SQL procedure successfully completed

SQL> exec dbms_sqltune.pack_stgtab_sqlset(sqlset_name => 

‘my_sql_tuning_set’,staging_table_name => ‘TABLE_SQLSET’,staging_schema_owner =>’JY

’);

PL/SQL procedure successfully completed

SQL> select * from table_sqlset;
 
NAME                           OWNER                          DESCRIPTION                                                                      SQL_ID        FORCE_MATCHING_SIGNATURE SQL_TEXT                                                                         PARSING_SCHEMA_NAME            BIND_DATA                                                                        BIND_LIST MODULE                                           ACTION                           ELAPSED_TIME   CPU_TIME BUFFER_GETS DISK_READS DIRECT_WRITES ROWS_PROCESSED    FETCHES EXECUTIONS END_OF_FETCH_COUNT OPTIMIZER_COST OPTIMIZER_ENV                                                                      PRIORITY COMMAND_TYPE FIRST_LOAD_TIME     STAT_PERIOD ACTIVE_STAT_PERIOD OTHER                                                                            PLAN_HASH_VALUE PLAN     SPARE1     SPARE2 SPARE3 SPARE4
------------------------------ ------------------------------ -------------------------------------------------------------------------------- ------------- ------------------------ -------------------------------------------------------------------------------- ------------------------------ -------------------------------------------------------------------------------- --------- ------------------------------------------------ -------------------------------- ------------ ---------- ----------- ---------- ------------- -------------- ---------- ---------- ------------------ -------------- -------------------------------------------------------------------------------- ---------- ------------ ------------------- ----------- ------------------ -------------------------------------------------------------------------------- --------------- ---- ---------- ---------- ------ --------------------------------------------------------------------------------
my_sql_tuning_set              JY                             TEST                                                                             2823agph489xc        1.702412129134E19 select id,name,password from gl_czy where name ='系统管理'                       ZW4001                                                                                                          

查询视图dba_objects发现这两个表的相关属性除了表名与创建时间，对象ID之外没有差异

SQL> select * from dba_objects where owner='JY' and OBJECT_NAME in('stgtab_sqlset','STGTAB_SQLSET');
 
OWNER                          OBJECT_NAME                                                                      SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED     LAST_DDL_TIME TIMESTAMP           STATUS  TEMPORARY GENERATED SECONDARY  NAMESPACE EDITION_NAME
------------------------------ -------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------------- ----------- ------------- ------------------- ------- --------- --------- --------- ---------- ------------------------------
JY                             STGTAB_SQLSET                                                                                                        92781          92781 TABLE               2016/5/26 1 2016/5/26 16: 2016-05-26:16:22:55 VALID   N         N         N                  1 
JY                             stgtab_sqlset                                                                                                        92747          92747 TABLE               2016/5/26 1 2016/5/26 16: 2016-05-26:16:04:49 VALID   N         N         N                  1 

SQL> desc jy.stgtab_sqlset
Name                        Type           Nullable Default Comments 
--------------------------- -------------- -------- ------- -------- 
ID                          NUMBER         Y                         
NAME                        VARCHAR2(30)   Y                         
OWNER                       VARCHAR2(30)   Y                         
DESCRIPTION                 VARCHAR2(256)  Y                         
SQL_ID                      VARCHAR2(13)   Y                         
SQL_SEQ                     NUMBER         Y                         
FORCE_MATCHING_SIGNATURE    NUMBER         Y                         
SQL_TEXT                    CLOB           Y                         
PARSING_SCHEMA_NAME         VARCHAR2(30)   Y                         
BIND_DATA                   RAW(2000)      Y                         
BINDS_CAPTURED              CHAR(1)        Y                         
BIND_POSITION               NUMBER         Y                         
BIND_VALUE                  ANYDATA        Y                         
MODULE                      VARCHAR2(64)   Y                         
ACTION                      VARCHAR2(64)   Y                         
ELAPSED_TIME                NUMBER         Y                         
CPU_TIME                    NUMBER         Y                         
BUFFER_GETS                 NUMBER         Y                         
DISK_READS                  NUMBER         Y                         
DIRECT_WRITES               NUMBER         Y                         
ROWS_PROCESSED              NUMBER         Y                         
FETCHES                     NUMBER         Y                         
EXECUTIONS                  NUMBER         Y                         
END_OF_FETCH_COUNT          NUMBER         Y                         
OPTIMIZER_COST              NUMBER         Y                         
OPTIMIZER_ENV               RAW(2000)      Y                         
PRIORITY                    NUMBER         Y                         
COMMAND_TYPE                NUMBER         Y                         
FIRST_LOAD_TIME             VARCHAR2(19)   Y                         
STAT_PERIOD                 NUMBER         Y                         
ACTIVE_STAT_PERIOD          NUMBER         Y                         
OTHER                       CLOB           Y                         
PLAN_HASH_VALUE             NUMBER         Y                         
PLAN_STATEMENT_ID           VARCHAR2(30)   Y                         
PLAN_PLAN_ID                NUMBER         Y                         
PLAN_TIMESTAMP              DATE           Y                         
PLAN_REMARKS                VARCHAR2(4000) Y                         
PLAN_OPERATION              VARCHAR2(30)   Y                         
PLAN_OPTIONS                VARCHAR2(255)  Y                         
PLAN_OBJECT_NODE            VARCHAR2(128)  Y                         
PLAN_OBJECT_OWNER           VARCHAR2(30)   Y                         
PLAN_OBJECT_NAME            VARCHAR2(30)   Y                         
PLAN_OBJECT_ALIAS           VARCHAR2(65)   Y                         
PLAN_OBJECT_INSTANCE        NUMBER         Y                         
PLAN_OBJECT_TYPE            VARCHAR2(30)   Y                         
PLAN_OPTIMIZER              VARCHAR2(255)  Y                         
PLAN_SEARCH_COLUMNS         NUMBER         Y                         
PLAN_ID                     NUMBER         Y                         
PLAN_PARENT_ID              NUMBER         Y                         
PLAN_DEPTH                  NUMBER         Y                         
PLAN_POSITION               NUMBER         Y                         
PLAN_COST                   NUMBER         Y                         
PLAN_CARDINALITY            NUMBER         Y                         
PLAN_BYTES                  NUMBER         Y                         
PLAN_OTHER_TAG              VARCHAR2(255)  Y                         
PLAN_PARTITION_START        VARCHAR2(255)  Y                         
PLAN_PARTITION_STOP         VARCHAR2(255)  Y                         
PLAN_PARTITION_ID           NUMBER         Y                         
PLAN_DISTRIBUTION           VARCHAR2(30)   Y                         
PLAN_CPU_COST               NUMBER         Y                         
PLAN_IO_COST                NUMBER         Y                         
PLAN_TEMP_SPACE             NUMBER         Y                         
PLAN_ACCESS_PREDICATES      VARCHAR2(4000) Y                         
PLAN_FILTER_PREDICATES      VARCHAR2(4000) Y                         
PLAN_PROJECTION             VARCHAR2(4000) Y                         
PLAN_TIME                   NUMBER         Y                         
PLAN_QBLOCK_NAME            VARCHAR2(30)   Y                         
PLAN_OTHER_XML              CLOB           Y                         
PLAN_EXECUTIONS             NUMBER         Y                         
PLAN_STARTS                 NUMBER         Y                         
PLAN_OUTPUT_ROWS            NUMBER         Y                         
PLAN_CR_BUFFER_GETS         NUMBER         Y                         
PLAN_CU_BUFFER_GETS         NUMBER         Y                         
PLAN_DISK_READS             NUMBER         Y                         
PLAN_DISK_WRITES            NUMBER         Y                         
PLAN_ELAPSED_TIME           NUMBER         Y                         
PLAN_LAST_STARTS            NUMBER         Y                         
PLAN_LAST_OUTPUT_ROWS       NUMBER         Y                         
PLAN_LAST_CR_BUFFER_GETS    NUMBER         Y                         
PLAN_LAST_CU_BUFFER_GETS    NUMBER         Y                         
PLAN_LAST_DISK_READS        NUMBER         Y                         
PLAN_LAST_DISK_WRITES       NUMBER         Y                         
PLAN_LAST_ELAPSED_TIME      NUMBER         Y                         
PLAN_POLICY                 VARCHAR2(10)   Y                         
PLAN_ESTIMATED_OPTIMAL_SIZE NUMBER         Y                         
PLAN_ESTIMATED_ONEPASS_SIZE NUMBER         Y                         
PLAN_LAST_MEMORY_USED       NUMBER         Y                         
PLAN_LAST_EXECUTION         VARCHAR2(10)   Y                         
PLAN_LAST_DEGREE            NUMBER         Y                         
PLAN_TOTAL_EXECUTIONS       NUMBER         Y                         
PLAN_OPTIMAL_EXECUTIONS     NUMBER         Y                         
PLAN_ONEPASS_EXECUTIONS     NUMBER         Y                         
PLAN_MULTIPASSES_EXECUTIONS NUMBER         Y                         
PLAN_ACTIVE_TIME            NUMBER         Y                         
PLAN_MAX_TEMPSEG_SIZE       NUMBER         Y                         
PLAN_LAST_TEMPSEG_SIZE      NUMBER         Y                         
SPARE1                      NUMBER         Y                         
SPARE2                      NUMBER         Y                         
SPARE3                      BLOB           Y                         
SPARE4                      CLOB           Y                         
SPARE5                      NUMBER         Y                         
SPARE6                      NUMBER         Y                         
SPARE7                      CLOB           Y                         
SPARE8                      CLOB           Y

从plsql对dbms_sqltune.create_stgtab_sqlset的参数table_name的描述中提到了大小写敏感，但是从实际情况来看只能用大写才能方便执行迁移。