Oracle Scratchpad

February 21, 2022

“Ignoring Hints” Catalogue

Filed under: Oracle — Jonathan Lewis @ 9:44 am GMT Feb 21,2022

This is a list of all articles I’ve written to examine claims that Oracle has “ignored a hint”, including articles where other aspects of hinting (such as SQL Plan Baslines) don’t work as expected.

Each entry has a date stamp and a short note of the contents. Some of these will also appear in the generic “Hints” catalogue. The articles are generally listed most-recent first.

  • Use_nl() revisited (Oct 2021) – a detailed examination of how a simple hint has been used by the optimizer but still produce a plan that appears to have ignored it.
  • Index hints – pt. 1 (Jan 2021) – a voyage of discovery as index hints don’t do exactly what you expect.
    • Index hints – pt. 2 (Jan 2021) – continuing the voyage of discovery with index skip scans and range scans.
  • Hinting errors (Jan 2021) – there are many reasons why your hints might be wrong; this is a list (probably incomplete) of possible reasons that I extracted from the Oracle executable.
  • Faking Baselines (Feb 2020) – an example of an SQL Plan Baseline that doesn’t reproduce the plan it’s supposed to.
  • Ignoring hints by choice (June 2019) – changes to the “_optimizer_ignore_hints” (previously hidden) parameter in 18g onwards. It’s official, and has a companion.
  • Avoiding APPEND (March 2019) – ideas for making Oracle ignore an APPEND hint in a 3rd party application.
  • Parallelism (May 2017) – the parallel(N) hint does not mean “make this query run parallel with degree N”.
  • What does use_nl(a,b) mean (Jan 2017) – a common misconception explained; this hint does not mean “do a nested loop from a to b”.
  • Hinting across upgrades (March 2016) – updates can introduce new transformations that make your previously working set of hints for a query incomplete: an example.
  • Ignore this hint – please (May 2014) – Oracle doesn’t “ignore” hints, but sometimes you might wish it did.

2 Comments »

  1. […] Ignoring hints (sub-)catalogue […]

    Pingback by Catalog Catalogue | Oracle Scratchpad — February 21, 2022 @ 9:45 am GMT Feb 21,2022 | Reply

  2. […] ignoring hints catalogue: a separate catalogue of articles about how Oracle doesn’t ignore hints, or examples of bugs, or explanations of the difficulty of hinting correctly. […]

    Pingback by Hinting Catalogue | Oracle Scratchpad — February 21, 2022 @ 9:49 am GMT Feb 21,2022 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by WordPress.com.

%d bloggers like this: