Re: Default optimizer_index_caching value DUMB ...

<ctcgag_at_hotmail.com> wrote in message
news:20041029123047.425$gR_at_newsreader.com...
> "Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote:
>>
>> Jonathan Lewis has an article on system statistics being potentially
>> unfortunate for you on his site that in passing deals with the problem
>> that O_I_C_A and O_I_C are both system wide measures and , typically,
>> it is process level problems that we wish to deal with.
>
> I've pondered the possibility that there could be more than one Nash
> equilibrium with these setting. If you tell CBO that indices aren't
> cached, then it will choose FTS and the indices won't be used and hence
> won't be cached. But if you tell CBO the indices are cached, then it will
> choose to use them, which will cause them to become cached.

:)

There will likely be some of that. Oftentimes though an uncached index read will be a better bet than a cached FTS. I'd expect the effect you descibe to be most prevalent when the access plan is not very efficient either way (meaning bad indexes,bad sql or bad design or all 3). In this case it probably isn't the parameters you wish to fix.

-- 
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com