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 ( 2 )
  • 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.
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