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:

• ALL_ROWS
• FIRST_ROWS_N

Those related to direct path operations:

• APPEND
• APPEND_VALUES
• NOAPPEND

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:

• OPT_PARAM (documentation is inaccurate when it says that it’s only valid for 5 parameters)
• OPTIMIZER_FEATURES_ENABLE (undocumented)
• NO_QUERY_TRANSFORMATION

Those related to bind variable/literal usage:

• CURSOR_SHARING_EXACT (never had to use it myself but I can see how it might be useful)

Those related to parallel operations:

• PARALLEL / SHARED
• PARALLEL_INDEX
• NO_PARALLEL / NOPARALLEL
• NO_PARALLEL_INDEX / NOPARALLEL_INDEX

Those related to remote operations:

• DRIVING_SITE

Those related to real time sql monitoring:

• MONITOR

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

• GATHER_PLAN_STATISTICS (indirectly documented)

Those related to caching and caching-like behaviours:

• MATERIALIZE (undocumented)
• RESULT_CACHE

Those related to query block naming:

• QB_NAME

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.