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):
------------------------------------------------