are optimizer hints wise in production-code?

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.


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!
LVL 18
Dave FordSoftware Developer / Database AdministratorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
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...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
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.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

PortletPaulEE Topic AdvisorCommented:
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 dbaCommented:
>> 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 DBACommented:
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 AdministratorAuthor Commented:
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
slightwv (䄆 Netminder) Commented:

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

Same for the parallel hints.
Dave FordSoftware Developer / Database AdministratorAuthor Commented:
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.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.