Re: 12.1.0.2 ADG Reading 1 block at a time? (TEMP / V$SORT_Segment)

From: Chris Taylor <christopherdtaylor1994_at_gmail.com>
Date: Wed, 22 Jan 2020 14:55:58 -0500
Message-ID: <CAP79kiT3jTR2gjmzZQ0r8fzJLgyLMGY+bsn-yPUQZbpT32p8fg_at_mail.gmail.com>



Wow, that's very interesting.

Doesn't exactly explain why I see 1 block at a time in the trace file, but would definitely help explain why I see so MANY I/Os from disk.

Thanks,
Chris

On Wed, Jan 22, 2020 at 2:28 PM Ilmar Kerm <ilmar.kerm_at_gmail.com> wrote:

> <I hope this email comes through only once, sorry, if duplicate>
> I remember seeing SR a year ago from a colleague about a similar thing on
> 12.1.0.2. We had similar issue, same/similar execution plan, but execution
> time on ADG much worse. Copy&paste from SR:
>
>
> Bug 25219941 : WITH SUBQUERY FACTORIZATION TEMP TABLE DOES NOT CACHE IN ADG
> closed as not a bug
>
> PROBLEM:
> --------
> huge IOs got in ADG caused from temp tables generated from with subquery
> factorization.
> .
> these temp tables were not be able to be cached in ADG the same as primary
> DB
> although the buffer_cache and PGA settings are similar in both primary and
> standby DBs
>
>
> WORKAROUND:
> _at_ -----------
> _at_ "_with_subquery" =inline
>
>
> -- why this is not a bug.
>
> _at_ There is no code defect here. The change of behaviour is to prevent
> _at_ wrong results from temp tables on standby. Temp materialization
> _at_ on standby has to go to disk.
> _at_ .
> _at_ "On ADG, scn cannot be moved. Thus temp blocks have wrong cr scns. This
> _at_ reuslts in wrong results for queries using temp materialization" - so the
> _at_ blocks have to be read from disk.
>
>
>
> On Wed, Jan 22, 2020 at 6:11 PM Jonathan Lewis <
> jonathan_at_jlcomp.demon.co.uk> wrote:
>
>> A partial answer - it relates to the WITH subquery.
>>
>> You've materialized a WITH subquery and then your main query is something
>> like;
>>
>> select
>> (aggregate scalar subquery),
>> (aggregate scalar subquery),
>> other columns
>> from
>> materialized_with_subquery msq1
>> where
>> simple_condition
>> or exists (
>> select from materialized_with_subquery msq2
>> correlated with msq1
>> )
>>
>> (You may not have written it quite like this, but the optimizer has
>> transformed what you wrote into something like this.
>>
>> So you scan the materialized CTE, and as you do so you repeatedly scan
>> the materialized CTE to execute the subquery.
>>
>> I'll offer the guess that for some reason the CTE isn't cached properly
>> (maybe it caches only the first block it uses in the subquery, so loses the
>> rest).
>>
>>
>>
>> Regards
>> Jonathan Lewis
>>
>
> --
> Ilmar Kerm
>

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Jan 22 2020 - 20:55:58 CET

Original text of this message