spm执行计划的绑定

主要步骤：
1)查看sql的sql_id,plan_hash_value值
2)将原始sql语句加入基线
3)查看基线（如果有多个需要删除只剩一个）
4）验证sql执行计划

两种方式：直接使用dbms_spm.load_plans_from_cursor_cache和coe_xfr_sql_profile.sql

实验一、直接使用dbms_spm.load_plans_from_cursor_cache绑定

1.创建用户
SQL> create user xmc identified by xmc;

User created.

SQL> grant dba to xmc;

Grant succeeded.

SQL> conn xmc/xmc
Connected.
2.创建表和索引并收集统计信息
SQL> create table test2 as select * from dba_objects;

Table created.

SQL> create index idx_test2 on test2(object_id)online;  这里注意online加了可以在线dml，搞大表索引的时候尤其要注意

Index created.

SQL> begin
  2  dbms_stats.gather_table_stats(ownname=>'XMC',tabname=>'TEST2',cascade=>true,no_invalidate=>false);
  3  end;
  4  /

PL/SQL procedure successfully completed.

3.执行原始的sql

SQL> set autot trace
SQL> select * from test2 where object_id=20;


Execution Plan
----------------------------------------------------------
Plan hash value: 4047680367

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

4.新开一个窗口，查原始sql的sql_id和plan_hash_value
SQL> set linesize 2000
SQL> col sql_id for a20
SQL> col sql_test for a40
SQL> col sql_text for a60
SQL> select sql_id,plan_hash_value,sql_text from v$sqlarea where sql_text like 'select * from test2 where object_id=20%';

SQL_ID             PLAN_HASH_VALUE SQL_TEXT
-------------------- --------------- ------------------------------------------------------------
4tm6j886yvzj3          4047680367 select * from test2 where object_id=20

5.将原始sql语句加入基线，查看原始sql的基线（如果没有加入基线，可能查出来的时候no rows）

SQL> var temp number;
SQL> begin
  2  :temp:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'4tm6j886yvzj3',plan_hash_value=>4047680367);
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL>  select sql_handle,sql_text,plan_name from dba_sql_plan_baselines where sql_text like 'select * from test2 where object_id=20%';

SQL_HANDLE               SQL_TEXT                                 PLAN_NAME
------------------------------ -------------------------------------------------------------------------------- ------------------------------
SQL_20df29fdb3e8ac52           select * from test2 where object_id=20                    SQL_PLAN_21rt9zqtyjb2k60b1ef84

6.对之前的sql加hint，执行新的sql

SQL> select /*+full(test2)*/* from test2 where object_id=20;

7.获得新sql语句的sql_id和plan_hash_value
SQL> select sql_id,plan_hash_value,sql_text from v$sqlarea where sql_text like 'select /*+full(test2)*/%';

SQL_ID             PLAN_HASH_VALUE SQL_TEXT
-------------------- --------------- ------------------------------------------------------------
bqyuwxskwqdun           300966803 select /*+full(test2)*/* from test2 where object_id=20

8.将新的sql_id和plan_hash_value加入到原始sql的基线中
SQL> var temp number;
SQL> begin
  2  :temp:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'bqyuwxskwqdun',plan_hash_value=>300966803,sql_handle=>'SQL_20df29fdb3e8ac52');
  3  end;
  4  /

PL/SQL procedure successfully completed.

9.查看原始sql的基线

SQL> select sql_handle,sql_text,plan_name from dba_sql_plan_baselines where sql_text like 'select * from test2 where object_id=20%';

SQL_HANDLE               SQL_TEXT                             PLAN_NAME
------------------------------ ------------------------------------------------------------ ------------------------------
SQL_20df29fdb3e8ac52           select * from test2 where object_id=20                SQL_PLAN_21rt9zqtyjb2k60b1ef84
SQL_20df29fdb3e8ac52           select * from test2 where object_id=20                SQL_PLAN_21rt9zqtyjb2k99963deb

10.此时执行原始sql，他是走的之前的那个SQL_PLAN_21rt9zqtyjb2k60b1ef84
SQL> select * from test2 where object_id=20;


Execution Plan
----------------------------------------------------------
Plan hash value: 4047680367

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

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

   2 - access("OBJECT_ID"=20)

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

11.删除SQL_PLAN_21rt9zqtyjb2k60b1ef84，留下后面hint的那个

SQL> var temp number;
SQL> begin
  2  :temp:=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_20df29fdb3e8ac52',plan_name=>'SQL_PLAN_21rt9zqtyjb2k60b1ef84');
  3  end;
  4  /

PL/SQL procedure successfully completed.

12.查看原始sql的基线

SQL> select sql_handle,sql_text,plan_name from dba_sql_plan_baselines where sql_text like 'select * from test2 where object_id=20%';

SQL_HANDLE               SQL_TEXT                                 PLAN_NAME
------------------------------ -------------------------------------------------------------------------------- ------------------------------
SQL_20df29fdb3e8ac52           select * from test2 where object_id=20                    SQL_PLAN_21rt9zqtyjb2k99963deb

