Re: Direct read stopped suddenly

Thank You All.

_at_Jonathan, I didnt see any such update query on that object. But i do see a stat gather ran on same object just before this issue started. Not sure how this would be related though as because there is no big difference in num_rows or block count from the past stats gathers.

Below were the last three entries in WRI$_OPTSTAT_TAB_HISTORY and the issue appeared on the 23rd may stats collection.

ROWCNT BLKCNT SAMPLESIZE ANALYZETIME 62153801 757301 62153801 5/23/2022 10:11:21 PM

56400041 683645 56400041 5/7/2022 10:11:29 PM

50958835 618173 50958835 4/20/2022 10:12:16 PM

On Wed, 25 May 2022, 11:34 pm Jonathan Lewis, <jlewisoracle_at_gmail.com> wrote:

>
> 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