学习之路——Oracle执行计划

—摘要
  作为从事了Oracle dba运维这个岗位一年多的从业者，很多时候会产生一种只需要会搭搭环境、做做备份、导导数据、处理一些简单故障就足够生存下去的错觉，显然，这样是不够的，每次遇到自己处理不了的问题就得找二线、找专家的时候，挫败感油然而生。
  那身为一个dba应该提升自己的什么能力呢？那就应该问问一个数据库最重要的是什么呢？那当然是数据！那与数据关系最密切的又是什么呢？那就是SQL，一条好的SQL对于一个程序、一个应用是多么的重要，所以就有了SQL优化。
  优化这门学问，水真的很深，最近我打开了一年前买的而至今没怎么看的崔华专家写的《基于Oracle的SQL优化》，从基础学起，做自己觉得喜欢而有意义的事，晚一点、慢一点都没关系。

Oracle执行计划概况图：

一．什么是执行计划

  一条简单的select-SQL包含什么呢？查询所有字段还是其中某几列？单表查询还是多表联合查询？是否包含where条件？是否有group by分组？还是order by排序？等等，这意味着执行一条SQL语句需要在Oracle内部实现很多步骤，那Oracle用来执行目标SQL语句的这些步骤的组合就被称为执行计划。
  一份执行计划可以分为三部分：

1. 目标SQL正文、SQL_ID、执行计划对应的PLAN HASH VALUE
2. 执行计划的主体部分
3. 执行计划的额外补充信息

二． 查看执行计划方法

  Oracle数据库里的SQL优化绝大多数都可以归结为针对目标SQL执行计划的调整，那如何获取一条SQL的执行计划呢？方法有很多，这里就只介绍最常用的4种方法：

1. explain plan 命令

explain plan for + 目标SQL_TEXT;
select * from table(dbms_xplan.display);

  explain plan将解析目标SQL所产生的执行计划写入PLAN_TABLE$ (各个session可看到自己执行SQL的执行计划的全局临时表)，随后执行的select * from table(dbms_xplan.display)只是从PLAN_TABLE$ 中将具体的执行步骤以格式化的方式显示。
我们可以看下示例：

SQL> explain plan for select * from test_emp;

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 242355602
------------------------------------------------------------------------------
| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |    14 |  1218 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL  | TEST_EMP |    14 |  1218 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------
Note
-----
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   - dynamic sampling used for this statement (level=2)
12 rows selected.

SQL> select count(*) from sys.plan_table$;

  COUNT(*)
----------
         2

SQL> set linesize 200
SQL> set pagesize 1000
SQL> col OPTIONS for a30
SQL> select operation,options,object_name,id,cardinality,cost from sys.plan_table$;

OPERATION          OPTIONS     OBJECT_NAME      ID     CARDINALITY    COST
-----------------  ----------  ---------------  -----  -------------  -------------
SELECT STATEMENT                                0      14             3
TABLE ACCESS       FULL        TEST_EMP         1      14             3

##与上面查到的执行计划一一对应

2. DBMS_XPLAN包

  使用DBMS_XPLAN包获取目标SQL的执行计划主要有4种方式，针对不同的应用场景，选择使用其中的一种方法去得到执行计划：
  第一种方法不用多说，很熟悉了，与explain plan相结合来使用；

select * from table(dbms_xplan.display); 

  第二种方法用于在sqlplus中查看刚刚执行过的sql的执行计划，第三个值也可以用”all”,不过”advanced”的显示结果比”all”更加详细；

select * from table(dbms_xplan.display_cursor(null,null,'advanced')); 

  第三种方法用于查看指定sql的执行计划

select * from table(dbms_xplan.display_cursor('sql_id/hash_value',child_cursor_number,'advanced'));   

  第四种方法用于查看指定SQL的所有历史执行计划

select * from table(dbms_xplan.display_awr('sql_id')); 

  方法2、3都是需要目标SQL的执行计划还在Shared Pool中才能够查询得到显示，但是目标SQL的执行计划已经被清出Shared Pool，那么只要该SQL被采集到了AWR Repository中，就可以用方法4来查看该SQL的历史执行计划。

3. AUTOTRACE开关

  在SQLPLUS中设置AUTOTRACE开关的语法如下所示：

SET AUTOTRACE {OFF | ON | TRACEONLY}
[EXPLAIN]
[STATISTICS]

  以下是各种语法搭配的介绍：

• 显示执行结果、执行计划、统计信息

SET AUTOTRACE ON

• 关闭Autotrace，只显示执行结果，此为默认值

SET AUTOTRACE OFF

• 不显示执行结果，显示结果数量、执行计划、统计信息

SET AUTOTRACE TRACEONLY

• 只显示执行计划

SET AUTOTRACE TRACEONLY EXPLAIN

• 只显示结果数量、统计信息

SET AUTOTRACE TRACEONLY STATISTICS

4. 10046事件

  使用10046事件得到目标SQL的执行计划与其他方法不同的地方在于它明确显示了目标SQL实际执行计划中的每一个执行步骤所消耗的逻辑读、物理读和花费的时间，这种细粒度的明细显示对于诊断复杂SQL的性能问题尤为有用。
  使用10046事件得到执行计划的具体执行步骤如下：
  1） 在当前会话Session激活10046事件

alter session set events '10046 trace name context forever,level 12';
oradebug event 10046 trace name context forever,level 12

  2） 在当前会话Session执行目标SQL
  3） 在当前会话Session关闭10046事件

alter session set events '10046 trace name context off';
oradebug event 10046 trace name context off

  4） tkprof 格式化10046事件所产生的trace文件

tkprof 原_file_name 新_file_name

以下做个示例：
1） 开启当前Session使用oradebug命令：

SQL> oradebug setmypid

2） 在当前会话Session激活10046事件

SQL> oradebug event 10046 trace name context forever,level 12

3） 在当前会话Session执行目标SQL

SQL> select count(*) from test.test_emp;
  COUNT(*)
----------
        14

4） 查询当前Session激活10046事件后多对应的trace文件

SQL> oradebug tracefile_name
/u01/oracle/diag/rdbms/lzw/lzw/trace/lzw_ora_37104.trc

5） 在当前会话Session关闭10046事件

SQL> oradebug event 10046 trace name context off

6） 使用tkprof工具格式化trace文件

$ tkprof /u01/oracle/diag/rdbms/lzw/lzw/trace/lzw_ora_37104.trc /home/oracle/test_emp_plan.trc

7） 查看格式化后的trace文件中目标SQL的执行计划

$ more /home/oracle/test_emp_plan.trc

SQL ID: fchrjm3zt2wwd Plan Hash: 2170825879
select count(*) from test.test_emp

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          3          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          4          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=3 pr=0 pw=0 time=16 us)
        14         14         14   TABLE ACCESS FULL TEST_EMP (cr=3 pr=0 pw=0 time=10 us cost=3 size=0 card=14)

三． 真实的执行计划

  在Oracle中怎么判断目标SQL的执行计划是真实的呢？其实很简单，那就是看目标SQL是否真正的执行过，真正执行过的SQL所对应的执行计划就是真实的，所以在上面介绍的众多查询SQL的执行计划方法中有以下方式得到的执行计划为真实的：

1） 10046事件
2） select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
3） select * from table(dbms_xplan.display_cursor('sql_id/hash_value',child_cursor_number,'advanced'));
4） select * from table(dbms_xplan.display_awr('sql_id'));

  ##使用SET AUTOTRACE命令得到的执行计划都有可能是不真实的，因为使用SET AUTOTRACE命令所显示的执行计划都是来源于调用explain plan命令。

四． 执行计划的执行顺序

  不管是专家写的书籍，还是博主写的博客，都编写了属于自己的一份看懂执行计划的执行顺序的口令，这些口令是非常之好的，但是未必最适合自己，我们可以通过不断借鉴前辈们的经验，来编写自己的学习领悟，不管是这个过程还是结果，都会让自己受益匪浅，找到最适合自己的学习方式，所以以下也是我自己总结出的看懂执行计划顺序的三句话：

从上往下看，第一个没有子节点的最先执行；
先执行完所有子节点，才能执行父节点；  
遇到并列的部分，靠上的先执行。

  拿下面这段执行计划做个简单的示例：
  我们从上往下看，会发现第一个没有子节点的就是ID=4的“ TABLE ACCESS FULL T2 ”，然后根据需要执行完所有的子节点才能执行父节点，那么接下来就是ID=5的“INDEX RANGE SCAN IX_CODE”、ID=3的“NESTED LOOPS”、ID=6的“TABLE ACCESS BY INDEX ROWID T1”，结合三句话，后面的顺序也就是ID=2的“NESTED LOOPS”、ID=8的”TABLE ACCESS FULL T1”、ID=7的“BUFFER SORT”、ID=1的“MERGE JOIN CARTESIAN”、ID=0的“SELECT STATEMENT”,所以这一段执行计划的执行顺序就是：4==>5==>3==>6==>2==>8==>7==>1==>0

五． 常见的执行计划

  这里我们就直接用示例来看看这些常见的执行计划吧：

1. 与表访问相关的执行计划

• TABLE ACCESS FULL—全表扫描

SQL> select empno,ename,rowid from scott.emp where ename='SCOTT';
EMPNO     ENAME      ROWID
--------    ----------  ------------------
7788        SCOTT      AAAVREAAEAAAACXAAH

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
SQL_ID  fqvvq5519bn1k, child number 0
-------------------------------------
select empno,ename,rowid from scott.emp where ename='SCOTT'

Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     3 (100)|          |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    32 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

• TABLE ACCESS BY USER ROWID—ROWID扫描(rowid源于用户指定)

SQL> select empno,ename from scott.emp where rowid='AAAVREAAEAAAACXAAH';

     EMPNO ENAME
---------- ----------
      7788 SCOTT

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID  cjfhzuxa59gw7, child number 0
-------------------------------------
select empno,ename from scott.emp where rowid='AAAVREAAEAAAACXAAH'

Plan hash value: 1116584662
-----------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |       |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY USER ROWID| EMP  |     1 |    32 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

• TABLE ACCESS BY INDEX ROWID—ROWID扫描(rowid源于索引)

SQL> select empno,ename from scott.emp where empno=7788;

     EMPNO ENAME
---------- ----------
      7788 SCOTT

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  6y35cw2wawujg, child number 0
-------------------------------------
select empno,ename from scott.emp where empno=7788

Plan hash value: 2949544139
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    20 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

##因为emp表的empno有索引PK_EMP，所以这里的ROWID来源于索引。

2. 与B树索引相关的执行计划

  先创建一个测试表：

SQL> create table test1(id number,name varchar2(1));

  插入7条数据并提交

SQL> insert into test1 values(99,'A');
SQL> insert into test1 values(100,'A');
SQL> insert into test1 values(101,'B');
SQL> insert into test1 values(102,'B');
SQL> insert into test1 values(103,'B');
SQL> insert into test1 values(104,'B');
SQL> insert into test1 values(105,'B');
SQL> commit;

• INDEX UNIQUE SCAN—索引唯一扫描

  在ID列上创建唯一性索引

SQL> create unique index idx_uni_test1 on test1(id);

SQL> select * from test1 where id=100;

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  304p57f3awv0n, child number 0
-------------------------------------
select * from test1 where id=100

Plan hash value: 2920889977
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |       |       |     1 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST1         |     1 |    15 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | IDX_UNI_TEST1 |     1 |       |     0   (0)|          |
---------------------------------------------------------------------------------------------

• INDEX RANGE SCAN—索引范围扫描

  删除测试表上的唯一性索引

SQL> drop index idx_uni_test1;

  重新创建一个非唯一性索引

SQL> create index idx_test1_1 on test1(id);

SQL> select /*+ index(test1 idx_test1_1) */* from test1 where id=100;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  d2vs2qkd79a8q, child number 0
-------------------------------------
select /*+ index(test1 idx_test1_1) */* from test1 where id=100

Plan hash value: 4186374959
-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST1       |     1 |    15 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_TEST1_1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

• INDEX FAST FULL SCAN—索引快速全扫描

  truncate test1表的数据

SQL> truncate table test1;

  重新插入10000行数据

SQL> begin
     for i in 1..5000
     loop
     insert into test1 values(i,'A');
     end loop;
     commit;
     end;
     /
SQL> begin 
     for i in 5001..10000
     loop
     insert into test1 values(i,'B');
     end loop;
     commit;
     end;
     /

  对test1表收集统计信息

SQL> exec dbms_stats.gather_table_stats(ownname =>'SYS',tabname => 'TEST1',estimate_percent => 100,cascade => TRUE,no_invalidate => false,method_opt => 'FOR ALL COLUMNS SIZE 1');

  将ID这一列设置为非空字段

SQL> alter table test1 modify(id not null);

SQL> select id from test1;

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  ftkg04k3d00n7, child number 0
-------------------------------------
select id from test1

Plan hash value: 836885815
--------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |     7 (100)|          |
|   1 |  INDEX FAST FULL SCAN| IDX_TEST1_1 | 10000 | 40000 |     7   (0)| 00:00:01 |
------------------------------------------------------------------------------------

• INDEX FULL SCAN—索引全扫描

SQL> select /*+ index(test1 idx_test1_1)*/id from test1;

select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  8avnt05yszym9, child number 0
-------------------------------------
select /*+ index(test1  idx_test1_1)*/id from test1

Plan hash value: 1011581059
--------------------------------------------------------------------------------
| Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |             |       |       |    20 (100)|          |
|   1 |  INDEX FULL SCAN | IDX_TEST1_1 | 10000 | 40000 |    20   (0)| 00:00:01 |
--------------------------------------------------------------------------------

• INDEX SKIP SCAN—索引跳跃式扫描

  删除索引idx_test1_1，创建复合索引idx_test1_2，前导列为name，第二列为id

SQL> drop index idx_test1_1;
SQL> create index idx_test1_2 on test1(name,id);

SQL> select * from test1 where id=100;

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  304p57f3awv0n, child number 0
-------------------------------------
select * from test1 where id=100

Plan hash value: 858043409
--------------------------------------------------------------------------------
| Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |             |       |       |     3 (100)|          |
|*  1 |  INDEX SKIP SCAN | IDX_TEST1_2 |     1 |     6 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

3. 其他典型的执行计划

  此部分就不做详细的说明了，下面简单的介绍下其中比较常见的几种执行计划：

• AND-EQUAL (INDEX MERGE)
  索引合并，当where条件中出现多个针对不同单列的等值条件，并这些列都有单键值的索引，则Oracle可能会以相应的单个等值条件去分别扫描这些索引，然后合并这些扫描单个索引所得的rowid集合，如果能从集合中值相同的rowid，Oracle只需用这些rowid回表即可得到最终执行结果。

• INDEX JOIN
  Index join针对单表上的不同索引之间的连接

• VIEW
  Oracle在处理包含视图的sql时，根据该视图是否可以做视图合并(View Merging),其对应的执行计划有两种方式：
  1.如果可以做视图合并，则Oracle在执行该SQL时可以直接针对该视图的基表，此时SQL的执行计划很可能不会出现“VIEW”；
  2.如果不能做视图合并，则Oracle将把该视图看作一个整体并独立地执行，此时SQL的执行计划将会出现关键字“VIEW”

• FILTER
  过滤、筛选，这是一种特殊的执行计划，对应的过程有三步：1.得到一个驱动结果集；2.根据一定的过滤条件从上述结果集中滤除不满足条件的记录；2.剩下的记录就会返回给最终用户或下一个执行步骤。

• SORT
  排序，执行计划中通常以组合的方式出现，如：SORT AGGREGATE、SORT UNIQUE、SORT JOIN、SORT GROUP BY、SORT ORDER BY、BUFFER SORT。
  需要注意的是执行计划中即使出现关键字“SORT”，也不一定是需要排序。

• UNION/UNION ALL
  表示对两个结果集进行合并，UNION ALL仅仅是简单地将两个结果集合并，并不做任何额外的处理；UNION除了合并之外，还会对合并后的结果进行排序去重。

六． 执行计划的稳定

  在生产环境中执行的SQL，我们都希望CBO能够产生正确的执行计划，但是有时候会因为各种各样的原因导致CBO会产生效率不高，甚至错误的执行计划，而且在日常运维中，我们会常常遇到一条SQL本来跑得好好的，突然慢得影响业务，这种SQL执行效率突然的衰减往往与目标SQL执行计划的改变有关。这种情况下，我们主要有两种方法可以用来调整和稳定目标SQL的执行计划：

1. SQL Profile

  SQL Profile主要是Oracle10g的工具，它主要能实现在不能修改目标SQL的SQL文本的情况下使目标SQL按指定的执行计划运行，锁住、稳定其执行计划。SQL Profile主要有两种类型：

• Automatic SQL Profile

  创建一个测试表，插入1万行，并创建索引

SQL> create table test2(id number);
SQL> declare
     begin
     for i in 1..10000
     loop
     insert into test2 values(i);
     end loop;
     commit;
     end;
     /

SQL> create index idx_test2 on test2(id);

  收集统计信息

SQL> exec dbms_stats.gather_table_stats(ownname => 'sys',tabname => 'test2',method_opt => 'for all columns size 1',cascade => true);

  执行目标SQL，并查看执行计划

SQL> select /*+ no_index(test2 idx_test2) */ * from test2 where id=1;

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  2h6rp4fp4h8nt, child number 0
-------------------------------------
select /*+ no_index(test2 idx_test2) */ * from test2 where id=1

Plan hash value: 300966803
---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       |     7 (100)|          |
|*  1 |  TABLE ACCESS FULL| TEST2 |     1 |     4 |     7   (0)| 00:00:01 |
---------------------------------------------------------------------------

  很显然这条加了Hint的SQL走了全表扫(TABLE ACCESS FULL)，但是正确的应该是走对IDX_TEST2的索引范围扫描(INDEX RANGE SCAN)。

  创建一个自动调整任务使用SQL Tuning Advisor来尝试对SQL生成Automatic SQL Profile

SQL> declare
        my_task_name varchar2(30);
        my_sqltext CLOB;
     begin
        my_sqltext := 'select /*+ no_index(test2 idx_test2) */ * from test2 where id=1';
        my_task_name := dbms_sqltune.create_tuning_task(
                sql_text => my_sqltext,
                user_name => 'SYS',
                scope => 'COMPREHENSIVE',
                time_limit => 60,
                task_name => 'my_sql_tuning_task',
                description => 'Task to tune a query on table test2');
     end;
     /

  执行自动调整任务

SQL> begin
     dbms_sqltune.execute_tuning_task(task_name => 'my_sql_tuning_task');
     end;
     /

SQL> set long 9000
SQL> set longchunksize 1000
SQL> set linesize 800
SQL> select dbms_sqltune.report_tuning_task( 'my_sql_tuning_task') from dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
-------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : my_sql_tuning_task
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 11/18/2019 19:17:50
Completed at       : 11/18/2019 19:17:50

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
-----------------------------------------------------------------
-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID     : atatj8g3j1gxc
SQL Text   : select /*+ no_index(test2 idx_test2) */ * from test2 where id=1
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
-------------------------------------------------------------------------------------
  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', task_owner => 'SYS', replace => TRUE);
  Validation results
  ------------------
  The SQL profile was tested by executing both its plan and the original plan
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
-------------------------------------------------------------------------------------
  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):             .000084           .000007      91.66 %
  CPU Time (s):                   .0001                 0        100 %
  User I/O Time (s):                  0                 0
  Buffer Gets:                       22                 2       90.9 %
  Physical Read Requests:             0                 0…
…
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 300966803
--------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
-----------------------------------------------------------------
----------
|   0 | SELECT STATEMENT  |       |     1 |     4 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST2 |     1 |     4 |     7   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ID"=1)
2- Using SQL Profile

DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
---------------------------------------------------------------------------------------
Plan hash value: 1855540135
------------------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |     1 |     4 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IDX_TEST2 |     1 |     4 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------

  从上述调整结果可以看出Oracle已经找到了更好的执行计划，并且已经创建了针对该SQL的Automatic SQL Profile，如果接受这个调整，目标SQL的响应时间将有91.66 %的改善，逻辑读将有90.9 %的改善，并且会由之前的全表扫变成索引范围扫描。

  最后只需根据Oracle的提示接收这个SQL Profile

