7 Levels of Hint Invalidity in Oracle SQL

Sections:
0:00 Introduction Hint Placement
9:50 Supported Hint Names (V$SQL_HINT)
22:40 Hint Parameters Incorrect (INDEX vs INDEX_RS examples)
43:50 Hint Conflict
56:00 Incorrect Expectactions (Example: PARALLEL hint only adjusts costing)
1:13:20 Optimizer Fix Control example (Unexpected Parallel Plan in 18c)
1:23:50 Hint Syntax Error Impact on Subsequent Hints
1:39:20 An Example of Undesired Parallelism
1:42:30 Controlling Join Orders
2:01:55 Where To Put Hints
2:17:40 Troubleshooting Why Hints Aren't Used
2:25:40 Join Elimination (failed) demo
2:37:00 Full Hinting, Outlines and SQL Plan Baselines
2:45:50 Disabling Hardcoded Legacy Hints in Application Code
2:52:28 Summary, Next Events and Q&A

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!