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‘));

Continue reading