RE: how to evaluate optimizer_index_caching
- From: "Polarski, Bernard" <Bernard.Polarski@xxxxxxxxxxxxxx>
- To: "Mark W. Farnham" <mwf@xxxxxxxx>
- Date: Mon, 22 Jan 2007 15:41:40 +0100
This is a very conservative approach where you state that old situation
was good and you track what went wrong.
However when you speak of upgrade, customers expects 'goodies' and once
you have cleaned what went wrong after the upgrade, you need to provide
to 'goodies' which at this moment will at least be as good as before
since it was not the list of stuff to clean. Hence we are back to 'how
do I know if I am optimal'. I would even say that after a release
upgrade (and I am preparing the init.ora of a 10gr2 from a 10gr1) the
situation is even worse:
I would like to show better but I have no way to assert my starting own
situation toward the optimal. In the 'air' we were, in the 'air' we
remains and if boss ask 'how are we better now' the only response on the
spot is 'we got a higher release and following arithmetic 10 > 9'. The
way to assert that we are better is the phone ringing less or I am too
pessimistic.
Bernard Polarski
Oracle DBA
-----Original Message-----
From: Mark W. Farnham [mailto:mwf@xxxxxxxx]
Sent: maandag 22 januari 2007 15:32
To: Polarski, Bernard; gints.plivna@xxxxxxxxx; knecht.stefan@xxxxxxxxx
Cc: Oracle Freelists.org
Subject: RE: how to evaluate optimizer_index_caching
Perhaps the reverse logic will help somewhat:
If you upgrade and all your plans go to hell in a hand cart, then it is
worth a try to tweak index caching while you figure things out.
It would have been better to save outlines for the queries that do the
preponderance of your load before the upgrade, use the outlines,
not tweak "affect all parses and plans" parameters and thus make it more
probable to easily find the newly bad plans, but I'm assuming you don't
have
that time machine available.
On part b, the real data being sampled from the system will likely be
superior in effect than any tweak you provide to the parameters with
guesses
for plug number from some set value mined from a paper or posting having
nothing to do with your actual case.
Overall the effect of the parameter is to favor or disfavor nested loop
solutions.
Can you find your bad (worst) plans? Were they formerly good or better
with
a previous release or statistics which generated nested loop plans?
Is it practical to revise the bad (worst) plans that produce a
significant
load or user inconvenience without toggling a parameter that may snipe
other
currently good plans and which definitely tells a semi-permanent lie to
the
optimizer which you will ever after be reluctant to change because you
cannot manage the side effects?
Specific recommendations on tweaking general parameters require specific
situations to diagnose.
Finally, remember it is not the sheer quantity of plans but rather the
resultant significant cost and/or user inconvenience that is important.
Instrumentation, profiling, and tracing can help you figure that out
from a
dramatic subset of "a whole DB" if you don't already know the answer
from
your knowledge of the database you are considering.
It was the experience of many application suite upgrades in the last few
years that tweaks to the cache parameters were an effective bandaid, but
clear experimental evidence has been presented (a lot from members of
this
list server) that there is no magic general value for these parameters
for
the general case.
I hope this helps,
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On
Behalf Of Polarski, Bernard
Sent: Monday, January 22, 2007 8:24 AM
To: gints.plivna@xxxxxxxxx; knecht.stefan@xxxxxxxxx
Cc: Oracle Freelists.org
Subject: RE: how to evaluate optimizer_index_caching
Thanks for the resource. I read it and it is the most complete up to now
I ever found. I noticed 2 recommandations :
a) First one is an implicit opinion taken out of a diagram in the pdf:
"If most of the executions plan are good then do not Modify
optimizer_index_caching/cost_adj"
First glance, sound wise words but ... it is a big problem to assert
that an SQL plan is optimal and you have to do it on 'most' SQL? In
practical, it is not feasible to study a whole DB just to respond to one
question. But at least there is a price on the question.
b) "With system statistics, the default value is usually good"
Which reformulate point a: if you don't know, don't touch.
So we have a least an answer: study all the DB and if most of the SQL
plan are not good then tweak the values of
optimizer_inder_caching/cost_adj.
No surprising that there is so little resource on these parameters. When
I google on it, I got 'optimizer_index_(xxx).. blabla ... favour index
access blabla...' and that's it.
Bernard Polarski
Oracle DBA
-----Original Message-----
From: Gints Plivna [mailto:gints.plivna@xxxxxxxxx]
2007/1/22, Stefan Knecht <knecht.stefan@xxxxxxxxx>:
> My colleague, christian antognini held a good presentation at last
year's
> miracle db forum in denmark about the "CBO configuration roadmap"
where he
> explains those as well -- google for it
http://www.trivadis.com/Images/CBOConfigurationRoadmap_tcm17-14317.pdf
Yeahh that's good article. I'v also recommended that for several
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
