RE: Really strange performance issue

  • From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: oracle-l <Oracle-L@xxxxxxxxxxxxx>, Andrew Kerber <andrew.kerber@xxxxxxxxx>
  • Date: Fri, 31 Oct 2014 09:15:52 +0000


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 = data1.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: