Chris,
The plan looked fine to me.
One oddity - why do you have optimizer_features_enable set to 9.2.0.8 ?
That's a bit extreme for a 12.1.0.2 database.
It's also not obvious why the optimizer could manage to choose a nested loop at
operation 23 when its first child is predicted to have 32M rows and its second
child is the view at operation 90 that has a cost of 481 each time it is
re-evaluated. But perhaps that because 9.2.0.8 couldn't do a "hash join right
outer" and thinks the nested loop outer will be cheaper than a merge join outer.
Since you've got the trace file, do you have the bit where Oracle dumps the
STAT lines so that you can get the Rowsource Operation plan ?
Regards
Jonathan Lewis
________________________________________
From: Chris Taylor <christopherdtaylor1994@xxxxxxxxx>
Sent: 22 January 2020 17:26
To: Jonathan Lewis; Cary Millsap; andy@xxxxxxxxxxxxxxx
Cc: Oracle L
Subject: Re: 12.1.0.2 ADG Reading 1 block at a time? (TEMP / V$SORT_Segment)
FYI - I formatted that dbms_xplan info as "fixed width" in my reply below but
I'm not sure if it preserved the formatting when you guys received it.
Chris
<snipped rest of message>
--
//www.freelists.org/webpage/oracle-l