execute dbms_sqltune.accept_sql_profile(task_name =>'my_sql_tuning_task', task_owner => 'SYS', replace => TRUE);

SQL> select /*+ no_index(test2 idx_test2) */ * from test2 where id=1;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID  2h6rp4fp4h8nt, child number 0
-------------------------------------
select /*+ no_index(test2 idx_test2) */ * from test2 where id=1

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

  从上述执行计划可以看出，在不改变SQL的情况下，SQL Profile确实改变了SQL的执行计划，但是这时如果SQL文本发生一点改动，那么原有的SQL Profile就会失效，这里就要配合FORCE_MATCH参数的使用：

execute dbms_sqltune.accept_sql_profile(task_name =>'my_sql_tuning_task', task_owner => 'SYS', replace => TRUE,force_match => true);

SQL> select /*+ no_index(test2 idx_test2) */ * from test2 where id=2;

SQL>  select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
SQL_ID  agznsf177yju6, child number 0
-------------------------------------
select /*+ no_index(test2 idx_test2) */ * from test2 where id=2

Plan hash value: 1855540135

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

  使用了FORCE_MATCH参数，即使where条件输入的值改变了，SQL Profile也可以起到作用。

• Manual SQL Profile

  Manual SQL Profile本质上就是一堆Hint组合，来源于执行计划中Outline Data部分的Hint组合这里需要使用到coe_xfr_sql_profile.sql，这个脚本可以在metalink上下载，如果没有账号的朋友可以留言，我会把sql发给你，以下是使用Manual SQL Profile的具体步骤：
  还是刚才创建的测试表，先把生成的Automatic SQL Profile删除：

SQL> exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_016e829f93290001');

  原本SQL的执行计划

SQL> select /*+ no_index(test2 idx_test2) */ * from test2 where id=3;

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
SQL_ID  8p7zdsna4jk2y, child number 0
-------------------------------------
select /*+ no_index(test2 idx_test2) */ * from test2 where id=3

Plan hash value: 300966803
---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       |     7 (100)|          |
|*  1 |  TABLE ACCESS FULL| TEST2 |     1 |     4 |     7   (0)| 00:00:01 |
---------------------------------------------------------------------------

  改写Hint的SQL的执行计划

SQL> select /*+ index(test2 idx_test2) */ * from test2 where id=3;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
SQL_ID  drv0zwkyrb5wr, child number 0
-------------------------------------
select /*+ index(test2 idx_test2) */ * from test2 where id=3

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

  查看两条sql的信息

SQL> select sql_text,sql_id,version_count from v$sqlarea where sql_text like '%id=3%';

SQL_TEXT                                                                            SQL_ID          VERSION_COUNT
---------------------------------------------------------------------------------   --------------  -------------
select sql_text,sql_id,version_count from v$sqlarea where sql_text like '%id=3%'    9c5fm1hxusqar   1

select /*+ no_index(test2 idx_test2) */ * from test2 where id=3                     8p7zdsna4jk2y   1

select /*+ index(test2 idx_test2) */ * from test2 where id=3                        drv0zwkyrb5wr   1

  查看SQL对应的PLAN HASH VALUE

原目标SQL：
SQL> select plan_hash_value from v$sql where sql_id='8p7zdsna4jk2y';
PLAN_HASH_VALUE
---------------
      300966803

加了Hint后的SQL:
SQL> select plan_hash_value from v$sql where sql_id='drv0zwkyrb5wr';
PLAN_HASH_VALUE
---------------
     1855540135

  上传coe_xfr_sql_profile.sql，针对SQL执行coe_xfr_sql_profile.sql

原SQL：
SQL> @/home/oracle/coe_xfr_sql_profile.sql
Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID         : "8p7zdsna4jk2y"
PLAN_HASH_VALUE: "300966803"

Execute coe_xfr_sql_profile_8p7zdsna4jk2y_300966803.sql

改写后的SQL：
SQL> @/home/oracle/coe_xfr_sql_profile.sql
Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID         : "drv0zwkyrb5wr"
PLAN_HASH_VALUE: "1855540135"

Execute coe_xfr_sql_profile_drv0zwkyrb5wr_1855540135.sql

  将改写后SQL的SQL_Profile文件中的以下这段替换原SQL的SQL_Profile文件的以下段：

改动后SQL：
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',
q'[DB_VERSION('11.2.0.4')]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$1")]',
q'[INDEX(@"SEL$1" "TEST2"@"SEL$1" ("TEST2"."ID"))]',
q'[END_OUTLINE_DATA]');

原SQL:
h := SYS.SQLPROF_ATTR(
q'[BEGIN_OUTLINE_DATA]',
q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',
q'[DB_VERSION('11.2.0.4')]',
q'[ALL_ROWS]',
q'[OUTLINE_LEAF(@"SEL$1")]',
q'[FULL(@"SEL$1" "TEST2"@"SEL$1")]',
q'[END_OUTLINE_DATA]');

  执行已修改后的coe_xfr_sql_profile_8p7zdsna4jk2y_300966803.sql

SQL> @/home/oracle/coe_xfr_sql_profile_8p7zdsna4jk2y_300966803.sql

  查看原SQL现在的执行计划

