_optimizer_ignore_hints; please optimizer ignore all hints inside my applications

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