oracle通过sql patch 为sql语句加hint

     有时候，sql 语句执行计划选择错误我们可以通过hint 方式更正执行计划，但是添加hint需要调整sql意味着需要对程序代码进行改动，这对一些生产环境可能是不允许的。从11g开始，可以通过sql patch 不改动sql语句为sql增加hint从而达到优化的目的。

对于绑定变量sql，可以采用如下方式:

11g:

--创建sql patch

declare
  c clob;
  hint_text varchar2(4000);

begin
select sql_fulltext into c from gv$sql where sql_id='&sql_id' and rownum=1;
hint_text:='&hint’ ;
  sys.dbms_sqldiag_internal.i_create_patch
   (sql_text  => c,
    hint_text => hint_text,
    name      => 'sql_patch_&sql_id');
end;
/

12c以及以上：

--创建sql patch

declare
  hint_text varchar2(4000);

begin
hint_text:='&hint' ;
  sys.dbms_sqldiag_internal.i_create_patch
   (sql_id =>’&sql_id’,
    hint_text => hint_text,
    creator=>'SYS',
    name      => 'sql_patch_&sql_id');
end;
/

--删除sql patch

begin

dbms_sqldiag.drop_sql_patch (name=> 'sql_patch_&sql_id')；

end;

/

--禁用sql patch

begin
dbms_sqldiag.alter_sql_patch(name=>'sql_patch_&sql_id', attribute_name=>'status', value=>'disabled');
end;
/

对于动态sql，可以采用如下方式:

--创建sql patch

DECLARE
     sql_fulltext clob;
     hints    varchar2(4000) :='&hints';
     descriptions varchar2(100):='sql patch for sql &sql_id';
     names varchar2(100)       :='sql_patch_&sql_id';
     output   varchar2(100);
     sqlpro_attr SYS.SQLPROF_ATTR;
  
BEGIN
     sqlpro_attr := SYS.SQLPROF_ATTR(hints);
     select sql_fulltext into sql_fulltext from gv$sql where sql_id='&sql_id' and rownum=1;
     output      := SYS.DBMS_SQLTUNE_INTERNAL.I_CREATE_SQL_PROFILE(
       SQL_TEXT => sql_fulltext,
       PROFILE_XML => DBMS_SMB_INTERNAL.VARR_TO_HINTS_XML(sqlpro_attr),
       NAME => names,      
       DESCRIPTION => descriptions,    
       CATEGORY => 'DEFAULT',
       CREATOR => 'SYS',
       VALIDATE => TRUE,
       TYPE => 'PATCH',
       FORCE_MATCH => TRUE,
       IS_PATCH => TRUE);
     dbms_output.put_line(output);
   END;
/

一些例子：

1、监控指定sql 性能，添加MONITOR hint

declare
c clob;
hint_text varchar2(4000);

begin
select sql_fulltext into c from gv$sql where sql_id='&sql_id' and rownum=1;
hint_text:='MONITOR’ ;
  sys.dbms_sqldiag_internal.i_create_patch
   (sql_text  => c,
    hint_text => hint_text,
    name      => 'sql_patch_&sql_id');
end;
/

2、在acs 特性关闭的情况下，解决由于列数据分布倾斜绑定变量窥视导致的sql 执行性能问题，添加BIND_AWARE hint

declare
c clob;
hint_text varchar2(4000);

begin
select sql_fulltext into c from gv$sql where sql_id='&sql_id' and rownum=1;
hint_text:='BIND_AWARE’ ;
sys.dbms_sqldiag_internal.i_create_patch
  (sql_text  => c,
   hint_text => hint_text,
   name      => 'sql_patch_&sql_id');
end;
/

3、在基数估计不准确的情况下，解决由于MERGE JOIN CARTESIAN 导致的性能问题，添加OPT_PARAM('_optimizer_mjc_enabled','false') hint

declare
c clob;
hint_text varchar2(4000);

begin
select sql_fulltext into c from gv$sql where sql_id='&sql_id' and rownum=1;
hint_text:='OPT_PARAM(''_optimizer_mjc_enabled'',''false'')' ;
sys.dbms_sqldiag_internal.i_create_patch
  (sql_text  => c,
   hint_text => hint_text,
   name      => 'sql_patch_&sql_id');
end;
/

4、sql 性能故障诊断时，查看sql 运行时的统计信息，添加 GATHER_PLAN_STATISTICS hint

declare
c clob;
hint_text varchar2(4000);

begin
select sql_fulltext into c from gv$sql where sql_id='&sql_id' and rownum=1;
hint_text:='GATHER_PLAN_STATISTICS’;
sys.dbms_sqldiag_internal.i_create_patch
  (sql_text  => c,
   hint_text => hint_text,
   name      => 'sql_patch_&sql_id');
end;
/

5、sql 执行计划走指定的索引，添加 INDEX(TABLE_NAME INDEX_NAME） hint

declare
c clob;
hint_text varchar2(4000);

begin
select sql_fulltext into c from gv$sql where sql_id='&sql_id' and rownum=1;
hint_text:='INDEX(TABLE_NAME INDEX_NAME)’;
sys.dbms_sqldiag_internal.i_create_patch
  (sql_text  => c,
   hint_text => hint_text,
   name      => 'sql_patch_&sql_id');
end;
/

   sql patch 查看：

   select t.name,t.sql_text,t.status,t.description,t.force_matching from dba_sql_patches t