sql profile简介

关于绑定sql的执行计划个人一直用的是oracle的sql plan baseline，其实oracle 10g就推出了sql profile来绑定执行计划，下面来简单的了解下oracle的sql profile的功能。 sql profile大体是实现两个功能： 1绑定现有sql的执行计划 2 在不修改代码的情况下使目

关于绑定sql的执行计划个人一直用的是oracle的sql plan baseline，其实oracle 10g就推出了sql profile来绑定执行计划，下面来简单的了解下oracle的sql profile的功能。

sql profile大体是实现两个功能：
1绑定现有sql的执行计划
2 在不修改代码的情况下使目标sql语句按照执行的执行计划执行，这两个功能sql plan baseline也是可以实现的，而比sql profile更加优秀的就是sql plan baseline还能够在sql运行是生成更优秀的执行计划基线，我们可以演练这个新的sql plan baseline来决定是否采用这个sql plan baseline。

sql profile有两种类型：一种是automatic类型，另一种是manual类型

automatic类型的sql profile是针对目标sql获取到一些额外的调整信息（类似oracle的动态采样来采集额外的信息），这些信息存储在数据字典中，当有了automatic类型的sql profile后，优化器产生执行计划时会根据目标sql所涉及统计信息等内容做相应的调整来保证选择最优的执行计划。

需要注意的automatic的sql profile并不是像stored outlines、sql plan baseline那样锁定目标sql的执行计划，automatic的sql profile在原则上只是提供了一些额外的统计信息，这些额外的统计信息必须于原目标sql的涉及的相关统计内容一起作用才能得到新的执行计划，如果原sql的统计信息等内容发生重大变化，即使原有的automatic类型的sql profile没有改变，该sql的执行计划也可能会发生变化，接下来xiaoyu会贴出部分case以供大家参考。

SQL>create table t_auto01 as select * from dba_objects;
SQL>create index ind_objectid on t_auto01(object_id);
SQL> select max(object_id) from t_auto01;

MAX(OBJECT_ID)
--------------
         87823
SQL>update  t_auto01 set object_id=100000  where object_id SQL>commit;
SQL>exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'T_AUTO01',cascade=>true);

SQL>set autotrace traceonly;
SQL> select /*+index(t_auto01 ind_objectid)*/* from t_auto01 where object_id=100000;

86366 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 4224651809

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              | 86348 |  8095K|  1388   (1)| 00:00:17 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_AUTO01     | 86348 |  8095K|  1388   (1)| 00:00:17 |
|*  2 |   INDEX RANGE SCAN          | IND_OBJECTID | 86348 |       |   153   (0)| 00:00:02 |
--------------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=100000)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      12806  consistent gets
          0  physical reads
          0  redo size
    9767726  bytes sent via SQL*Net to client
      63850  bytes received via SQL*Net from client
       5759  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      86366  rows processed

用STA生成automatic sql profile

SQL> select sql_id from v$sql where sql_text like 'select /*+index(t_auto01 ind_objectid)*/* from t_auto01 where object_id=100000%';

SQL_ID
-------------
5tvdfn4y8z5gg

declare
my_task_name varchar2(30);
begin
my_task_name:=dbms_sqltune.create_tuning_task(
sql_id=>'601ccgpfh9jcv',
scope=>'COMPREHENSIVE',
task_name=>'my_tuning_task'
);
dbms_sqltune.execute_tuning_task(task_name=>'my_tuning_task');
end;
/

SQL> set long 100000
SQL> select dbms_sqltune.report_tuning_task(task_name=>'my_tuning_task') from dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK(TASK_NAME=>'MY_TUNING_TASK')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : my_tuning_task
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 12/09/2014 19:01:37
Completed at       : 12/09/2014 19:01:37

-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID     : 5tvdfn4y8z5gg
SQL Text   : select /*+index(t_auto01 ind_objectid)*/* from t_auto01 where
             object_id=100000

