Re: query performance following 12c upgrade

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 24 Sep 2015 15:28:32 +0100
Message-Id: <D93172F0-FE9E-4DFA-BA5C-48B3DBB67D03_at_jlcomp.demon.co.uk>

10046 to start with.
You can email it to me direct if you prefer Won't be able to review it until this evening

Regards

Sent from my iPad

> On 24 Sep 2015, at 10:06, Steve Bradshaw <sjb1970_at_gmail.com> wrote:
> 
> Hi,
> 
> Which traces? I have a 10053 and 10046 from yesterday morning.
> 
> My team lead has set the optimizer_features_enable parameter to 11.2.0.3 as a temporary fix as we've had a lot of complaints from users about performance.
> 
> With my optimizer set to 12.1.0.2, I've just tried setting _optimizer_ads_use_result_cache to false, and re-ran the query and it was still slow.
> 
> Steve
> 
> 

>> On Wed, Sep 23, 2015 at 3:57 PM, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:
>>
>> Competition for the result cache sounds viable - though 12 seconds seems a little extreme.
>> If the OP can't get at trace file easily, or query own v$session_event or V$active_session_history then testing 12c after executing
>>
>> alter session set "_optimizer_ads_use_result_cache" = FALSE;
>>
>> might be an indicator - it should disable the use of the result cache for dynamic stats activity.
>>
>>
>>
>> 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 Stefan Koehler [contact_at_soocs.de]
>> Sent: 23 September 2015 15:13
>> To: sjb1970_at_gmail.com
>> Cc: ORACLE-L
>> Subject: Re: query performance following 12c upgrade
>>
>> Hi Steve,
>> thanks for the requested data.
>>
>> The execution plan (especially the important predicate section) and the amount of work is identical - expect the ROWID BATCHED part, but there is an
>> important hint with OPTIMIZER_FEATURES_ENABLE = 12.1.0.2.
>>
>> --------------8<----------------
>> Note
>> -----
>> - dynamic statistics used: dynamic sampling (level=2)
>> - 1 Sql Plan Directive used for this statement
>> --------------8<----------------
>>
>> Is it possible that the query is suffered by latches? How long does the parse itself take? SPDs are based on the result cache and this can have nasty
>> side effects. You can verify this by running a SQL trace on the slow SQL as previously suggested.
>>
>> By the way here are some good references about the result cache and SPD:
>> - http://berxblog.blogspot.de/2015/06/sql-plan-directives-and-result-cache.html
>> - https://dban00b.wordpress.com/2015/04/21/311/
>>
>> Best Regards
>> Stefan Koehler
>>
>> Freelance Oracle performance consultant and researcher
>> Homepage: http://www.soocs.de
>> Twitter: _at_OracleSK
>>
>> > Steve Bradshaw <sjb1970_at_gmail.com> hat am 23. September 2015 um 15:53 geschrieben:
>> >
>> > Hi,
>> >
>> > Please see the attached. opti12 is the results when run with optimizer_featured_enable=12.1.0.2, and opti11 it is 11.2.0.3
>> >
>> > I've had to anonymyse the data/columns/tables etc so the formatting may be a little out.
>> >
>> > Thanks
>> > Steve
>> --
>> http://www.freelists.org/webpage/oracle-l
>

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

Received on Thu Sep 24 2015 - 16:28:32 CEST