Thanks to Ilmar Kerm, this is now solved.
Per Oracle Note:
With Subquery Factorization Temp Table Does Not Cache in Standby in
12.1.0.2 (Doc ID 2251339.1)
Running the query with the alter session statement from the note returns
the results in:
2400 rows selected.
Elapsed: 00:00:07.02
That's CRAZY
Chris
On Wed, Jan 22, 2020 at 2:28 PM Ilmar Kerm <ilmar.kerm@xxxxxxxxx> 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:
@ -----------
@ "_with_subquery" =inline
-- why this is not a bug.
@ There is no code defect here. The change of behaviour is to prevent
@ wrong results from temp tables on standby. Temp materialization
@ on standby has to go to disk.
@ .
@ "On ADG, scn cannot be moved. Thus temp blocks have wrong cr scns. This
@ reuslts in wrong results for queries using temp materialization" - so the
@ blocks have to be read from disk.
On Wed, Jan 22, 2020 at 6:11 PM Jonathan Lewis <
jonathan@xxxxxxxxxxxxxxxxxx> 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
