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

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

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

> 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