Re: latch: cache buffers chains

I forgot to ask for clarification about how you are populating a GTT on a standby database.
Is this taking advantage of the 12c "temp_undo_enabled" The primary example suggests you're looking at a table of 385MB, with reads requests of 1MB each.
Is the standby GTT likely to be about the same size - it's suffered 22GB of blocks read with an average read size of about 320KB. I'm now wondering if this is a problem of read-consistency: are the slaves doing 1MB reads of the GTT then going crazy checking temp undo segments using single block reads.

Can you run a test on the standby that runs in isolation so that v$sysstat will be (mostly) the sum of the PX session stats; or run the test and check v$px_sesstat for the query so that you can capture what the PX processes for the query coordinator are doing. Also a couple of (diffs of) snapshots of v$filestat might be a pointer to where the action is happening.

Regards
Jonathan Lewis

On Fri, 1 Oct 2021 at 11:17, Laurentiu Oprea <laurentiu.oprea06_at_gmail.com> wrote:

> Hello Jonathan,
>
> Regarding point 1 I was thinking now that might be a single reason, in
> primary stats are collected for the GTTs after insert while in standby are
> not. Still estimates looks correct (maybe dynamic sampling)
>
> The original flow and query is very complicated so I tried to reduce it to
> a simple count(*) select with a join to replicate the behavior (sql monitor
> in the print screen).
>
>
>
>
> În vin., 1 oct. 2021 la 12:34, Jonathan Lewis <jlewisoracle_at_gmail.com> a
> scris:
>
>> First guess -
>> Is there any reason why the primary would know that the parent session
>> had written a very large amount of data to the GTT while the standby thinks
>> the GTT is very small. In the latter case you may find that PX sessions are
>> using buffered reads when you normally expect parallel tablescans to use
>> direct path.
>>
>> Second guess -
>> Is is possible that the execution plan has changed so that the primary is
>> doing a tablescan of the GTT but the standby is doing a parallel tablescan
>> of something else and using that to drive an indexed access path into the
>> GTT
>>
>> Regards
>> Jonathan Lewis
>>
>>
>> On Fri, 1 Oct 2021 at 10:06, Laurentiu Oprea <laurentiu.oprea06_at_gmail.com>
>> wrote:
>>
>>> Hello everyone.
>>>
>>> Version 12.1
>>>
>>> Looks like for a sql statement executed in standby database, with
>>> parallel, that selects data from a GTT (with on commit delete rows) the PX
>>> slaves spends a significant amount of time into latch: cache buffers chains
>>> and read by other session . Same query executed in primary database has no
>>> issues. THis issue is observed just for GTTs.
>>>
>>> Anyone have any ideas on how to mitigate this issue?
>>>
>>> THank you.
>>>
>>>

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