Re: 12.1.0.2 ADG Reading 1 block at a time? (TEMP / V$SORT_Segment)
Date: Wed, 22 Jan 2020 17:50:53 +0000
Message-ID: <LNXP265MB15621AB138FA7167CC990EA3A50C0_at_LNXP265MB1562.GBRP265.PROD.OUTLOOK.COM>
Chris,
The plan looked fine to me.
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
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
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.
Jonathan Lewis
From: Chris Taylor <christopherdtaylor1994_at_gmail.com>
Sent: 22 January 2020 17:26
To: Jonathan Lewis; Cary Millsap; andy_at_oracledepot.com
Cc: Oracle L
Subject: Re: 12.1.0.2 ADG Reading 1 block at a time? (TEMP / V$SORT_Segment)
<snipped rest of message>
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Jan 22 2020 - 18:50:53 CET