Hints of Acceptability

There are hints and then there are hints.

In version 11.2.0.3 there are 273 hints listed in V$SQL_HINT.

That’s four more than 11.2.0.2 by the way – (NO_)FULL_OUTER_JOIN_TO_OUTER and (NO_)OUTER_JOIN_TO_ANTI are the new additions.

But V$SQL_HINT doesn’t seem to be an absolutely comprehensive listing.

I only noticed one interesting omission – there’s no entry for PARALLEL.

There are entries for NO_PARALLEL / NOPARALLEL but these list their INVERSE as SHARED not PARALLEL.

I’ve never used or even heard of the SHARED hint but it certainly seems to just be synonymous with PARALLEL. Of course, the documentation documents PARALLEL but makes no mention of SHARED which has been a valid alternative since 8.1.0.

So, going down the entries in V$SQL_HINT, below is my initial attempt at a list of “hints of acceptability”, even if one or two are undocumented.

As long as their usage is appropriate, I think these can be used pretty much without guilt or sense of defeat / failure.

Those related to optimizer mode:

Those related to direct path operations:

Those related to optimizer cardinality/selectivity estimate adjustments:

  • CARDINALITY (Undocumented)
  • DYNAMIC_SAMPLING (In recent years, this has been my most favorite hint)
  • DYNAMIC_SAMPLING_EST_CDN (Undocumented since 9i)
  • OPT_ESTIMATE (Undocumented but useful link)

Those normally related to bugs and associated parameter changes and fix control:

Those related to bind variable/literal usage:

Those related to parallel operations:

Those related to remote operations:

Those related to real time sql monitoring:

Those related to tuning but which should not make it into production code:

Those related to caching and caching-like behaviours:

Those related to query block naming:

Have I missed any obvious candidates?
Is there anything you would add?

Of these listed above, let’s just dwell very briefly on those related to optimizer estimate adjustments.

Relatively speaking, do you not find that most – most not all – issues regarding SQL performance are related to accuracy – or rather inaccuracy – of rowsource estimates?

If so, then recommended reading should be Wolfgang Breitling’s Tuning by Cardinality Feeback, the bases of which are:

  1. The observation that:

    IF AN ACCESS PLAN IS NOT OPTIMAL IT IS BECAUSE THE CARDINALITY ESTIMATE FOR ONE OR MORE OF THE ROW SOURCES IS GROSSLY INCORRECT.

  2. The conjecture that:

    THE CBO DOES AN EXCELLENT JOB OF FINDING THE BEST ACCESS PLAN FOR A GIVEN SQL PROVIDED IT IS ABLE TO ACCURATELY ESTIMATE THE CARDINALITIES OF THE ROW SOURCES IN THE PLAN.

If the scope of a problem is one or two SQL statements, then a solution with a scope limited to one or two SQL statements – i.e. a rewrite or a hint – is more appropriate than something with a wider scope such as changing tab/column stats and/or histograms.

And in this respect a solution forcing an estimate adjustment – whether by a hard number by CARDINALITY or OPT_ESTIMATE, an adjustment fudge factor also via OPT_ESTIMATE or having a peek at some of the data in question via DYNAMIC_SAMPLING (only good for single table predicates) is more often than not a better, more flexible, longer lasting solution than forcing a nested loop or a hash join or a particular index.

There are, of course, times when you have no option – there are reasons why all these hundreds of hints exist after all.

But I always think that if I can’t get what I think I roughly want – and what I normally want is just for the estimates to be broadly accurate – either by rewriting the SQL or by using one of these acceptable hints then it’s almost an admission failure.

Away from the list above, hinting a SQL statement is not something which should be undertaken lightly.

Do you ever see a lot of sql statements joining at least a handful of tables but with a single USE_NL hint here or an INDEX hint there in the belief that this offers some sort of stability for the woolly concept of “the correct plan”? I know I do.

Bottom line: if you can avoid hinting you absolutely should.

But if you really are going to hint, you should be doing it properly.

What does this mean?:

  1. Being prescriptive and unambiguous with your directives – i.e. a single use_nl hint is not sufficient for a sql statement that joins eight tables for example.
  2. Using the full specification of the hint including queryblock and table specification syntax.

For more on queryblock naming see Jonathan Lewis’s article on qb_name including the discussions in the comments.

For more information on what you have to do to properly hint, see this excellent article on by Jonathan Lewis.

If Jonathan’s “simple” illustration is not enough to seriously make you reconsider your addiction to hinting, then you have issues and you have to be prepared to swallow the full specification including both query block and proper table specifications.

Yes, it’s ugly.

Yes, it’s not easy (compared to how you’ve probably been doing it).

But, if you’re thinking the above, perhaps revisit your attitude to hinting.

About these ads

7 Responses to Hints of Acceptability

  1. Dom,

    I think the best example of the level of hinting needed used to come from setting a stored outline. Create the outline, and it would generally have at least a dozen hints for a fairly complex statement. One or two for each table in the join, and some overall directives too.

    And what did those dozen or so hints achieve? A reasonable stab at plan stability, but no guarantee. So if a dozen hints won’t guarantee your plan, how could anybody hope to guarantee it with a single hint?

    regs

    Neil

  2. Dom Brooks says:

    Sometimes it’s hard to avoid a NO_MERGE…

  3. Pingback: Sql tuning request « OraStory

  4. In some cases, view merging and predicate do not happen. So push_pred and merge seems the option. What are your thoughts on those

    • Dom Brooks says:

      Most of the ones listed in the post above, I’d have no second thoughts about using (OPT_ESTIMATE is an exception).

      NO_MERGE, PUSH_PRED, NO_UNNEST, USE_HASH, INDEX, USE_NL and others fall into the category of hints which I find that I have to use sometimes but really don’t want to.

      For those I strongly believe we should be looking first at ways to avoid them by rewriting/rephrasing the query or by using adjusting rowsource estimates using dynamic_sampling, etc.

      What do you think?

      • Totally agreed. This is the first and foremost point. Tree Diagram creation of the query itself helped in understanding the correct nature of query to me many times. In fact, you helped me in removing my Compulsive Hint Disorder :) syndrome that I got over the time.

        I feel while working with queries which are having more than 6-7 tables in the join condition, optimizer cannot consider all the join paths as in that case it increases parse time much, sometimes it misses “functionally” optimized path. In that scenario hints like “leading” are beneficial after consulting the functional expert.

        Also I observed that in datawarehouse based queries, since the data volume is so huge that even gathering stats at 10% do not give correct data picture to optimizer. In that case heavily used queries needs some hints to make them run correctly.

Leave a Reply

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 )

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 62 other followers

%d bloggers like this: