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> wrote: > +42 > > -----Original Message----- > From: 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; 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 [oracle-l-bounce@xxxxxxxxxxxxx] on > behalf of Andrew Kerber [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> > 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.'