代码改变世界

【转载】sql_profile快速绑定脚本

2014-06-03 23:41  AlfredZhao  阅读(697)  评论(0)  编辑  收藏  举报

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

  1 declare
  2   ar_hint_table    sys.dbms_debug_vc2coll;
  3   ar_profile_hints sys.sqlprof_attr := sys.sqlprof_attr();
  4   cl_sql_text      clob;
  5   i                pls_integer;
  6 begin
  7   with a as (
  8   select
  9            rownum as r_no
 10          , a.*
 11   from
 12            table(
 13              -- replace with
 14              -- DBMS_XPLAN.DISPLAY_AWR
 15              -- if required
 16              dbms_xplan.display_cursor(
 17                '&&good_sql_id'
 18              ,  null
 19              , 'OUTLINE'
 20              )
 21              -- dbms_xplan.display_awr(
 22              --   '1'
 23              -- , null
 24              -- , null
 25              -- , 'OUTLINE'
 26              -- )
 27            ) a
 28   ),
 29   b as (
 30   select
 31            min(r_no) as start_r_no
 32   from
 33            a
 34   where
 35            a.plan_table_output = 'Outline Data'
 36   ),
 37   c as (
 38   select
 39            min(r_no) as end_r_no
 40   from
 41            a
 42          , b
 43   where
 44            a.r_no > b.start_r_no
 45   and      a.plan_table_output = '  */'
 46   ),
 47   d as (
 48   select
 49            instr(a.plan_table_output, 'BEGIN_OUTLINE_DATA') as start_col
 50   from
 51            a
 52          , b
 53   where
 54            r_no = b.start_r_no + 4
 55   )
 56   select
 57            substr(a.plan_table_output, d.start_col) as outline_hints
 58   bulk collect
 59   into
 60            ar_hint_table
 61   from
 62            a
 63          , b
 64          , c
 65          , d
 66   where
 67            a.r_no >= b.start_r_no + 4
 68   and      a.r_no <= c.end_r_no - 1
 69   order by
 70            a.r_no;
 71  
 72   select
 73            sql_text
 74   into
 75            cl_sql_text
 76   from
 77            -- replace with dba_hist_sqltext
 78            -- if required for AWR based
 79            -- execution
 80            v$sql
 81            -- sys.dba_hist_sqltext
 82   where
 83            sql_id = '&&bad_sql_id';
 84  
 85   -- this is only required
 86   -- to concatenate hints
 87   -- splitted across several lines
 88   -- and could be done in SQL, too
 89   i := ar_hint_table.first;
 90   while i is not null
 91   loop
 92     if ar_hint_table.exists(i + 1) then
 93       if substr(ar_hint_table(i + 1), 1, 1) = ' ' then
 94         ar_hint_table(i) := ar_hint_table(i) || trim(ar_hint_table(i + 1));
 95         ar_hint_table.delete(i + 1);
 96       end if;
 97     end if;
 98     i := ar_hint_table.next(i);
 99   end loop;
100  
101   i := ar_hint_table.first;
102   while i is not null
103   loop
104     ar_profile_hints.extend;
105     ar_profile_hints(ar_profile_hints.count) := ar_hint_table(i);
106     i := ar_hint_table.next(i);
107   end loop;
108  
109   dbms_sqltune.import_sql_profile(
110     sql_text    => cl_sql_text
111   , profile     => ar_profile_hints
112   , name        => '&&PROFILE_name'
113   -- use force_match => true
114   -- to use CURSOR_SHARING=SIMILAR
115   -- behaviour, i.e. match even with
116   -- differing literals
117   , force_match => true
118   );
119 end;
120 /

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