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_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_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 statementStatistics----------------------------------------------------------          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

select * from dbms_xplan.display_awr('aq03p7muwgvq5',1,'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")      INDEX_RS_ASC(@"SEL$1" "A"@"SEL$1" ("A"."OBJECT_ID" "A"."OBJECT_NAME"))      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'[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

      7  recursive calls      0  db block gets     10  consistent gets      1  physical reads      0  redo size   1609  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

当然你也可以用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;

做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
发现：

这一段里，有点异常，删除重新执行：

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'[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----------------------------------------------------------          1  recursive calls          0  db block gets       1248  consistent gets       1245  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