oracle sql_profile绑定异常处置

oracle sql_profile绑定异常处置：

　　一　建立测试表

　　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_hints

　　from

　　xmltable('/*/outline_data/hint'

　　passing (

　　select

　　xmltype(other_xml) as xmlval

　　from

　　dba_hist_sql_plan

　　where

　　sql_id = '&sql_id'

　　and plan_hash_value=&plan_hash_value

　　and other_xml is not null)) d

　　方法二：

　　select * from dbms_xplan.display_awr('aq03p7muwgvq5',0,'outline');

　　/*+

　　    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" "A"@"SEL$1")

　　    END_OUTLINE_DATA

　　*/

　　declare

　　  v_hints sys.sqlprof_attr;

　　  v_sqltext clob;

　　begin

　　  select 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 statement

　　Statistics

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

　　          7  recursive calls

　　          0  db block gets

　　       1254  consistent gets

　　       1246  physical reads

　　          0  redo size

　　       1606  bytes sent via SQL*Net to client

　　        519  bytes received via SQL*Net from client

　　          2  SQL*Net roundtrips to/from client

　　          0  sorts (memory)

　　          0  sorts (disk)

　　          1  rows processed

　　删除profile

　　begin

　　   dbms_sqltune.drop_sql_profile('sql_full');

　　   end;

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

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

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

　　declare

　　  v_hints sys.sqlprof_attr;

　　  v_sqltext clob;

　　begin

　　  select 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;