Using Oracle Cost-based Optimizer Hints: Part 2

By: Dan Hotka

This article series will focus using Oracle cost-based optimizer (CBO) hints, and this second article on producing a series of hints that will replicate an exact explain plan.

In Part 1, we discussed the OUTLINE produced by DBMS_XPLAN.Display(FORMAT=>’OUTLINE’),

Hokta Hints for Explain Plans 1


I find the IGNORE_OPTIM_EMBEDDED_HINTS to be an interesting hint to appear here.  This hint says to ignore all hints.  Maybe it really means ignore any additional hints in the SQL after this hint.  I think the END_OUTLINE_DATA is just a place holder of these outline generated hints and really is there for documentation purposes.

You can copy/paste these into your SQL and the exact explain plan will be produced.

I find this technique useful for those occasional problem SQLs that won’t stay tuned.  I also find this technique useful if you ever see any SQL with the RULE hint.  I see this on occasion, and its mostly Oracle’s dictionary SQL.  Now, I haven’t looked in Oracle12 yet but Oracle11r2…the RULE hint was still visible in 10046 SQL Trace files.

The RULE hint tells the CBO to use the old optimizer that generated explain plans based on a set of rules that was mostly centered around the existence or lack of available indexes and tended to drive off the last table in the from clause.  Both optimizers read backwards thru the SQL text and the last table is the first table the RBO processes.  This is why the rule of thumb back in the day was to put the largest table last.  Anyway, the RBO isn’t documented anymore but its still there.  IF you see the RULE hint, do your app a favor, run the outline as described in these articles and put those outline hints directly into the SQL, replacing the RULE hint.  You really want all application SQL running on the CBO, even if its heavily hinted.

There is another technique to add init.ora settings to particular SQL.  The OPT_PARAM hint has these parameters that can be set just like init.ora settings:

  • Optimizer_Dynamic_Sampling
  • Optimizer_Index_Caching
  • Optimizer_Index_Cost_Adj
  • Optimizer_use_Pending_Statistics
  • Star_Transformation_Enabled
  • Parallel_degree_Policy
  • Parallel_Degree_Limit
  • Optimizer_Features_Enable(<oracle version>)

I would prefer to see SQL tuned this way, for the few SQL that actually need a parameter change, than to change the parameter and affect other things that are already working fine in the database environment.

Database replay is a cool tool that showed up in Oracle 11g and allows for a unit of work to be captured.  It plays back much like the old magnetic tape recorders (I know, I’m really dating myself).

I find this replay an excellent way to test these SQL and application changes/init.ora changes in general.  Did it improve things?  Did the change make things worse?  This testing tecnique will quickly give you this answer.

I learned about database replay in Robert Freeman’s book Oracle 11g Database New Features.  I always get the new features book from Oracle press, Robert does a great job and this book jump starts me into the new database.

The next article in this series will continue this discussion.

Leave a Reply