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

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 statistics
Single Access Paths
Join Costs
Table Joins Considered
Join Methods Considered (NL/MS/HA)
 Level 1： 1級比2級更詳細，它包含2級的所有內容，在加如下內容：
Parameters used by the optimizer
Index 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>有點異常，刪除重新執行：

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