Re: Really strange performance issue

From: Andrew Kerber <andrew.kerber_at_gmail.com>
Date: Thu, 30 Oct 2014 20:32:30 -0500
Message-ID: <CAJvnOJZ0AivrgJ3Xs0v-eu-33iN3to=HkoghqkN+BNZDcWV4Gw_at_mail.gmail.com>

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_at_rsiz.com> wrote:

> +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
>
>
>

-- 
Andrew W. Kerber

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

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

Received on Fri Oct 31 2014 - 02:32:30 CET