Looking at the code, I think Sayan's comment is the relevant one. Waving my hands and guessing WILDLY - but I suspect I could create a data set where this happens: The optimizer is probably handling your subqueries as "unknown constant", which gives you a range scan on unknown values which gives the optimizer a guess of 0.25% - hence the application of cardinality feedback. On the first pass the optimizer drives off the created date - and discovers that it does a lot more work than expected (more rows), so on the second pass it reverses the join, which turns out to be a bad idea because the optimizer's estimated cardinality of '88888' (which doesn't get modified by the first pass) is badly wrong and/or the chosen access path back into wdata is much less efficient than expected. Regards Jonathan Lewis http://jonathanlewis.wordpress.com @jloracle ________________________________ From: Andrew Kerber [andrew.kerber@xxxxxxxxx] Sent: 31 October 2014 01:32 To: Mark W. Farnham Cc: Jonathan Lewis; Howard Latham; oracle-l Subject: Re: Really strange performance issue Below is a heavily redacted version of the query, all columns names and values changed,as I said it is pretty straightforward, SELECT 'Data4', wdata.created, wdata.value2 FROM wdata, wbedata WHERE wbedata.my_number = '888888' AND (wdata.created <= (select trim(value_string) from other.parm_value where job_type = 'D TEST' and parm_type = 'B END') || '-23.59.59.999999' AND (wdata.created >= (select trim(value_string) from other.parm_value where job_type = 'D TEST' and parm_type = 'B START') || '-23.59.59.999999')) AND (wdata.created = wbedata.created) AND (wdata.value2 = wbedata.value2) AND (wdata.value3 = data1.value3) ORDER BY wdata.created; On Thu, Oct 30, 2014 at 6:44 PM, Mark W. Farnham <mwf@xxxxxxxx<mailto:mwf@xxxxxxxx>> wrote: +42 -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx> [mailto:oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>] On Behalf Of Jonathan Lewis Sent: Thursday, October 30, 2014 4:00 PM To: andrew.kerber@xxxxxxxxx<mailto:andrew.kerber@xxxxxxxxx>; Howard Latham Cc: oracle-l Subject: RE: Really strange performance issue Although we generally expect cardinality feedback to result in better plans it's possible that a change in plan could change the order in which the data driving (e.g.) a scalar subquery is accessed, increasing the number of times a subquery is executed without changing the number of rows returned in the rowsource. If by "embedded select" you actually mean a scalar subquery it's possible that the main query does look more efficient to the optimizer, but the scalar subquery runs far more time. Easy to detect if you enable rowsource execution statistics (e.g. add hint gather_plan_statistics) and use the 'allstats last' format option with dbms_xplan.display_cursor(). Regards Jonathan Lewis http://jonathanlewis.wordpress.com @jloracle ________________________________________ From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx> [oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>] on behalf of Andrew Kerber [andrew.kerber@xxxxxxxxx<mailto:andrew.kerber@xxxxxxxxx>] Sent: 30 October 2014 14:51 To: Howard Latham Cc: oracle-l Subject: Re: Really strange performance issue I'll have to see if I can remove identifying information, but there is really nothing special about it, basically a two table join with a couple of embedded selects to get a date range. The plan is the same in both cases. Sent from my iPad > On Oct 30, 2014, at 9:42 AM, Howard Latham > <howard.latham@xxxxxxxxx<mailto:howard.latham@xxxxxxxxx>> wrote: > > Any chance of seeing the Query please? -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l -- Andrew W. Kerber 'If at first you dont succeed, dont take up skydiving.'