Re: Really strange performance issue
- From: Andrew Kerber <andrew.kerber@xxxxxxxxx>
- To: "Mark W. Farnham" <mwf@xxxxxxxx>
- Date: Sat, 1 Nov 2014 08:55:38 -0500
Hah! Not many 'will' statements you can make when looking at this sort of
stuff. But I will take a look at that.
Sent from my iPhone
> On Nov 1, 2014, at 8:38 AM, Mark W. Farnham <mwf@xxxxxxxx> wrote:
>
> IF the intention exists that other(job_type,parm_type) is unique, that
> assertion as a constraint, as JL mentioned, might help the optimizer and
> produce a plan such that feedback does NOT make the undesired change
> currently observed. (underline “might” and notice it is not “will”).
>
> From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
> Behalf Of Andrew Kerber
> Sent: Friday, October 31, 2014 8:37 AM
> To: Jonathan Lewis
> Cc: oracle-l
> Subject: Re: Really strange performance issue
>
> Jonathan - you are correct on the oddities, but there is no unique
> constraint. The row is unique however, and there are only about 30 rows in
> the table. I think I have any typos corrected below.
>
> Oracle support says it is a bug, no patch available yet, and the work around
> is to set _optimizer_use_feedback=false. Now I need to figure out the
> ramifications of doing that.
>
>
>
> 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 = wbedata.value3)
> ORDER BY wdata.created;
> Sent from my iPad
>
> On Oct 31, 2014, at 4:15 AM, Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
> wrote:
>
>
>
> I'm assuming, by the way, that any oddities in the sample code are just the
> result of trying to edit out the confidential stuff.
>
> In particular: I've asssumed that there's a unique constraint on (job_type,
> parm_type) so that the optimizer can "know" that there's only a single
> possible value; and I've assumed that the subquery is written to supply the
> column type and hasn't thrown in another obfuscating factor by causing column
> conversion.
>
>
> Regards
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
> @jloracle
> From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
> of Jonathan Lewis [jonathan@xxxxxxxxxxxxxxxxxx]
> Sent: 31 October 2014 08:57
> To: Andrew Kerber; Mark W. Farnham
> Cc: Howard Latham; oracle-l
> Subject: RE: Really strange performance issue
>
>
> 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 = wbedata.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.'
Other related posts:
