Re: Direct read stopped suddenly

I remember I saw in a presentation from the RWP guys the concept of small table replication, in which for parallel queries the table (if small enough) can be all replicated in buffer cache (but to be honest I don't remember exactly the test case and conditions)

... It might be the case if stats are gathered parallel on that particular node to have this happening.

În joi, 26 mai 2022 la 12:19, Jonathan Lewis <jlewisoracle_at_gmail.com> a scris:

>
> You're on an old version of Oracle, and I'm fairly sure I have a note
> somewhere (can't find it at present) that says something about the
> tablescan used during gather_table_stats() behaving differently from the
> tablescan used by a "normal" query. On a quick test on 19.11 I don't see
> any difference, though. It may be (i.e. I am inventing a hypothesis) that
> the query executed by the stat gathering call had to be read through cache
> in your case.
>
> You could test this hypothesis by checking how many blocks are in the
> cache on another node (excluding status = 'free'), then using the same call
> to gather stats on the table, and checking (a) if that leave a lot more
> blocks in the cache from the table and (b) what the session stats say about
> direct path reads and reads from cache etc.
>
> Regards
> Jonathan Lewis
>
>
> On Thu, 26 May 2022 at 09:16, Pap <oracle.developer35_at_gmail.com> wrote:
>
>> 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