SQL> select /*+ no_index(test2 idx_test2) */ * from test2 where id=3;

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
SQL_ID  8p7zdsna4jk2y, child number 0
-------------------------------------
select /*+ no_index(test2 idx_test2) */ * from test2 where id=3

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

##与Automatic SQL Profile相同，如果需要改变SQL的where输入的值，也能使用Manual SQL Profile，需要在刚才的coe_xfr_sql_profile_8p7zdsna4jk2y_300966803.sql中将“force_match”参数改为TRUE。

2. SPM(SQL Plan Management)

  Oracle 11g推出了新工具SPM(SQL Plan Management)，SPM是一种主动的稳定执行计划的手段，能够保证只有被验证过的执行计划才会被启用。上面说的SQL Profile方法来稳定目标SQL的执行计划，那就意味着很可能失去了继续优化SQL的执行效率的机会，SPM可以说是彻底解决了执行计划稳定性的问题，它既能够主动地稳定执行计划，又保留了继续使用新的执行效率可能更高的执行计划的机会。
  启用SPM后，每一个SQL都会存在对应的SQL Plan Baseline，这里面存储的就是该SQL的执行计划，我们可以在视图DBA_SQL_PLAN_BASELINES中查看目标SQL的所有SQL Plan Baseline，其中的ENABLED和ACCEPTED这两列用来描述一个SQL Plan Baseline多对应的执行计划是否能被Oracle启用，均为YES则表示已启用。
  Oracle11g中，有两种方法可以产生目标SQL的SQL Plan Baseline：

• 自动捕获

  参数optimizer_capture_sql_plan_baselines用于控制是否开启自动捕获SQL Plan Baseline，默认值为FALSE，可在Session和系统级别动态修改；optimizer_use_sql_plan_baselines用于是否启用SQL Plan Baseline，默认值TRUE，可在Session和系统级别动态修改。
  以下做个测试：
  在当前Session禁用SPM并同时开启自动捕获SQL Plan Baseline

SQL> alter session set optimizer_use_sql_plan_baselines=FALSE;
SQL> alter session set optimizer_capture_sql_plan_baselines=TRUE;

  创建测试表并添加索引

SQL> create table test3 as select * from dba_objects;
SQL> create index idx_test3 on test3(object_id);

  收集此表的统计信息

SQL> exec dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'TEST3',estimate_percent => 100,cascade => true);

SQL> select object_id,object_name from test3 where object_id between 100 and 105;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
SQL_ID  6mmfucvzy369v, child number 0
-------------------------------------
select object_id,object_name from test3 where object_id between 100 and
105

Plan hash value: 3735083495
------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST3     |     7 |   210 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_TEST3 |     7 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

  查看该SQL是否有对应的SQL Plan Baseline

SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id%';
no rows selected

  SQL只执行一次，Oracle不会自动捕获其SQL Plan Baseline。

  再次执行该SQL，查看是否有SQL Plan Baseline

SQL> select object_id,object_name from test3 where object_id between 100 and 105;

SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id%';
SQL_HANDLE                     PLAN_NAME                         ORIGIN         ENA             ACC              SQL_TEXT
---------------------          ------------------------------    -------------  ---------       --------         ------------------------------------------------------------------------------------
SQL_69de3b2923b336b6           SQL_PLAN_6mrjv54jv6dpqd1683003    AUTO-CAPTURE   YES             YES              select object_id,object_name from test3 where object_id between 100 and 105

  SQL已重复执行，所以现在Oracle就会自动捕获其SQL Plan Baseline，其中ENABLED和ACCEPTED的值均为“YES”。

  现将IDX_TEST3的聚簇因子修改为2400万，目的是为了能让SQL的执行计划变为对表TEST3的全表扫描

SQL> exec dbms_stats.set_index_stats(ownname => 'SYS',indname => 'IDX_TEST3',clstfct => 24000000,no_invalidate => false);

SQL> select index_name,clustering_factor from dba_indexes where index_name='IDX_TEST3';
INDEX_NAME                     CLUSTERING_FACTOR
------------------------------ -----------------
IDX_TEST3                               24000000

SQL> select object_id,object_name from test3 where object_id between 100 and 105;

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  6mmfucvzy369v, child number 0
-------------------------------------
select object_id,object_name from test3 where object_id between 100 and
105

Plan hash value: 3306317399
---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       |   339 (100)|          |
|*  1 |  TABLE ACCESS FULL| TEST3 |     7 |   210 |   339   (1)| 00:00:05 |
---------------------------------------------------------------------------

  再次查看dba_sql_plan_baselines视图

SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id%';
SQL_HANDLE                     PLAN_NAME                         ORIGIN         ENA             ACC                SQL_TEXT
---------------------          -------------------------------   -------------  -------         ------------       -----------------------------------------------------------------------------
SQL_69de3b2923b336b6           SQL_PLAN_6mrjv54jv6dpq7e6c5d22    AUTO-CAPTURE   YES             NO                 select object_id,object_name from test3 where object_id between 100 and 105

SQL_69de3b2923b336b6           SQL_PLAN_6mrjv54jv6dpqd1683003    AUTO-CAPTURE   YES             YES                select object_id,object_name from test3 where object_id between 100 and 105

  在当前Session禁用SPM并同时开启自动捕获SQL Plan Baseline

