RE: Time increase after first time running
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 2 Jun 2014 07:05:19 +0000
I agree, so long as we're sticking with guessing exactly what the OP really
meant, cardinality feedback seems to be a little more likely than adaptive
cursor_sharing.
A couple more circumstances that could trigger adaptive cursor sharing - bind
variable involving partition key, bind variables defining a range predicate.
(Neither case requires histograms to exist)
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: Mohamed Houri [mohamed.houri@xxxxxxxxx]
Sent: 02 June 2014 07:43
To: Jonathan Lewis
Cc: ORACLE-L
Subject: Re: Time increase after first time running
It is probably the effect of cardinality feedback that made the second
execution plan worse than the first one. If this is the case then your second
execution plan will have a Note specifying this
Note
-----
- cardinality feedback used for this statement
If it is really your case then try to cancel the appearance of this feature
using the hint
opt_param('_optimizer_use_feedback' 'false')
As per regards to Adaptive cursor sharing, I don’t think it is the case here
because you cursor needs to fulfil several conditions before Adaptive cursor
sharing kicks in; it needs to be bind sensitive (which might mean you should
have histogram on your predicate column) and bind aware before the CBO peeks at
the bind variable to see if its selectivity fits an existing child cursor or
needs to hard parse a new one. And even if ACS kicks in there is probably more
chance that the new execution plan produced by ACS will be more optimal than
the one due to cardinality feedback
Best regards
Mohamed Houri
2014-06-02 7:02 GMT+02:00 Jonathan Lewis
<jonathan@xxxxxxxxxxxxxxxxxx<mailto:jonathan@xxxxxxxxxxxxxxxxxx>>:
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<mailto:oracle-l-bounce@xxxxxxxxxxxxx>
[oracle-l-bounce@xxxxxxxxxxxxx<mailto:oracle-l-bounce@xxxxxxxxxxxxx>] on behalf
of Eriovaldo Andrietta [ecandrietta@xxxxxxxxx<mailto: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
--
Houri Mohamed
Oracle DBA-Developer-Performance & Tuning Expert
Member of Oraworld-team<http://www.oraworld-team.com/>
[http://www.oraclefromguatemala.com.gt/wp-content/uploads/2014/03/oraworld.png]
Visit My - Blog<http://www.hourim.wordpress.com/>
Let's Connect - <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/> Linkedin
Profile<http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>
My Twitter<https://twitter.com/MohamedHouri> -
MohamedHouri<https://twitter.com/MohamedHouri>
Other related posts:
