RE: Really strange performance issue

  • From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: "andrew.kerber@xxxxxxxxx" <andrew.kerber@xxxxxxxxx>, Howard Latham <howard.latham@xxxxxxxxx>
  • Date: Thu, 30 Oct 2014 19:59:31 +0000

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().

Jonathan Lewis

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?


Other related posts: