RE: Really strange performance issue
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 1 Nov 2014 15:26:31 +0000
Sorry, got ahead of myself there.
The "constant subquery" == constant value only applies to
select literal from table by primary/unique key
Not to
select column from table by primary/unique
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: 01 November 2014 14:10
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Really strange performance issue
A particular feature of the uniqueness constraint is that in 12c, and
back-ported to 11.2.0.4, the optimizer can acquire the value at optimisation
time, so the selectivity switches to "known range" rather than 0.25% unknown
range. http://jonathanlewis.wordpress.com/2014/09/29/12c-fixed-subquery-2
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: Mark W. Farnham [mwf@xxxxxxxx]
Sent: 01 November 2014 13:38
To: andrew.kerber@xxxxxxxxx; Jonathan Lewis
Cc: 'oracle-l'
Subject: RE: Really strange performance issue
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<mailto: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<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>] on behalf
of Jonathan Lewis
[jonathan@xxxxxxxxxxxxxxxxxx<mailto: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<mailto: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<mailto:mwf@xxxxxxxx>> wrote:
+42
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[mailto: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<mailto: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<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>] on
behalf of Andrew Kerber
[andrew.kerber@xxxxxxxxx<mailto: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<mailto: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:
