关于绑定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_idsql>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):
------------------------------------------------