Re: Time increase after first time running
- From: Dave Morgan <oracle@xxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 02 Jun 2014 19:13:50 -0600
From: Tim Gorman <tim@xxxxxxxxx>
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@xxxxxxxxxxxxxxxxxx>
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@xxxxxxxxxxx
403 399 2442
--
//www.freelists.org/webpage/oracle-l
Other related posts:
