Re: Time increase after first time running

From: Dave Morgan <oracle_at_1001111.com>
Date: Mon, 02 Jun 2014 19:13:50 -0600
Message-ID: <538D214E.9020202_at_1001111.com>



> From: Tim Gorman <tim_at_evdbt.com>
> Subject: Re: Time increase after first time running

> You've not provided basic information (i.e. Oracle version, platform and
> version, etc) nor have you provided any specific information (i.e. SQL text of the view,
> SQL trace/TKPROF output, DBMS_XPLAN ALLSTATS LAST output, etc).

> Your chances of receiving a useful response are
> extremely low.

I don't know about that. I have supplied no information at all, not even written to the list...

trim and snip .....

>
> From: Jonathan Lewis<jonathan_at_jlcomp.demon.co.uk>
> Subject: RE: Time increase after first time running
> Date: Mon, 2 Jun 2014 05:02:17 +0000
>
> As Tim highlights - if anyone gives you the right answer it will be a lucky guess.
> But if you would like to take my guess as a starting point for your investigation then I'd go for adaptive cursor-sharing or cardinality feedback causing Oracle to re-optimise on the second run because the first run didn't match expectation - and sometimes the re-optimisation will produce a plan that is slower.
>
> The materialize may help because (a) it stops Oracle from finding the slow thing when it re-optimises, or (b) (as as specific case of (a)) it may be that putting the subquery inline makes the statement sufficiently complex that it pushes the optimizer into the cardinality feedback / adaptive cycle.
>

And look at what I learn :) Heck, I can't even understand Jonathan's stuff most of the time :) Other than drinks at Openworld :)

Dave

-- 
Dave Morgan
Senior Consultant, 1001111 Alberta Limited
dave.morgan_at_1001111.com
403 399 2442
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Jun 03 2014 - 03:13:50 CEST

Original text of this message