OOOOH --- Forgot I had thrown that in there. I did that only in my test
script last night to see if it was a new transformation and forgot I had
that in there this morning.
(Sigh)
That means the plan is bogus as well for that dbms_xplan i pulled out.
Gah. Sorry about that.
Let me pull down another one without that in there.
Chris
On Wed, Jan 22, 2020 at 12:51 PM Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
wrote:
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