-------------------------------------------------------------------------------
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: 10.83%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name => 'my_tuning_task',
            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):             .018247           .007709      57.75 %
  CPU Time (s):                 .002199           .000699      68.21 %
  User I/O Time (s):                  0                 0
  Buffer Gets:                     1386              1236      10.82 %
  Physical Read Requests:             0                 0
  Physical Write Requests:            0                 0
  Physical Read Bytes:                0                 0
  Physical Write Bytes:               0                 0
  Rows Processed:                 86366             86366
  Fetches:                        86366             86366
  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: 4224651809

--------------------------------------------------------------------------------
------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)
| Time     |
--------------------------------------------------------------------------------
------------
|   0 | SELECT STATEMENT            |              | 86348 |  8095K|  1388   (1)
| 00:00:17 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_AUTO01     | 86348 |  8095K|  1388   (1)
| 00:00:17 |
|*  2 |   INDEX RANGE SCAN          | IND_OBJECTID | 86348 |       |   153   (0)
| 00:00:02 |
--------------------------------------------------------------------------------
------------

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

   2 - access("OBJECT_ID"=100000)

2- Using SQL Profile
--------------------
Plan hash value: 795571617

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          | 86348 |  8095K|   336   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| T_AUTO01 | 86348 |  8095K|   336   (1)| 00:00:05 |
------------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=100000)

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

antomatic sql profile已经生效：

SQL>execute dbms_sqltune.accept_sql_profile(task_name => 'my_tuning_task’, task_owner => 'SYS', replace => TRUE);
SQL> select /*+index(t_auto01 ind_objectid)*/* from t_auto01 where object_id=100000;

86366 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 795571617

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          | 86348 |  8095K|   336   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| T_AUTO01 | 86348 |  8095K|   336   (1)| 00:00:05 |
------------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=100000)

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

即使此时sql text有出现大小写、空格类、换行等的变化，sql profile依然可以生效

SQL> select /*+index(t_auto01 ind_objectid)*/* from T_AUTO01  where object_id=100000;

86366 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 795571617

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          | 86348 |  8095K|   336   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| T_AUTO01 | 86348 |  8095K|   336   (1)| 00:00:05 |
------------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=100000)

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

即使表发生了ddl，只要不影响原来的sql执行，相应的sql profile依然生效。

SQL> alter table t_auto01 add edate date;

Table altered.

SQL> select /*+index(t_auto01 ind_objectid)*/* from T_AUTO01  where object_id=100000;

86366 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 795571617

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          | 86348 |  8095K|   336   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| T_AUTO01 | 86348 |  8095K|   336   (1)| 00:00:05 |
------------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=100000)

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

如果我们具体谓词条件对应的具体值出现了变化，此时sql profile是没有办法生效的。

SQL> select /*+index(t_auto01 ind_objectid)*/* from t_auto01 where object_id=1000001;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 4224651809

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

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

   2 - access("OBJECT_ID"=1000001)

当然如果sql text出现了类似下列的变更，同样sql profile也无法生效。

SQL> select /*+index(t_auto01 ind_objectid)*/* from t_auto01 a where object_id=100000;

86366 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 795571617

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          | 86348 |  8095K|   336   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| T_AUTO01 | 86348 |  8095K|   336   (1)| 00:00:05 |
------------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=100000)

那么接下来还有一个问题，就是如果我们的应用程序没有写bind value，但是我们要绑定一系列的值都走同一类的profile，其实这个也比较容易，oracle提供的dbms_sqltune.accept_sql_profile中有个参数是force_match参数，该参数默认是false，当修改为true后，谓词的具体值即使发生了变化，sql profile依然生效。

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

PL/SQL procedure successfully completed.

SQL> select /*+index(t_auto01 ind_objectid)*/* from T_AUTO01  where object_id=100001;

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 4224651809

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

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

   2 - access("OBJECT_ID"=100001)

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


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

