OraFAQ Forum: Performance Tuning » Incorrect optimizer_index

Home » RDBMS Server » Performance Tuning » Incorrect optimizer_index_caching affect on plan optimization (Oracle 9.2, Unix platform)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Incorrect optimizer_index_caching affect on plan optimization [message #317414]

Thu, 01 May 2008 02:23

harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts

Senior Member

Good Evenings - This is a feedback post for an issue
resolution on CBO plan optimization which I started
in discussion with Ross and JR last evening in the SQL Forum:
http://www.orafaq.com/forum/m/317224/0/#msg_317224

It was aptly suggested to continue here, as the subject
evolved from sql-tuning to performance tuning via initialization parameters.

The problem summary is:
Oracle was continually choosing nested-loop index access methods for every join query I would write for which an index made it possible.

Subsequently the CBO was evaluating the cost to be much lower than other approaches such as hash or merge.
And always in actual execution time the nested loop failed by no small margin. Statistics gathering was not lacking and the nature of the joins were always "big tables, large expected hits between them".

I was given great advice for focus areas to research and a good optimization site reference link to start
my path of knowledge on some relevent ora.init parameters.

I'll cut to the chase although i have much more to write on the insanity of the research path which evolved to lead me to the fix. In brief - and it was a frustrating day, 2nd round of surprise layoffs at my firm which was recently aquired. Sadly as well it was our entire dba group which was let go.

The insanity is what I describe as consistent relevence debates on just about everything Oracle performance related that pretty much sums to A effects B and B affects A but nothing conclusive can be stated about A or B when there is a change in either.

Oracle chose nested loop access paths repeatedly because the index caching percentage for the optimizer index cache parm was 20% but table indices were built with 'no caching'.

In my less technical understanding:
The optimizer_index_caching setting tells the optimizer how much
of an index it should expect to be cached. Oracle's expectation
(Russ again I hear your golden words of wisdom - "cost" is
based on "goal") caused it to believe that the nested_loop path was always the way to go.

I altered my oracle session to set it to zero for index caching expectation for it to equal reality and when i re generated the plan for every one of my problematic queries, a hash join methodology was correctly chosen without need for an optimizer hint.

I had sifted through volume after volume of technical discussions on the relationship between the optimizer_index_cost_adj parameter which affects the weighting of index cost in regards to the the expected caching - the importance of the dependency coupled with the void of conclusion as to determinants to use to evaluate the relationship for any concrete performance scenario.

This untennable position seemed to me once again to enforce what up to now I had believed , in Oracle research, to be the endless paradox of "conclude to trust in the importance of every meaning but never trust each meaning is important to conclude anything".

Here is the analysis I followed based on Russ and JR's original suggestions for research of conditions that could play a significant hand in the problem:

-oracle optimizer mode was set to "choose" and not "first-rows-n or rule based"
-our index cost adjustment parm was set to the default 100% for 9.2
-i reviewed a saved ora.init filed for a db region that has passed on but was wise beyond its time in its decisions and recognized that we had the optimizer_index_caching to zero.
Duly noted - that was the default parm while in my trouble region our 20% setting was flagged as to having been modified.

-I turned to trying to learn what analysis i need to perform to
determine what is really being cached to this expectation cache parameter. I found our 'N' setting for cache when I looked at the parameters for one of our table indices.

-I found in the Ora.init that the optimizer index cache setting could be altered at the session level.

- Gave it a shot and it worked.

My new insight to my existential paradox on settings, paths, plan cost, and the like is that there IS ONE FUNDAMENTAL
criteria that can be trusted to always be an absolute meaning for conclusion on performance tuning

- The real execution time of a query. There can be nothing illusory about that. Its something I can rely on to determine the best access path, the correct index choice, and whether or not to tinker with this or that. Use outcome to determine if
(again to quote Russ) my goal matches oracle's.

Thanks to all moderators who contributed to my posts from whence the original discussions began!

Best Regards,
Harry