Oracle Scratchpad

May 9, 2009

Hints on Hints

Filed under: Execution plans,Hints,Tuning — Jonathan Lewis @ 5:58 pm BST May 9,2009

This is the title of a presentation I have given a few times in the last couple of years – most recently at Collaborate 09 in Orlando.

The IOUG has now posted on their website the presentation and a short article I wrote to go with it; but that’s for members only, so it seemed reasonable to publish the same thing on my own blog as well.

So here are links for a pdf file of the presentation, and the article in Word format.



  1. Hi Jonathan,

    I look forward to meeting you at OpenWorld this year.


    Comment by Ben Prusinski — May 10, 2009 @ 3:49 pm BST May 10,2009 | Reply

  2. Aah ! “The parallel hint tells Oracle to change the arithmetic – it doesn’t force Oracle to run parallel and it isn’t supposed to force Oracle to run parallel.”
    is *most* interesting.

    It wasn’t clear to me why sometimes I would see differences in behaviour when using FULL and PARALLEL together. I now have something to go on and test test test ! (And also validate if I hadn’t had syntax errors in my Hints !)

    Comment by Hemant K Chitale — May 11, 2009 @ 5:11 am BST May 11,2009 | Reply

    • Hemant,

      The “joy” of that particular hint is that you can put it into a piece of code and it produces a parallel tablescan for months – but as the data size grows bigger Oracle suddenly finds a serial indexed access path that costs less – and changes plans for “no apparent reason”.

      Comment by Jonathan Lewis — May 11, 2009 @ 6:17 pm BST May 11,2009 | Reply

  3. MetaLink Note#826893.1 contains a change of behaviour in handling invalid hints if such invalid hints include Keywords.
    Parsing of Hints has been (slightly) changed in 10g such that if an invalid hint includes a Keyword, any hint after that (even if valid) is ignored.

    Comment by Hemant K Chitale — May 20, 2009 @ 7:18 am BST May 20,2009 | Reply

  4. […] optimizer completely if you want it to do precisely what you want. Jonathan’s recent presentation Hints on Hints provides more information on the subject. If you tell your son not to touch the cookie jar, you […]

    Pingback by Does the Optimizer Need a Hint? « So Many Oracle Manuals, So Little Time — June 14, 2009 @ 6:19 pm BST Jun 14,2009 | Reply

  5. […] current best cost. It is the same issue as with PARALLEL hint explained by Jonathan Lewis in his Hints on Hints paper: single PARALLEL hint is not enough to make the plan go parallel. So how to make sure the […]

    Pingback by Enabling constraint in parallel « Timur Akhmadeev's blog — February 16, 2011 @ 7:05 pm GMT Feb 16,2011 | 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: Logo

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

Google photo

You are commenting using your Google 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