Re: Time increase after first time running

In fact , now I solved the issue doing it and I would like to say to all what I did:

1.) Statment sql Before (this is just the idea, the query is too big) :

create or replace view ...
 select columns
   from tables1,

           (select columns from tables2) tables2  where tables2.id = tables1.id;

running the sql above, the first time was 30 seg , running again, the time is 2m30seg
I checked the plan using autot traceonly and is are the same in two situations.

2.) Current statment sql :
create or replace view ...
with tables2 as
select /*+ MATERIALIZE */ columns from tables2 AS
 select columns
   from tables1,

            tables2
 where tables2.id = tables1.id;

running the sql 2 above, the first time was 30 seg , running again, the time keeps 30 seg
I also checked the plan using autot traceonly and is are the same in two situations.

My question is : why the second query keeps the time running several times ?

                          and the first does not keep the same time for

Thanks for attention.

2014-06-02 4:05 GMT-03:00 Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>:

>
> 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>:
>
>>
>> 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-l