RE: Time increase after first time running
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- 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.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Eriovaldo Andrietta [ecandrietta@xxxxxxxxx]
Sent: 01 June 2014 23:07
To: Tim Gorman
Cc: ORACLE-L
Subject: Re: Time increase after first time running
Hi Tim,
Thanks for answer.
I agree with you.
I did the question as is, by the strange situation, normally occurs time
decrease and not time increase .
I know that I must consider the charge of the environment and lot of things.
but in my case, the same query after running the first time , it increases the
time running again and several times.
I used the hint /*+ MATERIALIZE */ and now the query is keeping to same time.
Regards
Eriovaldo
2014-06-01 18:29 GMT-03:00 Tim Gorman <tim@xxxxxxxxx<mailto:tim@xxxxxxxxx>>:
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.
On 5/31/2014 9:49 PM, Eriovaldo Andrietta wrote:
Hello,
I have a view that run in a Oracle 11g with RAC and when I create the view and
run the first time, the time is : 30 seg
The at the second, third ... the time go to 2m:30seg
Why does it happen ?
I expect less than 5 seconds after firts time ...
Strange ...
Regards
Eriovaldo
Other related posts:
