RE: Really strange performance issue

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 1 Nov 2014 15:26:31 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901E5B1B2_at_exmbx06.thus.corp>


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
_at_jloracle



From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Jonathan Lewis [jonathan_at_jlcomp.demon.co.uk] Sent: 01 November 2014 14:10
To: oracle-l_at_freelists.org
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
_at_jloracle



From: Mark W. Farnham [mwf_at_rsiz.com]
Sent: 01 November 2014 13:38
To: andrew.kerber_at_gmail.com; 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk>> 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
_at_jloracle



From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>] on behalf of Jonathan Lewis [jonathan_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk>] 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
_at_jloracle



From: Andrew Kerber [andrew.kerber_at_gmail.com<mailto:andrew.kerber_at_gmail.com>] 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_at_rsiz.com<mailto:mwf_at_rsiz.com>> wrote: +42

-----Original Message-----
From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [mailto: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<mailto: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<mailto:oracle-l-bounce_at_freelists.org> [oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>] on behalf of Andrew Kerber [andrew.kerber_at_gmail.com<mailto: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<mailto: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




--
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

--
http://www.freelists.org/webpage/oracle-l
Received on Sat Nov 01 2014 - 16:26:31 CET

Original text of this message