如上只有一个了，再次运行原始sql，查看执行计划

SQL>  select * from test2 where object_id=20;


Execution Plan
----------------------------------------------------------
Plan hash value: 300966803

---------------------------------------------------------------------------
| Id  | Operation      | Name  | Rows  | Bytes | Cost (%CPU)| Time      |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    1 |    98 |   344   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| TEST2 |    1 |    98 |   344   (1)| 00:00:05 |
---------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=20)

Note
-----
   - SQL plan baseline "SQL_PLAN_21rt9zqtyjb2k99963deb" used for this statement
此时走的执行计划是SQL_PLAN_21rt9zqtyjb2k99963deb，即hint的那个



实验二：使用oracle官方提供的coe_xfr_sql_profile.sql进行绑定

实验一已将执行计划绑定为不走索引，这里将执行计划绑定回走索引的

1.使用之前的走索引的sql_id和hash值直接进行绑定，运行脚本（sysdba权限）

SQL> @coe_xfr_sql_profile.sql
sql_id:4tm6j886yvzj3           
hash_plan_value:4047680367

Execute coe_xfr_sql_profile_4tm6j886yvzj3_4047680367.sql
on TARGET system in order to create a custom SQL Profile
with plan 4047680367 linked to adjusted sql_text.


COE_XFR_SQL_PROFILE completed.


SQL> @coe_xfr_sql_profile_4tm6j886yvzj3_4047680367.sql

        SIGNATURE
---------------------
  2368658098642005074


       SIGNATUREF
---------------------
 10962808917454791067


... manual custom SQL Profile has been created

2.查看基线
select sql_handle,sql_text,plan_name from dba_sql_plan_baselines where sql_text like 'select * from test2 where object_id=20%';

SQL_HANDLE               SQL_TEXT                             PLAN_NAME
------------------------------ ------------------------------------------------------------ ------------------------------
SQL_20df29fdb3e8ac52           select * from test2 where object_id=20                SQL_PLAN_21rt9zqtyjb2k60b1ef84
SQL_20df29fdb3e8ac52           select * from test2 where object_id=20                SQL_PLAN_21rt9zqtyjb2k99963deb


3.此时执行原始sql，并查看执行计划走的是哪一个

SQL> explain plan for  select * from test2 where object_id=20;

Explained.

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4047680367

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

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

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

|   0 | SELECT STATEMENT        |        |     1 |    98 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST2    |     1 |    98 |     2   (0)| 00:00:01 |

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

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


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


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

   2 - access("OBJECT_ID"=20)

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

这里看出是走的"coe_4tm6j886yvzj3_4047680367" 定义的执行计划。

4.删除"coe_4tm6j886yvzj3_4047680367"
$more coe_xfr_sql_profile_4tm6j886yvzj3_4047680367.sql 找到如下语句

To drop this custom SQL Profile after it has been created:
EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE('coe_4tm6j886yvzj3_4047680367');
PL/SQL procedure successfully completed.

5.查看基线并再次验证原始sql，并查看执行计划走的是哪一个

SQL>select sql_handle,sql_text,plan_name from dba_sql_plan_baselines where sql_text like 'select * from test2 where object_id=20%';

SQL_HANDLE               SQL_TEXT                             PLAN_NAME
------------------------------ ------------------------------------------------------------ ------------------------------
SQL_20df29fdb3e8ac52           select * from test2 where object_id=20                SQL_PLAN_21rt9zqtyjb2k60b1ef84
SQL_20df29fdb3e8ac52           select * from test2 where object_id=20                SQL_PLAN_21rt9zqtyjb2k99963deb

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

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 300966803

---------------------------------------------------------------------------
| Id  | Operation      | Name  | Rows  | Bytes | Cost (%CPU)| Time      |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    1 |    98 |   344   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| TEST2 |    1 |    98 |   344   (1)| 00:00:05 |
---------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------
   1 - filter("OBJECT_ID"=20)

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

可以看到，虽然在基线里面还可以看到coe_xfr_sql_profile_4tm6j886yvzj3_4047680367.sql指定的执行计划，但是在执行sql的时候并没有再使用这个执行计划

6.删除无用的SQL_PLAN_21rt9zqtyjb2k60b1ef84，留下之前hint的那个

SQL> var temp number;
SQL> begin
  2  :temp:=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_20df29fdb3e8ac52',plan_name=>'SQL_PLAN_21rt9zqtyjb2k60b1ef84');
  3  end;
  4  /
PL/SQL procedure successfully completed.

7.再次查看基线
SQL> select sql_handle,sql_text,plan_name from dba_sql_plan_baselines where sql_text like 'select * from test2 where object_id=20%';

SQL_HANDLE               SQL_TEXT                             PLAN_NAME
------------------------------ ------------------------------------------------------------ ------------------------------
SQL_20df29fdb3e8ac52           select * from test2 where object_id=20                SQL_PLAN_21rt9zqtyjb2k99963deb