X

Insights into Statistics, Query Optimization and the Oracle Optimizer

  • Monday, June 12, 2017

Adding and Disabling Hints Using SQL Patch

By: Nigel Bayliss | Product Manager

If you’re a DBA, it’s likely that you’ve encountered systems where a lot of SQL statements have been hinted almost as a matter of policy. Perhaps you’d like to figure out if these hints are actually helping. You might like to demonstrate to a development team that they should probably dial down their enthusiasm for micro-managing the Oracle Optimizer. Sometimes, you might want to apply a hints on-the-fly.

A while ago, Maria Colgan wrote a couple of posts (here and here) on SQL Patch and how you can add hints to SQL in a packaged application. In other words you can apply hints to SQL statements without having to change any application code. From Oracle Database 12c Release 2, the interface to SQL Patch is greatly improved and easier to use. In particular, it’s now part of the public API and the hint text is a CLOB (because VARCHAR2 can be too limiting if you want to specify a complete query outline). The API includes a new SQL_ID parameter too. Check out the documentation for the details, but here’s an example of the new look:

patch_name := dbms_sqldiag.create_sql_patch(
     sql_id=>'amz7zfdk33czb',
     hint_text=>' FULL(@"SEL$1" "T1"@"SEL$1")');

For the rest of this post I’ll use the new DBMS_SQLDIAG API, but everything here will work in Oracle Database 11g and Oracle Database 12c Release 1 if you use the internal DBMS_SQLDIAG API documented in Maria’s post.

Take a look at the following example. The query really should use indexes I1 and I2 on T1.V and T2.V, but I’ve hinted it to use a FULL scan or T2.

select /* QUERY2 */ /*+ FULL(t2) */ sum(t1.id)
from   t1,t2
where  t1.id = t2.id
and    t1.v = 1000
and    t2.v = 1000;
----------------------------------------------
| Id  | Operation                     | Name |
----------------------------------------------
|   0 | SELECT STATEMENT              |      |
|   1 |  SORT AGGREGATE               |      |
|   2 |   NESTED LOOPS                |      |
|   3 |    NESTED LOOPS               |      |
|*  4 |     TABLE ACCESS FULL         | T2   |
|*  5 |     INDEX RANGE SCAN          | I1   |
|*  6 |    TABLE ACCESS BY INDEX ROWID| T1   |
----------------------------------------------

If you create a SQL patch with an additional hint for a FULL scan on T1 (using the create_sql_patch example above) the new plan will include the FULL hints for both T1 and T2. In other words, the new hint has been merged with the preexisting hint:

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'LAST OUTLINE BASIC NOTE'));

------------------------------------
| Id  | Operation           | Name |
------------------------------------
|   0 | SELECT STATEMENT    |      |
|   1 |  SORT AGGREGATE     |      |
|*  2 |   HASH JOIN         |      |
|*  3 |    TABLE ACCESS FULL| T1   |
|*  4 |    TABLE ACCESS FULL| T2   |
------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T1"@"SEL$1")
      FULL(@"SEL$1" "T2"@"SEL$1")
      LEADING(@"SEL$1" "T1"@"SEL$1" "T2"@"SEL$1")
      USE_HASH(@"SEL$1" "T2"@"SEL$1")
      END_OUTLINE_DATA
  *

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."ID"="T2"."ID")
   3 - filter("T1"."V"=1000)
   4 - filter("T2"."V"=1000)

Note
-----
   - SQL patch "PATCH1" used for this statement

Hints specified using SQL patch must include query block names (such as @"SEL$1") . If you’ve not done this before it can appear quite difficult, but if you use example queries and display the SQL execution plans using the OUTLINE format (see above), it becomes pretty easy to figure out what you need to do.

What if you want to disable hints for an individual SQL statement? Well, there’s a neat trick to do this. If you look closely, you’ll see it in the OUTLINE above. Let’s do it for our query:

patch_name := dbms_sqldiag.create_sql_patch(
     sql_id=>'amz7zfdk33czb',
     hint_text=>' IGNORE_OPTIM_EMBEDDED_HINTS');

Here’s the new plan – it’s the one we should have in the first place:

-------------------------------------------------------
| Id  | Operation                              | Name |
-------------------------------------------------------
|   0 | SELECT STATEMENT                       |      |
|   1 |  SORT AGGREGATE                        |      |
|   2 |   NESTED LOOPS                         |      |
|   3 |    NESTED LOOPS                        |      |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| T1   |
|   5 |      INDEX RANGE SCAN                  | I1   |
|   6 |     INDEX RANGE SCAN                   | I2   |
|   7 |    TABLE ACCESS BY INDEX ROWID         | T2   |
-------------------------------------------------------

You can even disable all existing hints and supply new ones. Here the FULL(t2) hint has been removed and I've added FULL(t1):

... hint_text=>'IGNORE_OPTIM_EMBEDDED_HINTS LEADING (t1 t2) FULL(@"SEL$1" "T1"@"SEL$1")');

----------------------------------------------
| Id  | Operation                     | Name |
----------------------------------------------
|   0 | SELECT STATEMENT              |      |
|   1 |  SORT AGGREGATE               |      |
|   2 |   NESTED LOOPS                |      |
|   3 |    NESTED LOOPS               |      |
|   4 |     TABLE ACCESS FULL         | T1   |
|   5 |     INDEX RANGE SCAN          | I2   |
|   6 |    TABLE ACCESS BY INDEX ROWID| T2   |
----------------------------------------------

In common with using hints in general, if you supply invalid hints via SQL patch then the effects can be confusing if you fail to notice your error – so take care. Patch #17203284 is currently available for Oracle Database 12.1.0.2 if you want to make the new public interface available in this release.

Join the discussion

Comments ( 4 )
  • V Puli Tuesday, August 1, 2017
    There a way to find out what hints to apply ?
    Is there an advisor, like SQL Tuning Advisor or a way to figure out which ones apply a patch for better performance ?
  • Nigel Bayliss Wednesday, August 9, 2017
    Hi - DBMS_SQLDIAG and SQL Patch are support tools if you want to get into the real details. If you want tuning automation, then take a Oracle's Automatic SQL Tuning Advisor. This feature figures out if there are better SQL execution plans and you can implement the improvements it discovers automatically - using SQL profiles.
  • Chuck Saturday, July 21, 2018
    It appears that hit eradication with hint_text=>' IGNORE_OPTIM_EMBEDDED_HINTS' does not apply to hints defined in actual views called from unhinted SELECT statements in PL/SQL packages. Should it?

    I get the note "- SQL patch "SYS_SQLPTCH_0164bde0ea750002" used for this statement" after applying the patch:

    DECLARE
    patch_name VARCHAR (32767);
    BEGIN
    patch_name :=
    DBMS_SQLDIAG.create_sql_patch (
    sql_id => '9kjgv5uzhc4bj',
    hint_text => ' IGNORE_OPTIM_EMBEDDED_HINTS');
    DBMS_OUTPUT.put_line ('patch_name = ' || patch_name);
    END;
    /

    The view definition has a couple of "SELECT /*+ MATERIALIZE */" and a "SELECT /*+ FULL (table1) */" hints that I want to disable, maybe just for a single user. The 12.1 tuning solution deployed after a 12.2 upgrade and the view rewrite without the hints seems to be the best solution.
  • Nigel Bayliss Friday, July 27, 2018
    Hi Chuck - thanks for your comment, and thanks for the excellent test case you sent me. This does seem to be specifically related to the MATERIALIZE hint. In your test case, we could successfully override the hint with INLINE (in SQL Patch) but the 'trick' in this blog post does not work in 12c. The materialize hint is processed differently to most other hints, so this might be the underlying cause. There have been one or two bugs related to MATERIALIZE in plan outlines (e.g. 25998859) and I can see that the behavior is different in Oracle Database 18c. In this database version I was able to reproduce the desired plan if I used the following hint in SQL Patch: hint_text=>'BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS END_OUTLINE_DATA' . The BEGIN and END were essential for it to work, so this is definitely something for me to investigate further.
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha
Oracle

Integrated Cloud Applications & Platform Services