RE: Really strange performance issue
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <jonathan@xxxxxxxxxxxxxxxxxx>, <andrew.kerber@xxxxxxxxx>, "'Howard Latham'" <howard.latham@xxxxxxxxx>
- Date: Thu, 30 Oct 2014 19:44:33 -0400
+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?
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
