Re: Really strange performance issue

  • From: Andrew Kerber <andrew.kerber@xxxxxxxxx>
  • To: "jonathan@xxxxxxxxxxxxxxxxxx" <jonathan@xxxxxxxxxxxxxxxxxx>
  • Date: Fri, 31 Oct 2014 10:38:41 -0500

Interestingly, the performance is highly dependent on histograms on the wbedata 
table.  If I delete histograms on that table, it comes up with a bad plan 
regardless of the optimizer use feedback setting.  If I put the histograms back 
in, it goes back to the original behavior.  I'm not sure if that is useful 
information, but it is interesting. 

Sent from my iPad

> On Oct 31, 2014, at 8:48 AM, Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx> 
> wrote:
> 
> 
> Andrew,
> 
> If Oracle gave you a visible bug number I'd be interested to see what it was.
> 
> 
>    
> Regards
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
> @jloracle 
> From: Andrew Kerber [andrew.kerber@xxxxxxxxx]
> Sent: 31 October 2014 12:37
> 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?
>>> --
>>> http://www.freelists.org/webpage/oracle-l
>>> 
>>> 
>>> --
>>> http://www.freelists.org/webpage/oracle-l
>> 
>> 
>> 
>> -- 
>> Andrew W. Kerber
>> 
>> 'If at first you dont succeed, dont take up skydiving.'

Other related posts: