【转载】sql_profile快速绑定脚本

原文地址：http://blog.itpub.net/10972173/viewspace-674702

declare
  ar_hint_table    sys.dbms_debug_vc2coll;
  ar_profile_hints sys.sqlprof_attr := sys.sqlprof_attr();
  cl_sql_text      clob;
  i                pls_integer;
begin
  with a as (
  select
           rownum as r_no
         , a.*
  from
           table(
             -- replace with
             -- DBMS_XPLAN.DISPLAY_AWR
             -- if required
             dbms_xplan.display_cursor(
               '&&good_sql_id'
             ,  null
             , 'OUTLINE'
             )
             -- dbms_xplan.display_awr(
             --   '1'
             -- , null
             -- , null
             -- , 'OUTLINE'
             -- )
           ) a
  ),
  b as (
  select
           min(r_no) as start_r_no
  from
           a
  where
           a.plan_table_output = 'Outline Data'
  ),
  c as (
  select
           min(r_no) as end_r_no
  from
           a
         , b
  where
           a.r_no > b.start_r_no
  and      a.plan_table_output = '  */'
  ),
  d as (
  select
           instr(a.plan_table_output, 'BEGIN_OUTLINE_DATA') as start_col
  from
           a
         , b
  where
           r_no = b.start_r_no + 4
  )
  select
           substr(a.plan_table_output, d.start_col) as outline_hints
  bulk collect
  into
           ar_hint_table
  from
           a
         , b
         , c
         , d
  where
           a.r_no >= b.start_r_no + 4
  and      a.r_no <= c.end_r_no - 1
  order by
           a.r_no;
 
  select
           sql_text
  into
           cl_sql_text
  from
           -- replace with dba_hist_sqltext
           -- if required for AWR based
           -- execution
           v$sql
           -- sys.dba_hist_sqltext
  where
           sql_id = '&&bad_sql_id';
 
  -- this is only required
  -- to concatenate hints
  -- splitted across several lines
  -- and could be done in SQL, too
  i := ar_hint_table.first;
  while i is not null
  loop
    if ar_hint_table.exists(i + 1) then
      if substr(ar_hint_table(i + 1), 1, 1) = ' ' then
        ar_hint_table(i) := ar_hint_table(i) || trim(ar_hint_table(i + 1));
        ar_hint_table.delete(i + 1);
      end if;
    end if;
    i := ar_hint_table.next(i);
  end loop;
 
  i := ar_hint_table.first;
  while i is not null
  loop
    ar_profile_hints.extend;
    ar_profile_hints(ar_profile_hints.count) := ar_hint_table(i);
    i := ar_hint_table.next(i);
  end loop;
 
  dbms_sqltune.import_sql_profile(
    sql_text    => cl_sql_text
  , profile     => ar_profile_hints
  , name        => '&&PROFILE_name'
  -- use force_match => true
  -- to use CURSOR_SHARING=SIMILAR
  -- behaviour, i.e. match even with
  -- differing literals
  , force_match => true
  );
end;
/

 使得sql_profile的绑定更易操作，只需要确定good_sql_id, bad_sql_id, PROFILE_name。即可快速绑定。