RE: Time increase after first time running

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 2 Jun 2014 07:05:19 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D901DF8418_at_exmbx05.thus.corp>

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
_at_jloracle

---

From: Mohamed Houri [mohamed.houri_at_gmail.com] 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_at_jlcomp.demon.co.uk<mailto:jonathan_at_jlcomp.demon.co.uk>>:

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
_at_jloracle

---

From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> [oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org>] on behalf of Eriovaldo Andrietta [ecandrietta_at_gmail.com<mailto:ecandrietta_at_gmail.com>] 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_at_evdbt.com<mailto:tim_at_evdbt.com>>: 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>

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

Received on Mon Jun 02 2014 - 09:05:19 CEST