Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

January 23, 2008

_optimizer_ignore_hints; please optimizer ignore all hints inside my applications

Filed under: Oracle How To — H.Tonguç Yılmaz @ 6:34 am

After 10g Rule Based Optimizer(RBO) is not supported, DBMS_STATS must be one of your best friends now, you even collect statistics on Oracle’s dictionary and fixed tables. Hints, optimizer directives can be a better name as Jonathan Lewis advises, can be very dangerous sometimes. They may behave differently release to release, they do not produce error messages if something is wrong, but much more important they may stay in your code hard-coded forever. As a result you may never benefit optimizer new features.

The applications in time may be hinted because of some quick dirty performance fixing need of course, inside your views and packages you will easily find them when you query the dictionary. We said after 10g RBO is not supported but still it can be used as an option with the old RULE hint, and even Oracle uses this hint for its own dictionary access time to time. You can catch these calls inside 10046 event trace file. There are really interesting undocumented hints after 10g when you study the trace files.

So what if you want to get most out of the optimizer and you do not want to go over each line of your code to clean up the hard-coded hints, okey this one is like a dream come true;

alter session set "_optimizer_ignore_hints" = TRUE ;

I first learned and blogged this at Jonathan Lewis’s seminar. Normally under score(they are also called hidden, un-supported, un-documented) might be used only with Oracle supports guidance, but there are several exceptions, like _trace_files_public which may cause a security problem as a side affect, you may still use them without experiencing any problems.

It is always better to test these kind of a parameter first at session level and at your test database of course, monitor the results and then if you are ~100% sure about their side affects then take the risk for their advantages. I used some of them before several times even Oracle support didn’t advised me to after my testing results were so dramatic.

So here is a small demo for this one, I hope it also helps you; Code Listing 186 – _optimizer_ignore_hints; please optimizer ignore all hints inside my applications

Testing Information : the scripts mentioned are tested on Oracle Database 11g Enterprise Edition Release 11.1.0.6.0

3 Comments »

  1. Hello,

    We found this really useful when we upgraded 10.1 -> 10.2. http://blog.nominet.org.uk/tech/2006/06/19/oracle-odbc-performance-problems-in-oracle-102/

    It turned out one of our apps using ODBC was appending a RULE hint to some queries which ran about x100 slower in 10.2 than in 10.1, doing a logon trigger and this paramter helped the performance no end.

    jason.

    Comment by jarneil — January 23, 2008 @ 11:47 am | Reply

  2. Jarneil thank you for a real life experience example for _optimizer_ignore_hints.

    Comment by H.Tonguç Yılmaz — January 25, 2008 @ 10:11 pm | Reply

  3. What should I do with old hints in my workload? – http://optimizermagic.blogspot.com/2009/11/what-should-i-do-with-old-hints-in-my.html

    Comment by H.Tonguç Yılmaz — November 21, 2009 @ 4:16 am | Reply


RSS feed for comments on this post. TrackBack URI

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

Blog at WordPress.com.

%d bloggers like this: