Why Doesn’t My Hint Work?

On March 30, 2013, in Oracle SQL Tuning, by Stelios Charalambides
A Honda GX160 5.5 HP. pressure washer in action.

A Honda GX160 5.5 HP. pressure washer in action. (Photo credit: Wikipedia)

It’s spring in New Hampshire and therefore time to start spring cleaning. So, I got out my pressure hose to clean the deck, nothing more than a 10 minute job.  But not when the hose doesn’t fit, the power cable doesn’t reach and the water supply is insufficient. In the end, my simple, 10 minute job turned into a 1 hour marathon.

Tuning some SQL can be like that.

Run the SQL and see an execution plan that doesn’t seem right? Then you may turn to a hint to try and get a quick fix. Using hints is usually a sign that something isn’t right, but they do get used as quick fixes (that sometimes stay until the next version of the product).

As a performance analyst, if I see a hint in a statement I think why does this developer think he knows better than the optimizer?

There are many types of case that require the use of a hint: bad statistics, an optimizer bug, maybe even in house rules to ensure the optimizer keeps to the assigned plan.  Usually, setting the hint is a quick job;  just confirm the plan and you’re away. But what happens when your quick fix doesn’t fix anything? Why doesn’t your hint work?

You need to go looking to see if the hint is being used and if not why not. Let’s look at an example

Here’s my SQL created from the example products that come with Oracle

set autotrace traceonly explain;
set lines 100
select
a.first_name
,a.last_name
,a.hire_date
from
employees a
,employees b
where
a.hire_date = b.hire_date
and a.department_id=30
/

The execution plan looks like this

Execution Plan

———————————————————-

Plan hash value: 2254211361

———————————————————————————————–

| Id | Operation                 | Name             | Rows| Bytes | Cost (%CPU)| Time

———————————————————————————————–

| 0 | SELECT STATEMENT           |                   | 7   | 238 | 6 (17)| 00:00:01 |

|*1 | HASH JOIN                  |                   | 7   | 238 | 6 (17)| 00:00:01 |

| 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES         | 6   | 156 | 2 (0) | 00:00:01 |

|*3 | INDEX RANGE SCAN           | EMP_DEPARTMENT_IX | 6   |     | 1 (0) | 00:00:01 |

| 4 | TABLE ACCESS FULL          | EMPLOYEES         | 107 | 856 | 3 (0) | 00:00:01 |

———————————————————————————————–

Predicate Information (identified by operation id):

—————————————————

1 – access(“A”.”HIRE_DATE”=”B”.”HIRE_DATE”)

3 – access(“A”.”DEPARTMENT_ID”=30)

The query uses a Hash Join, but now being much cleverer than the optimizer, I decide there should be a Nested Loop instead so I’m going to use the hint so that the first line in my SQL now changes to

SQL> select /*+ use_nl(a b) */

If I re-run my SQL I get, as expected, a new execution plan

Execution Plan

———————————————————-

Plan hash value: 3321434377

———————————————————————————————–

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

———————————————————————————————–

| 0 | SELECT STATEMENT | | 7 | 238 | 12 (0)| 00:00:01 |

| 1 | NESTED LOOPS | | 7 | 238 | 12 (0)| 00:00:01 |

| 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 6 | 156 | 2 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 6 | | 1 (0)| 00:00:01 |

|* 4 | TABLE ACCESS FULL | EMPLOYEES | 1 | 8 | 2 (0)| 00:00:01 |

———————————————————————————————–

Predicate Information (identified by operation id):

—————————————————

3 – access(“A”.”DEPARTMENT_ID”=30)

4 – filter(“A”.”HIRE_DATE”=”B”.”HIRE_DATE”)

 

No problems so far.  Everything is going just as planned. The cost is higher, but since I know better than the optimizer I’m ignoring that.

So what happens if I add another hint now?

/*+ use_merge (a b) */.

Everything reverts back to the hash join plan. This is because the use_merge hint and the use_nl hint are incompatible. Please note however, there is no error message.  The SQL just runs. At this point you may say well that’s obvious I wouldn’t do that,  but there are some subtle cases of conflict that are not at all obvious.  For example, what about use_NL(a) and Use_Merge (b), would they conflict? Try it for yourself.

Just like my pressure hose and its connections, checking step by step that each part worked (and finding some that didn’t) you need to check,  don’t assume.  In more complex plans it may not be obvious what happened. So how do you check the status of your hints? In all cases get the 10053 trace:

SQL> alter session set max_dump_file_size=unlimited

SQL> alter session set events ’10053 trace name context forever, level 1′;

Now look for the 10053 trace and have a look at a little section near the end.  Search for “Dumping Hints”

 

Dumping Hints

===========

atom_hint=(@=OCF2D080 err=4 resol=1 used=0 token=923 org=1 lvl=3 txt=USE_MERGE (“B”) )

atom_hint=(@=OCF2D368 err=4 resol=1 used=0 token=924 org=1 lvl=3 txt=USE_NL (“B”) )

atom_hint=(@=OCF2DIF4 err=4 resol=1 used=0 token=923 org=1 lvl=3 txt=USE_MERGE (“A”) )

atom_hint=(@=OCF2D538 err=4 resol=1 used=0 token=924 org=1 lvl=3 txt=USE_NL (“A”) )

************ WARNING: SOME HINTS HAVE ERRORS ***********

The SQL ran, but the 10053 shows clearly that the optimizer thought it had been asked to do something which was not possible, in this case use_merge and use_nl can’t both be used on the same aliases.

Sometimes you see these errors and you still don’t know what the errors mean, because the hints look fine to you. In these cases read the descriptions in the Performance Tuning Guide and think about what you are asking the optimizer to do.

Just like me and the hose connections. The fitment just couldn’t connect to the tap.  Sometimes what you ask for just isn’t possible.

 

3 Responses to “Why Doesn’t My Hint Work?”

  1. Pretty cool. I didn’t know that the 10053 trace would show you hints that were rejected.

  2. Rich says:

    Stelios,

    Can you explain what each of the below represent?

    @=OCF2D080 err=4 resol=1 used=0 token=923 org=1 lvl=3

    • Rich, unfortunately the meaning of these tokens is not documented. The only ones I’ve seen speculation on are err, used and txt. Those are the obvious ones also and I’m sure the speculation is based on limited ‘tests’ where authors try a few things to see what happens. Unfortunately this is not a valid way to test a computer program that we can’t see into (unless we have the source code of Oracle). About all we can say for sure is that if the hint appears in this section it has been recognized as a hint. err=0 seems to mean that the hint was not used, but this is not always the case. used=0 means the hint was not used, although there are cases where used=1 can still result in a hint which is not used. So in summary if used=0 it wasn’t used, if used=1 it probably was used but maybe not.

Leave a Reply



Buy My Book!

Archives

All entries, chronologically...

Visit our friends!

A few highly recommended friends...