我们发现此时加上了force_matching=>true参数后，谓词具体对应值变化的sql走了一个新的sql profile SYS_SQLPROF_014a323f8ddc0001，而且这个sql profile并不走SYS_SQLPROF_014a3230758f0000的全表扫描的执行计划，而是走更加高效的index range scan，这里需要明确加上force_match=>true新生成的sql profile不一定跟之前的sql profile执行计划一样，因为automatic sql profile仅仅只是一些额外的统计信息来保证生成更准确高效的执行计划。

同样还需要注意的即使是同一个sql profile也可能会产生不同的执行计划：

SQL> exec dbms_sqltune.drop_sql_profile(‘SYS_SQLPROF_014a323f8ddc0001’);
SQL> exec dbms_sqltune.drop_sql_profile(‘SYS_SQLPROF_014a3230758f0000’);
SQL> exec dbms_sqltune.drop_tuning_task(task_name=>'my_tuning_task');

重新生成一个sql profile
SQL> declare
  2  my_task_name varchar2(30);
  3  begin
  4  my_task_name:=dbms_sqltune.create_tuning_task(
  5  sql_id=>'601ccgpfh9jcv',
  6  scope=>'COMPREHENSIVE',
  7  task_name=>'my_tuning_task'
  8  );
  9  dbms_sqltune.execute_tuning_task(task_name=>'my_tuning_task');
 10  end;
 11  /

PL/SQL procedure successfully completed.

SQL> set long 199999
SQL> select dbms_sqltune.report_tuning_task(task_name=>'my_tuning_task') from dual;

DBMS_SQLTUNE.REPORT_TUNING_TASK(TASK_NAME=>'MY_TUNING_TASK')
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : my_tuning_task
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 12/09/2014 19:57:36
Completed at       : 12/09/2014 19:57:36

-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID     : 601ccgpfh9jcv
SQL Text   : select /*+index(t_auto01 ind_objectid)*/* from T_AUTO01  where
             object_id=100000

-------------------------------------------------------------------------------
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: 10.82%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name => 'my_tuning_task',
            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):             .021363            .00914      57.21 %
  CPU Time (s):                 .021396           .009198      57.01 %
  User I/O Time (s):                  0                 0
  Buffer Gets:                     1385              1236      10.75 %
  Physical Read Requests:             0                 0
  Physical Write Requests:            0                 0
  Physical Read Bytes:                0                 0
  Physical Write Bytes:               0                 0
  Rows Processed:                 86371             86371
  Fetches:                        86371             86371
  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: 4224651809

--------------------------------------------------------------------------------
------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)
| Time     |
--------------------------------------------------------------------------------
------------
|   0 | SELECT STATEMENT            |              | 86363 |  8096K|  1386   (1)
| 00:00:17 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_AUTO01     | 86363 |  8096K|  1386   (1)
| 00:00:17 |
|*  2 |   INDEX RANGE SCAN          | IND_OBJECTID | 86363 |       |   152   (0)
| 00:00:02 |
--------------------------------------------------------------------------------
------------

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

   2 - access("OBJECT_ID"=100000)

2- Using SQL Profile
--------------------
Plan hash value: 795571617

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          | 86363 |  8096K|   336   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| T_AUTO01 | 86363 |  8096K|   336   (1)| 00:00:05 |
------------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=100000)

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

force_match方式accept sql profile：

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

PL/SQL procedure successfully completed.

SQL> select /*+index(t_auto01 ind_objectid)*/* from T_AUTO01  where object_id=100000;

86371 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 795571617

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          | 86363 |  8096K|   336   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| T_AUTO01 | 86363 |  8096K|   336   (1)| 00:00:05 |
------------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=100000)

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


Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
       6913  consistent gets
          1  physical reads
          0  redo size
    4045316  bytes sent via SQL*Net to client
      63861  bytes received via SQL*Net from client
       5760  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      86371  rows processed

SQL> select /*+index(t_auto01 ind_objectid)*/* from T_AUTO01  where object_id=1;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 4224651809

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

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

   2 - access("OBJECT_ID"=1)

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


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

更新t_auto01表的数据全部为object_id=1，造成数据倾斜

SQL> update t_auto01 set object_id=1;
86371 rows updated.
SQL> commit;
Commit complete.

这里的sql profile对应的执行计划依然是index range scan

SQL> select /*+index(t_auto01 ind_objectid)*/* from T_AUTO01  where object_id=1;

86371 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 4224651809

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

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

   2 - access("OBJECT_ID"=1)

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

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      12957  consistent gets
          0  physical reads
          0  redo size
    9768433  bytes sent via SQL*Net to client
      63861  bytes received via SQL*Net from client
       5760  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      86371  rows processed

重新收集下统计信息：

SQL> exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'T_AUTO01',cascade=>true);

PL/SQL procedure successfully completed.

SQL> select /*+index(t_auto01 ind_objectid)*/* from T_AUTO01  where object_id=1;

86371 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 795571617

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          | 86363 |  8096K|   336   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| T_AUTO01 | 86363 |  8096K|   336   (1)| 00:00:05 |
------------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=1)

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


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      12957  consistent gets
          0  physical reads
          0  redo size
    9768433  bytes sent via SQL*Net to client
      63861  bytes received via SQL*Net from client
       5760  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      86371  rows processed

sql profle对应的执行计划变成了full table scan。

这里我们需要明确的是如果重新收集了统计信息，automatic的sql profile的执行计划是有可能会发生变化的。

由于automatic sql profile只是一些额外的统计信息，这个将导致如果sql涉及的表统计信息发生变化，automatic的sql profile将无法绑定目标sql的执行计划。

下面来介绍下manual类型的sql profile，manual sql profile实际上就是一堆hint的组合，它能很好的绑定目标sql的执行计划，这个跟automatic sql profile是不相同的。

manual类型的sql profile同样可以在不改变目标sql的sql文本情况下调整其执行计划，

SQL> create table t_manual01 as select * from dba_objects;

Table created.

SQL> create index ind_manual_objid on t_manual01(object_id);

Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'T_MANUAL01',cascade=>true);

PL/SQL procedure successfully completed.

SQL> select /*+index(t_manual01 ind_manual_objid)*/* from t_manual01 where object_id=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 371934742

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

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

   2 - access("OBJECT_ID"=1)

SQL> update t_manual01 set object_id=1;

86320 rows updated.
SQL> commit;

Commit complete.

此时由于全部object_id为1，全表扫描将更加适合

SQL> select /*+index(t_manual01 ind_manual_objid)*/* from t_manual01 where object_id=1;

Execution Plan
----------------------------------------------------------
Plan hash value: 371934742

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

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

   2 - access("OBJECT_ID"=1)

SQL> select /*+full(t_manual01)*/* from t_manual01 where object_id=1;

86320 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1705140427

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

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

   1 - filter("OBJECT_ID"=1)

SQL> select sql_id,plan_hash_value,sql_text from v$sql where sql_text like 'select /*+index(t_manual01 ind_manual_objid)*/* from t_manual01 where object_id=1%';

SQL_ID        PLAN_HASH_VALUE SQL_TEXT
------------- --------------- ------------------------------------------------------------
5usjcvmsxj6mb   371934742 select /*+index(t_manual01 ind_manual_objid)*/* from t_manual01 where object_id=1


SQL> select sql_id,plan_hash_value,sql_text from v$sql where sql_text like 'select /*+full(t_manual01)*/* from t_manual01 where object_id=1%';

SQL_ID        PLAN_HASH_VALUE SQL_TEXT
------------- --------------- ------------------------------------------------------------
fp5ng25383cvk      1705140427 select /*+full(t_manual01)*/* from t_manual01 where object_id=1

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5usjcvmsxj6mb, child number 0
-------------------------------------
select /*+index(t_manual01 ind_manual_objid)*/* from t_manual01 where
object_id=1

Plan hash value: 371934742

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

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / T_MANUAL01@SEL$1
   2 - SEL$1 / T_MANUAL01@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "T_MANUAL01"@"SEL$1" ("T_MANUAL01"."OBJECT_ID"))
      END_OUTLINE_DATA
  */

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fp5ng25383cvk, child number 0
-------------------------------------
select /*+full(t_manual01)*/* from t_manual01 where object_id=1

Plan hash value: 1705140427

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

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1 / T_MANUAL01@SEL$1

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T_MANUAL01"@"SEL$1")
      END_OUTLINE_DATA
  */

这种移花接木的基本思路为：
1 coe_xfr_sql_profile.sql脚本生成目标sql的manual sql profile脚本
2 针对目标sql添加hint，直到sql能走出你需要的执行计划，针对这个sql用coe_xfr_sql_profile.sql脚本生成manual sql profile脚本
3 用修改后的sql生成的manual sql profile脚本中的outline data部分替换掉目标sql对应的manual sql profile脚本
4 执行目标sql的manual sql profile脚本生成manual sql profile

SQL> @coe_xfr_sql_profile.sql

Parameter 1:
SQL_ID (required)

Enter value for 1: 5usjcvmsxj6mb


PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
      371934742        .052

Parameter 2:
PLAN_HASH_VALUE (required)

Enter value for 2: 371934742

Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID         : "5usjcvmsxj6mb"
PLAN_HASH_VALUE: "371934742"

SQL>BEGIN
  2    IF :sql_text IS NULL THEN
  3      RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');
  4    END IF;
  5  END;
  6  /
SQL>SET TERM OFF;
SQL>BEGIN
  2    IF :other_xml IS NULL THEN
  3      RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');
  4    END IF;
  5  END;
  6  /
SQL>SET TERM OFF;

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


COE_XFR_SQL_PROFILE completed.

SQL> @coe_xfr_sql_profile.sql

Parameter 1:
SQL_ID (required)

Enter value for 1: fp5ng25383cvk


PLAN_HASH_VALUE AVG_ET_SECS
--------------- -----------
     1705140427         .06

Parameter 2:
PLAN_HASH_VALUE (required)

Enter value for 2: 1705140427

Values passed to coe_xfr_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL_ID         : "fp5ng25383cvk"
PLAN_HASH_VALUE: "1705140427"

SQL>BEGIN
  2    IF :sql_text IS NULL THEN
  3      RAISE_APPLICATION_ERROR(-20100, 'SQL_TEXT for SQL_ID &&sql_id. was not found in memory (gv$sqltext_with_newlines) or AWR (dba_hist_sqltext).');
  4    END IF;
  5  END;
  6  /
SQL>SET TERM OFF;
SQL>BEGIN
  2    IF :other_xml IS NULL THEN
  3      RAISE_APPLICATION_ERROR(-20101, 'PLAN for SQL_ID &&sql_id. and PHV &&plan_hash_value. was not found in memory (gv$sql_plan) or AWR (dba_hist_sql_plan).');
  4    END IF;
  5  END;
  6  /
SQL>SET TERM OFF;

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


COE_XFR_SQL_PROFILE completed.

生成两个sql_id脚本后

[oracle@redhat_ora11g ~]$ vi coe_xfr_sql_profile_5usjcvmsxj6mb_371934742.sql
。。。
DECLARE
sql_txt CLOB;
h       SYS.SQLPROF_ATTR;
BEGIN
sql_txt := q'[
select /*+index(t_manual01 ind_manual_objid)
*/* from t_manual01 where object_id=1
]';
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_RS_ASC(@"SEL$1" "T_MANUAL01"@"SEL$1" ("T_MANUAL01"."OBJECT_ID"))]',
q'[END_OUTLINE_DATA]');
:signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
sql_text    => sql_txt,
profile     => h,
name        => 'coe_5usjcvmsxj6mb_371934742',
description => 'coe 5usjcvmsxj6mb 371934742 '||:signature||'',
category    => 'DEFAULT',
validate    => TRUE,
replace     => TRUE,
force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
END;
/
WHENEVER SQLERROR CONTINUE
SET ECHO OFF;
。。。

[oracle@redhat_ora11g ~]$ more coe_xfr_sql_profile_fp5ng25383cvk_1705140427.sql
。。。
DECLARE
sql_txt CLOB;
h       SYS.SQLPROF_ATTR;
BEGIN
sql_txt := q'[
select /*+full(t_manual01)
*/* from t_manual01 where object_id=1
]';
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" "T_MANUAL01"@"SEL$1")]',
q'[END_OUTLINE_DATA]');
:signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
sql_text    => sql_txt,
profile     => h,
name        => 'coe_fp5ng25383cvk_1705140427',
description => 'coe fp5ng25383cvk 1705140427 '||:signature||'',
category    => 'DEFAULT',
validate    => TRUE,
replace     => TRUE,
force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
END;
/
WHENEVER SQLERROR CONTINUE
SET ECHO OFF;
。。。

force_match => FALSE根据实际情况修改，默认为false，如果修改为true，谓词的具体值发生变化，sql profile依然生效。

将目标sql也就是coe_xfr_sql_profile_5usjcvmsxj6mb_371934742.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_RS_ASC(@"SEL$1" "T_MANUAL01"@"SEL$1" ("T_MANUAL01"."OBJECT_ID"))]',
q'[END_OUTLINE_DATA]');

替换为coe_xfr_sql_profile_fp5ng25383cvk_1705140427.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" "T_MANUAL01"@"SEL$1")]',
q'[END_OUTLINE_DATA]');
。。。

这里也很好理解，其实manual sql profile也就是Outline Data的信息，manual sql profile就是用Outline Data来固化sql的执行计划。

替换完毕后再次执行脚本就将目标sql绑定了一个manual sql profile

SQL> @coe_xfr_sql_profile_5usjcvmsxj6mb_371934742.sql
...
SQL> DECLARE
  2  sql_txt CLOB;
  3  h       SYS.SQLPROF_ATTR;
  4  BEGIN
  5  sql_txt := q'[
  6  select /*+index(t_manual01 ind_manual_objid)
  7  */* from t_manual01 where object_id=1
  8  ]';
  9  h := SYS.SQLPROF_ATTR(
 10  q'[BEGIN_OUTLINE_DATA]',
 11  q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
 12  q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',
 13  q'[DB_VERSION('11.2.0.4')]',
 14  q'[ALL_ROWS]',
 15  q'[OUTLINE_LEAF(@"SEL$1")]',
 16  q'[FULL(@"SEL$1" "T_MANUAL01"@"SEL$1")]',
 17  q'[END_OUTLINE_DATA]');
 18  :signature := DBMS_SQLTUNE.SQLTEXT_TO_SIGNATURE(sql_txt);
 19  DBMS_SQLTUNE.IMPORT_SQL_PROFILE (
 20  sql_text    => sql_txt,
 21  profile     => h,
 22  name        => 'coe_5usjcvmsxj6mb_371934742',
 23  description => 'coe 5usjcvmsxj6mb 371934742 '||:signature||'',
 24  category    => 'DEFAULT',
 25  validate    => TRUE,
 26  replace     => TRUE,
 27  force_match => FALSE /* TRUE:FORCE (match even when different literals in SQL). FALSE:EXACT (similar to CURSOR_SHARING) */ );
 28  END;
 29  /

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly;
SQL> select /*+index(t_manual01 ind_manual_objid)*/* from t_manual01 where object_id=1;

86320 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1705140427

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

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