Re: Really strange performance issue
From: Andrew Kerber <andrew.kerber_at_gmail.com>
Date: Fri, 31 Oct 2014 09:07:36 -0500
Message-Id: <386976D2-9AB8-4979-A661-B5938B067626_at_gmail.com>
They list two bugs, 13454409 and 16837274.
>> 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;
>>
>>
>> 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
>> _at_jloracle
>> From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Jonathan Lewis [jonathan_at_jlcomp.demon.co.uk]
>> 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
>> _at_jloracle
>> From: Andrew Kerber [andrew.kerber_at_gmail.com]
>> 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;
>>
>>
>>
>>
>> --
>> Andrew W. Kerber
>>
>> 'If at first you dont succeed, dont take up skydiving.'
Date: Fri, 31 Oct 2014 09:07:36 -0500
Message-Id: <386976D2-9AB8-4979-A661-B5938B067626_at_gmail.com>
They list two bugs, 13454409 and 16837274.
Sent from my iPad
> On Oct 31, 2014, at 8:48 AM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> 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 > _at_jloracle > From: Andrew Kerber [andrew.kerber_at_gmail.com] > 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. > >>> FROM wdata, wbedata
>> SELECT 'Data4',
>> wdata.created,
>> wdata.value2
>> 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_at_jlcomp.demon.co.uk> 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
>> _at_jloracle
>> From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Jonathan Lewis [jonathan_at_jlcomp.demon.co.uk]
>> 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
>> _at_jloracle
>> From: Andrew Kerber [andrew.kerber_at_gmail.com]
>> 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_at_rsiz.com> wrote: >>> +42 >>> >>> -----Original Message----- >>> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] >>> On Behalf Of Jonathan Lewis >>> Sent: Thursday, October 30, 2014 4:00 PM >>> To: andrew.kerber_at_gmail.com; 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 >>> _at_jloracle >>> >>> ________________________________________ >>> From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on >>> behalf of Andrew Kerber [andrew.kerber_at_gmail.com] >>> 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_at_gmail.com> >>> 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.'
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Oct 31 2014 - 15:07:36 CET