Re: Time increase after first time running
Date: Mon, 2 Jun 2014 08:43:46 +0200
Message-ID: <CAJu8R6ifsoYwGAOtAFDdYN2pzO9vArcNtA4u_Bwgdngp5gi6tw_at_mail.gmail.com>
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>:
>
> 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 [oracle-l-bounce_at_freelists.org] on
> behalf of Eriovaldo Andrietta [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>:
>
>> 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/> 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-lReceived on Mon Jun 02 2014 - 08:43:46 CEST