Re: Direct read stopped suddenly

Just throwing in another thought.

Why would a large fraction of the table be in the cache already? Maybe it's a side effect of a high-volume update to the table that has left a large number of dirty blocks in CUR mode in the cache, in which case an attempt to force a direct read might mean those blocks have to be written before the direct path read can start. Before trying to resolve the issue caused by "the current state" it's best to understand exactly what the current state is and how you got into it in case the resolution actually makes things worse.

Regards
Jonathan Lewis

On Wed, 25 May 2022 at 14:39, Andy Sayer <andysayer_at_gmail.com> wrote:

> Just to add my 22c, if the object is pretty much completely in memory
> already - that should be faster to read than going to the storage cells.
>
> Sure, there’s some wizardry being performed by storage indexes which isn’t
> replicated on the instance side (you have to read the whole table from
> memory to full scan it with a predicate) but surely it can’t be
> significantly worse? Assuming you’re benefiting from storage indexes - you
> might have a case for creating a real index.
>
> Thanks,
> Andy
>
> On Wed, 25 May 2022 at 14:25, Pap <oracle.developer35_at_gmail.com> wrote:
>
>> Also Sayan, as it appears to be availability or presence of certain
>> percentage of blocks in the buffer cache out of total number of blocks of
>> the table , is the deciding factor between the smart scan vs buffered read
>> of an object. So is it good idea to have some kind of alerting done so as
>> to catch this odd situation beforehand and then flush the object from the
>> buffer cache proactively to avoid this issue?
>>
>>
>>
>>

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