Using Oracle Cost-based Optimizer Hints: Part 4

By: Dan Hotka


This series of articles has focused on Cost-Based Optimizer (CBO) hints or directives to the cost-based optimizer (CBO) on manual directions for the explain plan (be sure to read part 1part 2 and part 3).

In this final article, let’s take a look at the hints that I do use.

QB_NAME Hint

I use the QB_NAME hint quite a bit when working with any SQL that has sub queries.  You can also use this hint in the main line to replace the SQL$1 query block label to something self-documenting and meaningful like Main perhaps.

LEADING and ORDERED Hints

When SQL tuning in the good old days with the old Rule-Based Optimizer, we used to shuffle the tables on the FROM clause to find the best performing SQL among the tables being accessed.  I still recommend this trick, but you have to use the LEADING or the ORDERED hint.  I prefer the LEADING hint; it was introduced in Oracle 10 and it allows you to name the tables in the hint itself (be sure to use table alias labels if they appear in your SQL).  The ORDERED hint says to take the tables in the order of the FROM clause, a directive that allows us to play the shuffle game.  I like the LEADING hint better because I can play the same shuffle game but just by manipulating the text of the hint, not the text of the SQL.

You can find the best running SQL by using this technique and then asking the question: “Is this good enough?”.  If so, your tuning task was quick and simple.

If not, start with this output because the CBO liked it the best. Then start looking for the mismatch on join types and cardinality (estimated rows vs actual rows).  You really do need to see the actual rows when working with problem SQL because the standard explain plan just shows the estimated rows.

USE_NL Hint

I have had good luck changing SORT-MERGE join operations to Nested Loop join operations using the USE_NL hint.  You can specify two or more tables in this hint, again, using table aliases if they are defined in your SQL code.  I have actually used this technique to reduce a materialized view build from 4 hours down to 1.5 hours!

Another place hints are useful is when you know you are past the end of useful statistics, particularly date data.  The CBO guesses at row counts when there is a histogram and the statistics are off the end of the histogram.  If your cardinality (explain plan row count) is 1% or 5% of the total rows in the table object, the CBO is simply guessing.  It has no idea how many rows are there.

Again, Oracle 11 fixes this for each individual SQL with cardinality feedback, but this doesn’t help the first execution.  What if the WHERE clauses keep changing, for example?  Oracle 12 SQL directives should help but time will tell if it will fix these kinds of stats issues.

DYNAMIC SAMPLE Hint

The DYNAMIC SAMPLE hint is useful when stats are old.  This hint tells the CBO to go and sample some of the data then produce an explain plan.  I’m not a huge fan of dynamic sampling because it causes longer parse times.  But if a dynamic sample, even if it takes several additional seconds to hard parse can produce an explain plan that executes and saves minutes or hours of time.seems like a good trade to me.

Leave a Reply