一　建立测试表
create table a nologging as select * from all_objects;
二　准备工作
找到sql_Id='aq03p7muwgvq5'
select* from V$sql where sql_text like '% from a where object_id=3%';
找到全表的outline:
方法一：dba_hist_sql_plan/v$sql_plan都可以

select extractvalue(value(d), '/hint') as outline_hintsfromxmltable('/*/outline_data/hint'passing (selectxmltype(other_xml) as xmlvalfromdba_hist_sql_planwheresql_id = '&sql_id'and plan_hash_value=&plan_hash_valueand other_xml is not null)) d

方法二：

select * from dbms_xplan.display_awr('aq03p7muwgvq5',0,'outline');/*+BEGIN_OUTLINE_DATAIGNORE_OPTIM_EMBEDDED_HINTSOPTIMIZER_FEATURES_ENABLE('11.2.0.4')DB_VERSION('11.2.0.4')ALL_ROWSOUTLINE_LEAF(@"SEL$1")FULL(@"SEL$1" "A"@"SEL$1")END_OUTLINE_DATA*/

declarev_hints sys.sqlprof_attr;v_sqltext clob;beginselect sql_fulltext into v_sqltext from v$sql where sql_id='aq03p7muwgvq5' and rownum<2;v_hints:=sys.sqlprof_attr(q'[FULL(@"SEL$1" "A"@"SEL$1")]');dbms_sqltune.import_sql_profile(v_sqltext,v_hints,'sql_full',force_match=>true,replace=>true);end;

建立索引
create index I_ind_object_id_com on a(object_id,object_name) nologging;
查看执行计划，并没有走索引：

 Execution Plan----------------------------------------------------------Plan hash value: 2248738933 --------------------------------------------------------------------------| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT|| 1 |98 | 177 (1)| 00:00:01 ||*1 |TABLE ACCESS FULL| A| 1 |98 | 177 (1)| 00:00:01 |-------------------------------------------------------------------------- Predicate Information (identified by operation id):---------------------------------------------------1 - filter("OBJECT_ID"=3) Note----- - SQL profile "sql_full" used for this statementStatistics----------------------------------------------------------7recursive calls0db block gets 1254consistent gets 1246physical reads0redo size 1606bytes sent via SQL*Net to client519bytes received via SQL*Net from client2SQL*Net roundtrips to/from client0sorts (memory)0sorts (disk)1rows processed

删除profile

begin dbms_sqltune.drop_sql_profile('sql_full'); end;

再次执行sql,找到走索引的outline

select * from dbms_xplan.display_awr('aq03p7muwgvq5',1,'outline');/*+BEGIN_OUTLINE_DATAIGNORE_OPTIM_EMBEDDED_HINTSOPTIMIZER_FEATURES_ENABLE('11.2.0.4')DB_VERSION('11.2.0.4')ALL_ROWSOUTLINE_LEAF(@"SEL$1")INDEX_RS_ASC(@"SEL$1" "A"@"SEL$1" ("A"."OBJECT_ID" "A"."OBJECT_NAME"))END_OUTLINE_DATA*/

declarev_hints sys.sqlprof_attr;v_sqltext clob;beginselect sql_fulltext into v_sqltext from v$sql where sql_id='aq03p7muwgvq5' and rownum<2;v_hints:=sys.sqlprof_attr(q'[INDEX_RS_ASC(@"SEL$1" "A"@"SEL$1" ("A"."OBJECT_ID" "A"."OBJECT_NAME"))]');dbms_sqltune.import_sql_profile(v_sqltext,v_hints,'sql_comp_ind',force_match=>true,replace=>true);end;

建立更优的索引
create index I_ind_object_id on a(object_Id) nologging;
再次查看执行计划，并没有走更优的索引

Execution Plan----------------------------------------------------------Plan hash value: 3075844428 ---------------------------------------------------------------------------------------------------| Id| Operation | Name| Rows| Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT| | 1 |98 | 3 (0)| 00:00:01 || 1 |TABLE ACCESS BY INDEX ROWID| A | 1 |98 | 3 (0)| 00:00:01 ||*2 | INDEX RANGE SCAN| I_IND_OBJECT_ID_COM | 1 | | 2 (0)| 00:00:01 |------------------------------------------------------------------------ Predicate Information (identified by operation id):--------------------------------------------------2 - access("OBJECT_ID"=3) Note----- - SQL profile "sql_comp_ind" used for this statement Statistics

7recursive calls0db block gets 10consistent gets1physical reads0redo size 1609bytes sent via SQL*Net to client519bytes received via SQL*Net from client2SQL*Net roundtrips to/from client0sorts (memory)0sorts (disk)1rows processed

当然你也可以用SQLT里的coe_xfr_sql_profile.sql或者create_sql_profile.sql生成sql_profile;

这里有一点比较扯的是用完整的outline，写进去不报错，但执行计划不走sql_profile里约定的内容；

declarev_hints sys.sqlprof_attr;v_sqltext clob;beginselect sql_fulltext into v_sqltext from v$sql where sql_id='aq03p7muwgvq5' and rownum<2;v_hints:=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" "A"@"SEL$1")]',q'[END_OUTLINE_DATA]');dbms_sqltune.import_sql_profile(v_sqltext,v_hints,'sql_full',force_match=>true,replace=>true);end;

做10053：

select value from v$parameter where name='sql_trace';alter session set sql_trace=true;alter session set tracefile_identifier='mytrace';

--single instance
select tracefile from v$process where addr in (select paddr from v$session where sid in (select sid from v$Mystat where rownum<2));
--/u01/app/oracle/diag/rdbms/orcl/ORCL/trace/ORCL_ora_22354_mytrace.trc
ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';
select * from a where object_id=3

/*Level 2：2级是1级的一个子集，它包含以下内容：Column statisticsSingle Access PathsJoin CostsTable Joins ConsideredJoin Methods Considered (NL/MS/HA) Level 1： 1级比2级更详细，它包含2级的所有内容，在加如下内容：Parameters used by the optimizerIndex statistics */

ALTER SESSION SET EVENTS '10053 trace name context off';
没有看出什么异常，和traceonly看到的一致，做sql_trace跟踪sql_profile写入的基表sys.sqlobj$data：
select to_char(comp_data),signature from sys.sqlobj$data where plan_Id=0
发现：

<outline_data<hint><![CDATA[BEGIN_OUTLINE_DATA]]></hint><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]></hint><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]]></hint><hint><![CDATA[DB_VERSION('11.2.0.4')],q'[ALL_ROWS]]></hint><hint><![CDATA[OUTLINE_LEAF(@"SEL$1")]]></hint><hint><![CDATA[FULL(@"SEL$1" "A"@"SEL$1")]]></hint><hint><![CDATA[END_OUTLINE_DATA]]></hint></outline_data>

这一段里，<hint><![CDATA[DB_VERSION('11.2.0.4')],q'[ALL_ROWS]]></hint>有点异常，删除重新执行：

declarev_hints sys.sqlprof_attr;v_sqltext clob;beginselect sql_fulltext into v_sqltext from v$sql where sql_id='aq03p7muwgvq5' and rownum<2;v_hints:=sys.sqlprof_attr(q'[BEGIN_OUTLINE_DATA]',q'[IGNORE_OPTIM_EMBEDDED_HINTS]',q'[OPTIMIZER_FEATURES_ENABLE('11.2.0.4')]',q'[ALL_ROWS]',q'[OUTLINE_LEAF(@"SEL$1")]',q'[FULL(@"SEL$1" "A"@"SEL$1")]',q'[END_OUTLINE_DATA]');dbms_sqltune.import_sql_profile(v_sqltext,v_hints,'sql_full',force_match=>true,replace=>true);end;

正常：

Execution Plan----------------------------------------------------------Plan hash value: 2248738933 --------------------------------------------------------------------------| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT|| 1 |98 | 177 (1)| 00:00:01 ||*1 |TABLE ACCESS FULL| A| 1 |98 | 177 (1)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - filter("OBJECT_ID"=3) Note----- - SQL profile "sql_full" used for this statement Statistics----------------------------------------------------------1recursive calls0db block gets 1248consistent gets 1245physical reads0redo size 1606bytes sent via SQL*Net to client519bytes received via SQL*Net from client2SQL*Net roundtrips to/from client0sorts (memory)0sorts (disk)1rows processed