RE: Really strange performance issue

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Thu, 30 Oct 2014 19:44:33 -0400
Message-ID: <050b01cff49b$75f1c1c0$61d54540$_at_rsiz.com>

+42

-----Original Message-----
From: 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; 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 [oracle-l-bounce_at_freelists.org] on behalf of Andrew Kerber [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>
wrote:
>
> Any chance of seeing the Query please?

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Received on Fri Oct 31 2014 - 00:44:33 CET