RE: Really strange performance issue
Date: Sat, 1 Nov 2014 08:27:44 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901E5B0C1_at_exmbx06.thus.corp>
But does it come up with two different bad plans or the same bad plan twice ?
That is a serious point (though you don't have to answer question). Cardinality feedback is purely about adjusting the estimated cardinality of the tables where the optimizer was "guessing". The effects of adding or deleting histograms may affect the consequences, but it (probably) doesn't affect the principles.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle
From: Andrew Kerber [andrew.kerber_at_gmail.com] Sent: 31 October 2014 15:38
To: Jonathan Lewis
Cc: oracle-l
Subject: Re: Really strange performance issue
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_at_jlcomp.demon.co.uk<mailto: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<mailto: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.
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_at_jlcomp.demon.co.uk<mailto: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<mailto:oracle-l-bounce_at_freelists.org> [oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>] on behalf of Jonathan Lewis [jonathan_at_jlcomp.demon.co.uk<mailto: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<mailto: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<mailto:mwf_at_rsiz.com>> wrote: +42
-----Original Message-----
From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [mailto: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<mailto: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<mailto:oracle-l-bounce_at_freelists.org> [oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>] on behalf of Andrew Kerber [andrew.kerber_at_gmail.com<mailto: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<mailto: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 Sat Nov 01 2014 - 09:27:44 CET