Re: optimizer_ ???

On 4/26/05, Peter Alteheld <palteheld_at_yahoo.de> 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

--
http://www.freelists.org/webpage/oracle-l