We help IT Professionals succeed at work.

are optimizer hints wise in production-code?

Dave Ford
Dave Ford asked
As I come up to speed on Oracle (having been a DB2-guy for the last couple decades), I see a A LOT of existing code that uses optimizer-hints in its queries.

From what I've read on various Oracle-focused web-sites, several Oracle "experts" advise AGAINST putting optimizer-hints in prodcution code because:

- With every Oracle patch or upgrade, the hint will probably be wrong.
- With every DDL, the hint will probably be wrong.

One "expert" says:

The reason to be wary of hinting is that by embedding hints in your SQL, you are overriding the optimizer and saying that you know more than it does – not just now, but every time in the future that your SQL will be run, irrespective of any other changes that may happen to your database. The likely consequence of this is that your SQL will possibly run sub-optimally now and almost certainly in the future.

(see http://allthingsoracle.com/a-beginners-guide-to-optimizer-hints/)

So, if optimizer-hints are commonly known to be "uswise", why are they so frequently used? ( ... at least in the code I've seen )

Thanks for your advice!
Watch Question

Most Valuable Expert 2012
Distinguished Expert 2019
I'm one that is against hints in production.

Use them in testing to see what may be achieved IF the optimizer chooses the 'wrong' plan.

>>( ... at least in the code I've seen )

How old is the code you are seeing?

Not all that long ago Oracle's Cost-Based Optimizer was pretty horrible when it came to choosing an efficient execution plan and hints were pretty common.

It has gotten MUCH better in the recent past.

If you are seeing hints in newer code, then in my opinion, the folks may not know what they are doing?

I know you'll probably find someone 'famous' that is using them and make me eat those words...
Most Valuable Expert 2011
Top Expert 2012
The reason I discourage most hints is because they don't really work in isolation.  That's why hint's can sometimes be ignored.  It's not because the optimizer overrode your hint, it's because your hint was invalid along with other implicit hints or incompatibilities.

for instance,  look at a very simple query:

select * from dual;

on an db,  the plan generated the following set of hints as part of its execution outline.

      FULL(@"SEL$1" "DUAL"@"SEL$1")

As your query gets more complex, the number of hints embedded in the outline grow significantly.
When you add a single hint to use an index or nested loops instead of a hash join, your hint has to be compatible with all of the other hints or you'll get unexpected results.

Unless you understand what the rest of the outline is doing, simply injecting a hint into the middle of it isn't really controlled tuning.  It's just fiddling at random.
Most Valuable Expert 2011
Top Expert 2012

Having said that, there are hints that are safe

One I particularly like is the CARDINALITY hint (which, unfortunately is undocumented so I still don't recommend it.
The optimizer must make estimates of how big a table will be.  Using the CARDINALITY hint, you can tell it explicitly.

The DRIVING_SITE hint can also be useful.    Your local optimizer will make its best guess as to what's on the other side of a distributed query but it's never going to know for sure.  This is a good place to add some human knowledge and force  execution where you want it.

Other hints, like index vs full, joins methods, table ordering, etc  are less reliable because now you're making assumptions about the way the optimizer may or may not process information and you're locking in a logic on a system that will likely change.

So, in general, I try to avoid hints that involve me "competing" with the optimizer; but I will, on occasion, use them where I can give the optimizer some information that it simply can't know on its own.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
I have seen a few COTS applications using hints in the past, and then them being forced to change as the rdbms changes. e.g. from rule based to cost based optimisers, but even if not so dramatic as that change new features get introduced and/or the optimiser gets revised over time. So from my COTS vendor based perspective it is better to avoid hints. They have a habit of creating bugs in the future.

So, I agree with the quote used in the question. (& with slightwv & sdstuber*)

There can be rare exceptions made, but there has to be a damn good reason

* although I  didn't know about the cardinality hint
Geert GOracle dba
Top Expert 2009
>> It has gotten MUCH better in the recent past.
Has it really ? SPM can cripple a server if not caged decently ...
and why do we need SPM if the optimizer has gotten much better.
sorry about that comment, but in my opinion the optimizer isn't much better in 11.2
it's actually more unstable

HINTS are a workaround for the unstable optimizer.
SPM (SQL Plan Management) can also be used to "help" the optimizer
i'm all for oracle, but they haven't lived up to their old flagship standards for the database

the most common case i see is vendors demanding a RULE based optimizer for the whole database
and all the developers adding the CHOOSE hint to the querries in other schemas
I refuse this and provide a login trigger for their systems with set optimizer_mode = rule for their sessions

the optimizer (and performance tuning in general) requires constant attention

in 11 having the default behavior of letting the database generate histograms during analysis is also a bad default
johnsoneSenior Oracle DBA
Having worked mostly with very large OLTP systems.  Sometimes hints are necessary.  Sorry to say it.  The optimizer gets better, but is never perfect.  Even the rules based optimizer makes bad choices.

Would you rather deal with a hint, or with something in the where clause that prevents the use of an index?  I see both.  Concatenating nulls to strings or adding 0 to number fields so that certain indexes are not used in certain queries because it is the wrong index.  If you need to help the optimizer, I would rather see a hint.  Why?  Because you can easily search through query files and code for hints.  You cannot do that for crazy things in where clauses.

And I second the histograms are problems.  If you have tables with large numbers of inserts and every increasing fields that are used in indexes (think sequences and dates), histograms get out of date very fast and can cause drastic changes in query plans without any other changes.  Histograms almost require up to the second statistics.

I will say that if you have hints in the code, that you need to evaluate them with every new release.  The optimizer does change, so the hint may no longer be needed, or a different one may be better.

Tuning is not a one time exercise.  It is a constant project that never ends.
Dave FordSoftware Developer / Database Administrator


To summarize the comments:

> slightwv:  I'm one that is against hints in production.
> sdstuber: The reason I discourage most hints is because they don't really work in isolation ... Having said that, there are hints that are safe
> PortletPaul: it is better to avoid hints
> Geert Gruwez: HINTS are a workaround for the unstable optimizer.
> johnsone: Sometimes hints are necessary

I realize you said much more than that summary, but I tried to capture the "essence" of what each of you said. I apologize if I missed your main point.

In my case, most of the hints I see in our production code are like these:


/*+ full(MP) parallel(MP, 16) */


Rarely ( but every so often ), I'll see an index-hint:

/*+ index(MSL XCL01000) */

Thanks for your valuable input. I certainly understand a bit more the dangers and necessities of hints.

-- DaveSlash
Most Valuable Expert 2012
Distinguished Expert 2019


This is a special hint that does 'special' things.  I would read up on it.

Same for the parallel hints.
Dave FordSoftware Developer / Database Administrator


Thanks, slightwv. I'll do that.

Oh, and by the way, in your comment, you asked "How old is the code you are seeing?"

It's relatively new (a few years old), but the developers who wrote it are ... how shall I say this ... "seasoned veterans". :-)

If memory serves, you and I both fit that description, too.