UKOUG Tech13: Optimizer & SQL statements – hint or not hint that’s the question

It’s the final day of Tech13, less sessions but still good topics. Mainly about the Oracle’s optimizer: looking at the execution plans and a debate about hinting SQL statements. I will focus on the hint topic in this blog.

Due to trip planning it’s a much shorter day, less time for attending sessions and less time for blogging. I will focus on the debate between Tony Hasler and Jonathan Lewis about how the optimizer takes care about the hint we can give.

Does the optimizer ignore hints?

In many cases, we provide hints to the optimizer and it seems its have no effect on the execution plan.
Through several examples, the two speakers showed that the optimizer don’t always react as we can expect and explained root causes.
Except a few bugs, the optimizer will follow the hints but for several reasons it will not if:

• something is wrong or hint is misused
• operation will be illegal
• operation is not relevant

As the documentation is not always as good as we want/think, they are some restrictions we can be not aware of when using a particular hint and it can makes us think the optimizer didn’t obey.
The main interest of that debate is also to open a discussion about hint usage and the existing traps.

What are the main drawback of using hints?

The big mistake is to use an incomplete set of hints and get lucky enough to have the expected execution plan.
So in order to use hints, we have to be very precise.

In case of join between several tables, to influence enough the optimizer the following parameter should be set:

• the join order
• the join method
• the table access path

It will represent at least two hints per table so it’s very easing to go too fast and to get the right result.

I have to add that using hint will not fix the execution plan. It’s not because at a specific time the hint did what we expected that it will be always the case. The data will change and/or maybe the physical structure will change and there is no guaranty that the hint will still be relevant or applicable.

Should we don’t use hint at all?

In many cases in I.T, we shall not be so affirmative. We have to keep every door unlocked to avoid loosing options when we need to solve a problem.

In general, hints should be avoided. If another solution is found, it will be in most cases a better solution.
But the last sentence started with in general, so there are cases, we will need hints to make the optimizer aware of some options it will not consider or to correct a behavior because it cannot get the right estimations.

If hints are used, it should be done carefully and should be use with SQL Plan Management if the final goal is to get a fixed plan.

Now Tech13 is over for me, I spent a very good time in Manchester. I’ve seen some many things, some were known other were new. Many interesting questions or debate, so I’m very happy to be part of such event as a delegate but also as a speaker. See you there maybe next year !