Re: optimizer_ ???
- From: Niall Litchfield <niall.litchfield@xxxxxxxxx>
- To: palteheld@xxxxxxxx
- Date: Tue, 26 Apr 2005 14:05:03 +0100
On 4/26/05, Peter Alteheld <palteheld@xxxxxxxx> wrote:
> Hi Chris
>=20
> thank you very much for clearing this point so prompt. I haven't expected=
such a detailed example - thank you!
>=20
> How huge can the impact on the response time be by tweaking oic? Precisel=
y: do you know of an example where you gain a response time reduction which=
is very impressive?
I had cause to Google this just recently and I can't find any examples
of changes to response time. Moreover in trying to demonstrate my base
argument, that response time may go up,down or remain unchanged in
response to changes in oic - and oica and that this is sql statement
(and data) dependent and not system dependent I found that in the vast
majority of cases the actual change in response time was negligible.
On reflection I think that I'd expect that, except for large changes
in the parameters, since
1. the cost is an estimate of the elapsed time a query will take and=20
2. *in my observation* there *tend* to be either one (or sometimes 2
or 3) plans that are streets ahead of the other possible choices of
execution plan and
3. *observation and general trends again* that if there are 2 or 3
they tend to have very similar response times.
then for the vast majority of sql statements tuning OIC is unlikely to
make much perceptible difference - even if the plans do change.
That all said I do think that *if you are setting up a system* it is
probably sensible and entirely justifiable to set OIC to some
relatively high figure (80s or 90s) - in other words to correct one of
(Wolfgangs?) fallacies - that every index read will be a physical IO.
I suspect that setting system stats is the right thing to do instead
of setting OICA.
--=20
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
