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=>'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.