SQL> alter session set optimizer_use_sql_plan_baselines=TRUE;
SQL> alter session set optimizer_capture_sql_plan_baselines= FALSE;

  现在的IDX_TEST3的聚簇因子还是2400万

SQL> select index_name,clustering_factor from dba_indexes where index_name='IDX_TEST3';
INDEX_NAME                     CLUSTERING_FACTOR
------------------------------ -----------------
IDX_TEST3                               24000000

  再次执行SQL，查看执行计划

SQL> select object_id,object_name from test3 where object_id between 100 and 105;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  6mmfucvzy369v, child number 2
-------------------------------------
select object_id,object_name from test3 where object_id between 100 and
105

Plan hash value: 3735083495
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |       |       |  1908 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST3     |     7 |   210 |  1908   (0)| 00:00:23 |
|*  2 |   INDEX RANGE SCAN          | IDX_TEST3 |     7 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Note
-----
   - SQL plan baseline SQL_PLAN_6mrjv54jv6dpqd1683003 used for this statement

  从上述可以看出，目标SQL的执行计划从全表扫变回了对索引IDX_TEST3的索引范围扫描，Note部分SQL plan baseline SQL_PLAN_6mrjv54jv6dpqd1683003，表明了SPM开启的情况下，Oracle依然会选择ENABLED和ACCEPTED的值均为“YES”的SQL plan baseline。

  如果在启用了SPM的情况下，想起用该SQL新的执行计划，如以下操作：

SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id%';

SQL_HANDLE                     PLAN_NAME                         ORIGIN         ENA             ACC            SQL_TEXT
------------------------       -----------------------------     ------------   -------         ---------      ------------------------------------------------------------------------------------
SQL_69de3b2923b336b6           SQL_PLAN_6mrjv54jv6dpq7e6c5d22    AUTO-CAPTURE   YES             NO             select object_id,object_name from test3 where object_id between 100 and 105

SQL_69de3b2923b336b6           SQL_PLAN_6mrjv54jv6dpqd1683003    AUTO-CAPTURE   YES             YES            select object_id,object_name from test3 where object_id between 100 and 105

  在11gR1中，只需要将SQL_PLAN_6mrjv54jv6dpqd1683003的ACCEPTED设置为NO就可以了：

SQL> var temp varchar2(1000);
SQL> exec:temp:=dbms_spm.alter_sql_plan_baseline(sql_handle => 'SQL_69de3b2923b336b6',plan_name => 'SQL_PLAN_6mrjv54jv6dpqd1683003',attribute_name => 'accepted',attribute_value => 'NO');

  但是在11gR2中，这样操作就会报错，需要使用DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE和DBMS_SPM.ALTER_SQL_PLAN_BASELINE达到启用新执行计划的目的：

  先将新执行计划的ACCEPTED的值设为YES

SQL> exec:temp:=dbms_spm.evolve_sql_plan_baseline(sql_handle => 'SQL_69de3b2923b336b6',plan_name => 'SQL_PLAN_6mrjv54jv6dpq7e6c5d22',verify => 'NO',commit => 'YES');

SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id%';

SQL_HANDLE                     PLAN_NAME                         ORIGIN         ENA             ACC           SQL_TEXT
------------------------       ------------------------------    -------------  -------        ---------      -------------------------------------------------------------------------------------
SQL_69de3b2923b336b6           SQL_PLAN_6mrjv54jv6dpq7e6c5d22    AUTO-CAPTURE   YES             YES           select object_id,object_name from test3 where object_id between 100 and 105

SQL_69de3b2923b336b6           SQL_PLAN_6mrjv54jv6dpqd1683003    AUTO-CAPTURE   YES             YES           select object_id,object_name from test3 where object_id between 100 and 105

  然后再将原先执行计划的ENABLED的值设为‘NO’

SQL> exec:temp:=dbms_spm.alter_sql_plan_baseline(sql_handle => 'SQL_69de3b2923b336b6',plan_name => 'SQL_PLAN_6mrjv54jv6dpqd1683003',attribute_name => 'ENABLED',attribute_value => 'NO');

SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id%';

SQL_HANDLE                     PLAN_NAME                         ORIGIN         ENA             ACC          SQL_TEXT
------------------------       ------------------------------    -------------  ------          -----        --------------------------------------------------------------------------------------
SQL_69de3b2923b336b6           SQL_PLAN_6mrjv54jv6dpq7e6c5d22    AUTO-CAPTURE   YES             YES          select object_id,object_name from test3 where object_id between 100 and 105

SQL_69de3b2923b336b6           SQL_PLAN_6mrjv54jv6dpqd1683003    AUTO-CAPTURE   NO              YES          select object_id,object_name from test3 where object_id between 100 and 105

  再次执行SQL，查看执行计划

SQL> select object_id,object_name from test3 where object_id between 100 and 105;

SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  6mmfucvzy369v, child number 1
-------------------------------------
select object_id,object_name from test3 where object_id between 100 and
105

Plan hash value: 3306317399
---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       |   339 (100)|          |
|*  1 |  TABLE ACCESS FULL| TEST3 |     7 |   210 |   339   (1)| 00:00:05 |
---------------------------------------------------------------------------

  从上述测试结果来看，我们可以轻易地在目标SQL的多个执行计划中切换，所以SPM既能主动地稳定执行计划，又保留了继续使用新的执行计划的机会。

• 手工生成

  手工生成SQL Plan Baseline的方式和Manual SQL Profile一样，可以在不改变目标SQL的情况下调整执行计划，以下做个示例：
  使用刚才的测试表做一个查询，获取执行计划

SQL> select /*+ no_index(test3 idx_test3) */object_name,object_id from test3 where object_id=7;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  335s7c506w0bz, child number 0
-------------------------------------
select /*+ no_index(test3 idx_test3) */object_name,object_id from test3
where object_id=7

Plan hash value: 3306317399
---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       |   339 (100)|          |
|*  1 |  TABLE ACCESS FULL| TEST3 |     1 |    30 |   339   (1)| 00:00:05 |
---------------------------------------------------------------------------

  没有开启自动捕获，目标SQL还未生成对应的SQL Plan Baseline

SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select /*+ no_index(test3 idx_test3) */object_name%';
no rows selected

  使用目标SQL的初始执行计划对应的信息手工生成SQL Plan Baseline

SQL> var temp number
SQL> exec:temp:=dbms_spm.load_plans_from_cursor_cache(sql_id => '335s7c506w0bz',plan_hash_value => 3306317399);

SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select /*+ no_index(test3 idx_test3) */object_name%';

SQL_HANDLE                     PLAN_NAME                         ORIGIN         ENA             ACC         SQL_TEXT
-------------------------      -------------------------------   -------------  -----           ------      ------------------------------------------------------------------------------------
SQL_e2a8001297a557c9           SQL_PLAN_f5a002abuapy97e6c5d22    MANUAL-LOAD    YES             YES         select /*+ no_index(test3 idx_test3) */object_name,object_id from test3 where ob

  改写原目标SQL，加入Hint重新执行

SQL> select /*+ index(test3 idx_test3) */object_name,object_id from test3 where object_id=7;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  43yfvtarchtu8, child number 0
-------------------------------------
select /*+ index(test3 idx_test3) */object_name,object_id from test3
where object_id=7

Plan hash value: 3735083495
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |       |       |   277 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST3     |     1 |    30 |   277   (0)| 00:00:04 |
|*  2 |   INDEX RANGE SCAN          | IDX_TEST3 |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

  用改写后的SQL的执行计划多对应的SQL_ID和PLAN HASH VALUE以及原SQL的SQL Plan Baseline的sql_handle来生成新的SQL Plan Baseline

SQL> exec:temp:=dbms_spm.load_plans_from_cursor_cache(sql_id => '43yfvtarchtu8',plan_hash_value => 3735083495,sql_handle => 'SQL_e2a8001297a557c9');

SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select /*+ no_index(test3 idx_test3) */object_name%';

SQL_HANDLE                     PLAN_NAME                         ORIGIN         ENA             ACC            SQL_TEXT
-------------------------      ----------     ---------------    -------------  ----            ------         --------------------------------------------------------------------------------
SQL_e2a8001297a557c9           SQL_PLAN_f5a002abuapy97e6c5d22    MANUAL-LOAD    YES             YES            select /*+ no_index(test3 idx_test3) */object_name,object_id from test3 where ob

SQL_e2a8001297a557c9           SQL_PLAN_f5a002abuapy9d1683003    MANUAL-LOAD    YES             YES            select /*+ no_index(test3 idx_test3) */object_name,object_id from test3 where ob

  删除原SQL的执行计划所对应的SQL Plan Baseline

SQL> exec:temp:=dbms_spm.drop_sql_plan_baseline(sql_handle => 'SQL_e2a8001297a557c9',plan_name => 'SQL_PLAN_f5a002abuapy97e6c5d22');

SQL> select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select /*+ no_index(test3 idx_test3) */object_name%';

SQL_HANDLE                     PLAN_NAME                         ORIGIN         ENA             ACC           SQL_TEXT
-------------------------      -----------------------------     ------------   -------         -------       ---------------------------------------------------------------------------------
SQL_e2a8001297a557c9           SQL_PLAN_f5a002abuapy9d1683003    MANUAL-LOAD    YES             YES           select /*+ no_index(test3 idx_test3) */object_name,object_id from test3 where ob

  再次执行原目标SQL，查看其执行计划

SQL> select /*+ no_index(test3 idx_test3) */object_name,object_id from test3 where object_id=7;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  335s7c506w0bz, child number 2
-------------------------------------
select /*+ no_index(test3 idx_test3) */object_name,object_id from test3
where object_id=7

Plan hash value: 3735083495
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |       |       |   277 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST3     |     1 |    30 |   277   (0)| 00:00:04 |
|*  2 |   INDEX RANGE SCAN          | IDX_TEST3 |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Note
-----
   - SQL plan baseline SQL_PLAN_f5a002abuapy9d1683003 used for this statement

  这样就实现了用SQL Plan Baseline在不改变SQL文本的情况下，改变执行计划。

  执行计划是Oracle数据库里SQL优化的基础，绝大多数Oracle数据库中的SQL优化都可以说是对SQL执行计划的调整，所以我们都应该学会看懂执行计划。