Using SQLPatch to inject HINTs when you can’t touch the code

Here I’m putting some example code on how to use SQLPatch for HINTing SQLs when it’s not possible to touch the application code or use other techniques to fix execution plan.

SQL Patches:

  • work since 11.2.0.3
  • are internally based on SQL Profiles;
  • may be used to add HINTs to SQLs when you can’t touch code(like with Packaged Application);
  • don’t require Diagnostics or Tuning Pack Licenses;
  • sql text is normalized before storing, so additional spaces and case mismatch is not a problem;
  • don’t handle SQL embedded literals, except through unpublished call to I_CREATE_SQL_PROFILE – see example later;
  • RESULT_CACHE hint is honored since 12.1.0.2 and 12.2 – Check BUG:16974854;
  • to check if SQL Profile was used add +NOTE when displaying execution plan
    • SELECT * FROM table(dbms_xplan.display_cursor(format=>’+NOTE‘));
  • to use HINTs with corrent syntax get them from desired execution plan:
    • SELECT * FROM table(dbms_xplan.display_cursor(format=>’+OUTLINE‘));

Creating SQLPatch

DECLARE
  l_sql_text        CLOB         := 'select /*+   no_index(emp pk_emp) */ * from emp where empno=7839';
  l_sql_id          VARCHAR2(30) :='???';
  l_sql_patch_hints VARCHAR2(500):='OPTIMIZER_FEATURES_ENABLE(''11.2.0.1'')';
  l_sql_patch_name  VARCHAR2(30) :='my_sql_patch';
  l_sql_patch_desc  VARCHAR2(500):=l_sql_patch_name || 'my_sql_patch_description';
BEGIN
  IF (l_sql_text IS NULL) THEN
    SELECT sql_fulltext INTO l_sql_text FROM v$sqlarea WHERE sql_id = l_sql_id AND ROWNUM < 2;
END IF;
SYS.DBMS_SQLDIAG_INTERNAL.i_create_patch( sql_text    => l_sql_text
    , hint_text   => l_sql_patch_hints
    , name        => l_sql_patch_name
    , description => l_sql_patch_desc
  );
END;
/

Creating SQLPatch with FORCE_MATCH=TRUE to cope with literals

SET SERVEROUTPUT ON
DECLARE
  l_sql_text        CLOB         :='select /*+ NO_index(emp pk_emp) */ * from EMP where empno=7839';
  l_sql_id          VARCHAR2(30) :='7a1dd4z42gw7t';
  l_sql_patch_hints VARCHAR2(500):=q'[INDEX(@"SEL$1" "EMP"@"SEL$1") OPT_PARAM('optimizer_dynamic_sampling' 0)]';
  l_sqlpro_attr SYS.SQLPROF_ATTR :=SYS.SQLPROF_ATTR(l_sql_patch_hints);
  l_sql_patch_name  VARCHAR2(30) :='my_sql_patch';
  l_sql_patch_desc  VARCHAR2(500):=l_sql_patch_name || 'my_sql_patch_description';
  l_output   varchar2(100);
BEGIN
  IF (l_sql_text IS NULL) THEN
    SELECT sql_fulltext INTO l_sql_text FROM v$sqlarea WHERE sql_id = l_sql_id AND ROWNUM < 2;
END IF;
l_output := SYS.DBMS_SQLTUNE_INTERNAL.I_CREATE_SQL_PROFILE(
SQL_TEXT    => l_sql_text,
  PROFILE_XML => DBMS_SMB_INTERNAL.VARR_TO_HINTS_XML(l_sqlpro_attr),
  NAME        => l_sql_patch_name,
  DESCRIPTION => l_sql_patch_desc,
  CATEGORY    => 'DEFAULT',
  CREATOR     => 'SYS',
  VALIDATE    => TRUE,
  TYPE        => 'PATCH',
  FORCE_MATCH => TRUE,
  IS_PATCH    => TRUE);
  DBMS_OUTPUT.PUT_LINE(l_output);
END;
/

Dropping SQLPatch

exec DBMS_SQLDIAG.DROP_SQL_PATCH(name=> 'my_sql_patch')

Disabling SQLPatch

exec DBMS_SQLDIAG.ALTER_SQL_PATCH(name=>'my_sql_patch', attribute_name=>'STATUS', value=>'DISABLED');

References:

  • 1931944.1 How to Create a SQL Patch to add Hints to Application SQL Statements
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s