| 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
|
|
|
|
|
|
|
|
|
|
| Re: Incorrect optimizer_index_caching affect on plan optimization [message #317515 is a reply to message #317414] |
Thu, 01 May 2008 15:02  |
coleing
Messages: 213
Registered: February 2008
|
Senior Member |
|
|
I was having this exact same problem, and found that the clustering factor on the index was a bit rubbish (due to the way the index had gotten populated in a fragmented way). I did a rebuild on the index, and the path went from nested loops index to hash join as expected. (previously I was having to manually hint everything).
Just a thought.
|
|
|
|
| Re: Incorrect optimizer_index_caching affect on plan optimization [message #317552 is a reply to message #317515] |
Thu, 01 May 2008 22:06 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Hmmm @coleing. You may have mixed your terms; or perhaps its me mixing my terms.
My understanding is that CLUSTERING is related to the frequency of adjacent index entires residing in the same block in the table. In this way, a RANGE or FULL scan of the index will be more efficient as it re-reads fewer table blocks.
Rebuilding an index does not have any effect on clustering because the index is rebuilt in the same order and the table rows are not touched.
The only thing that rebuilding an index WILL achieve is removal of empty space that results from bulk deletions or updates of indexed values that are not subsequently replaced by new rows with values in the same range.
Ross Leishman
|
|
|
|
|
|
| Re: Incorrect optimizer_index_caching affect on plan optimization [message #318755 is a reply to message #317576] |
Wed, 07 May 2008 19:25 |
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
|
Senior Member |
|
|
Good evenings,
Thought it worth adding some final notes on OPTIMIZER_INDEX_CACHING and on the clustering factor (something prior to this thread I knew nothing much about but spent time reading up before just asking)
When my index cache was set to 20% more anomalies were in occurence than Oracle's goal of choosing nest loops access for any available index on a table join.
Again, having set it to zero resynched my goals with Oracle and the CBO correctly choose the most efficient plan in my problem queries (verified by the fact the my +use_hash and +use_merge hint queries always outperformed the nested loops).
Now in the beginning of my topic thread started in the sql
forum I noted that Oracle was creating different plans based
on the order of the tables specified in the join -
which as the experts pointed out, just 'cannot be'.
I believed before I was on 10g but verified this box is still 9.2, if of relevance.
As one can guess, this was with tables in which the leading table had an applicable index for nested looping.
I'll have to re-execute the plans on them with the cache setting back to 20 to see if there was a pattern; i cant say from memory if just cost changed or if different access methods were chosen.
However, noteworthy is that once I fixed the optimizer index cache setting that the ordering of the tables made absolutley no difference.
On index clustering factor, I found a good write up on index internals which sides with Ross and contradicts a lot of expert opinions - even an Oracle white paper! - that index rebuilding alone will improve cf (let alone change it at all) with the same reason Ross provided.
The link is a pdf www.miracleas.dk/images/upload/Docs/Richard Foote.pdf. titled:
Oracle B-Tree Index Internals:Rebuilding The Truth
Richard Foote
It also points out "good clustering but bad clustering factor" where leaf nodes exist in adjacent blocks. It also dispells myths that index rebuilds are the only way to rid whitespace.
Richard Foote and colleagues used block dumps after setting up each scenario to show the re-used space.
In anycase - its good to know we have the real experts here!
I have been getting significant performance increases by rebuilding tables and ordering the data by the most common foreign key. And CF greatly improved (closest to # of table blocks vs records) and, of course, of the only real
importance -query performance on range scans greatly improved.
Thanx again all for great advice and getting me researching on the right track.
Regards
Harry
|
|
|
|
| Re: Incorrect optimizer_index_caching affect on plan optimization [message #319481 is a reply to message #317414] |
Sun, 11 May 2008 19:46 |
TheSingerman
Messages: 49
Registered: April 2008
Location: Brighton, Michigan
|
Member |
|
|
A few bits of clarification:
OPTIMIZER_INDEX_CACHING describes the percent of index blocks which are found in the cache. A value of zero does not mean "let oracle use it's default behavior", it means "you will never find an index block in the cache". It is used by the CBO as part of it's cost calculation (it costs more to read from disk than from cache). The default value for this parameter is zero because then the calculation reduces to the release 7 values.
If you are likely to find an index in the cache, then this should be set to a non-zero number. Setting this parameter (in an alter session statement) is one way of forcing a nested loops or hash join, and sometimes it is the only way.
Whatever you set here must be somewhat of a guess. In 10g, Oracle will calculate better values for the CBO parameters, so it is better not to set it in the init.ora "file."
|
|
|
|
| Re: Incorrect optimizer_index_caching affect on plan optimization [message #319795 is a reply to message #319481] |
Tue, 13 May 2008 01:28 |
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
|
Senior Member |
|
|
Thx, Ross had aptly corrected me on the 0 % setting.
(let oracle decide) - my dba is tellin for 9.2.0.3 that 20% was the default setting (though the ora init showed default = N).
Ross,just to comment on your final answer of wisdom to me when I had asked if Oracle would re-evaluate the cost (once its goal and mine were in sync) when it chose the correct optimizer plan
was validated in the examp I sent to my dba.
I viewed the explain plan of one of my inner joins where a Nested_loop was incorrectly chosen (where my query beat the CBO using a hash_join).
I altered my session to set the optimizer index caching to zero.
When the next explain plan on the query showed the hash_join method as elected, I forced the nested_loop plan via an optimizer hint and observed that the CBO relative cost was now much higher than the hash_join.
I had to stop my inquiries due to politics, with the dba, who was adamant still that the 20% was correct per some cardinal rule - and as the buyout firm relinquished all our firm's dba's - here's wishing you worked for my co.!
thx for all your help
-Harry
ps - thank you for your expansion on the topic as well, Singerman
|
|
|
|
| Re: Incorrect optimizer_index_caching affect on plan optimization [message #320167 is a reply to message #319795] |
Wed, 14 May 2008 04:08 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Actually, TheSingerman was disagreeing with me, saying that 0 means 0, not "default behaviour".
I can't say that I have experimented to be certain. My statement was based on the link I provided above, which says:
| Oracle Database Reference | The default for this parameter is 0, which results in default optimizer behavior.
|
In light of TheSingerman's statement, I can see that this quote still has two possible interpretations.
I'm leaving it open - undecided either way.
The default was 0 in 9i as well. See http://download.oracle.com/docs/cd/B10501_01/server.920/a96536/ch1137.htm#1021404
And 8i (where it was first introduced): http://download-west.oracle.com/docs/cd/A87860_01/doc/index.htm
Like all initialisation parameters, feel free to update them, but you must know WHY you are doing it and what behaviour it will affect. It seems that neither of these is true at your site.
Ross Leishman
|
|
|
|
| Re: Incorrect optimizer_index_caching affect on plan optimization [message #320755 is a reply to message #320167] |
Fri, 16 May 2008 03:27 |
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
|
Senior Member |
|
|
At this point Ross - going on the original dogma of "goal" I am going to use the metric of how the optimizer behaves in regards to who or whom (computer or man) choses the quickest executing query. After swtiching back to zero, the optimizer and I were in-sync goal-wise.
Unfortunately the new corporate dba's and i were not in sync on that topic! Irrespective of the stats I provided they chose "20 is default! is best".
As to not stir up politics I relinquished my quest to synchronize the CBO with development "goals".
It was hard to bite my lip in response to
"Show my one case were a nested loop is slower than a hash join".
I provided the top join query off my arsenal of proof to which I was responded to with "Well those are big tables". My preferred response would have been to ask for one example where the nested_loop join method would be quicker...as all our tables are, relatively, "big".
I think with all the dependencies and nuances, the "goal" perspective combined with the metrics of actual performance time vs text advice is the way of merit to determine tuning.
In the whole journey I found a plethorae of information I am ignorant on and hence forth, return to my studies of index internals!
thanks all, again!
|
|
|
|
| Re: Incorrect optimizer_index_caching affect on plan optimization [message #321018 is a reply to message #317414] |
Sat, 17 May 2008 22:27 |
TheSingerman
Messages: 49
Registered: April 2008
Location: Brighton, Michigan
|
Member |
|
|
| Quote: | As to not stir up politics I relinquished my quest to synchronize the CBO with development "goals".
It was hard to bite my lip in response to
"Show my one case were a nested loop is slower than a hash join".
|
It is worth mentioning that rleishman's blog here on orafaq has several examples where a nested loop is slower than a hash join. His discussion of the 10104 trace is all by itself worth reading the post. Tom Kyte has several examples also (his 9i book has an example which should convince anyone).
I want to thank Ross for forcing me to read in detail what I had appearently skimmed before. Now I need to do some research and figure out some reasons when and why a Nested Loop join on two large result sets might be faster than a Hash Join.
|
|
|
|
| Re: Incorrect optimizer_index_caching affect on plan optimization [message #321213 is a reply to message #321018] |
Mon, 19 May 2008 08:03 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
I know we're getting a bit off-topic here, but a case where NL can come close to HASH on large result-sets exceeding 10% of their source segment is a UNIQUE INDEX UNIQUE SCAN that does not require a table lookup where the vast majority of the index reads are cached.
At this point, it becomes line-ball, but there is still a break-even point. As the relative proportions increase over 10%, the case for HASH becomes ever more compelling.
Multi-table joins are interesting as well. Hash Join 3 or more huge tables and you start to get memory contention problems. I haven't done a lot of research in this area, but it would definitely affect the break-even point.
Ross Leishman
|
|
|
|
| Re: Incorrect optimizer_index_caching affect on plan optimization [message #321620 is a reply to message #321213] |
Tue, 20 May 2008 21:02 |
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
|
Senior Member |
|
|
Hey Ross,
Very interesting, i'll have to look into that access method -
now just to distinguish, in that Unique scan - is the data from the join table be retrieved directly from the index?
You mention break-even point and thats exactly what I am seeking right now on my system. I've seen plenty of theoretical graphs concering access time and physical i/o based on volume and clustering and this or that hit ratio, but i'm all for experiential results at this point.
I have made leaps in performance tuning for indices in our devlopment region where i have some access (though all $session tables hidden from view) by starting with a simpe rebuild of tables in sort order of the main foreign key for good clustering, and then take advantage of index joins over highly clustered concatonated keys which were abound.
As to the hash performance - we have about 2.5 gig available in the pga which i thought was the driving factor for in-memory hashing (the hash sort size param defunct in 9i?)
After I complete my tests for the breakeven point i'll submit the stats,queires, and table/index structures, and environment details. Unfortunately i cant even turn autotrace on! grrrr
thx again for your valued info!
-Harry
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
