As I promised in the end of the last hacking session (about Oracle SQL Monitoring, I will run another one on 2. June 2020. This one will be a deep dive into Oracle hint usage and various scenarios of hint (in)validity. I have too much material in my full-week Advanced Oracle SQL Tuning training, so I’m moving some “narrow deep dives” out and make available for free, so that we could spend more time actually tuning SQL during the class.
7 Levels of Hint Invalidity in Oracle SQL
Update: I have uploaded the Oracle SQL Hinting hacking session videos to YouTube.
Abstract:
The most common Oracle SQL hint-related question I get is “why is Oracle ignoring my hint”. It may seem like hints are some sort of soft recommendations that the optimizer has the freedom to use or not. This is not correct, Oracle SQL hints are directives, exact orders that the optimizer must obey.
But what about all the cases where my index hint didn’t actually force an index use then? The answer is that the hint was invalid. Oracle’s optimizer does obey hints, but only if they are valid in the context of the current plan shape evaluated!
In this hacking session, I will explain and demo (at least) seven layers of hint invalidity. As the simplest example, if the syntax of the used hint is incorrect, obviously it won’t be obeyed. But there are more complex and subtle cases of hint invalidity and I will demo & troubleshoot them in this hacking session.
You’ll learn some CBO fundamentals, how to correctly hint in the (hopefully rare) cases where hints are the best solution, read hint usage hints (sic!) from an optimizer trace file and also the Oracle 19c new feature - hint usage report section in execution plans!
Date & Time:
- Tuesday 2 June 2020
- 12pm-2:00pm ET
Registration:
Preparation:
There’s no preparation needed for this hacking session, but if you want to learn more about the internals of Oracle SQL Plan execution, you can view my old SQL execution hacking sessions:
Have fun and “see” you soon!
