Re: query performance following 12c upgrade

From: Mark Burgess <mark_at_burgess-consulting.com.au>
Date: Thu, 24 Sep 2015 20:26:19 +1000
Message-Id: <A8769B16-0A2D-49B8-8F42-5F367CF4D494_at_burgess-consulting.com.au>

Sorry - I just realised I had a typo in there:

> optimizer_adpative_features=FALSE

Should be:

optimizer_adaptive_features=false

..

Depending on how far you want to go with this you could consider:

• flushing the shared pool
• purging any SQL plan directives related to these objects if they exist.

It may be worth purging the server result cache - previously tracing some of the SQL’s we had problems with did show the dynamic statistics SQL’s using the RESULT_CACHE hint (or one similar - I don’t have access to the trace files at the moment) - just issue a DBMS_RESULT_CACHE.FLUSH. Or alternatively bounce the instance.

Also - are you current on the PSU releases?

Regards,

Mark

> On 24 Sep 2015, at 7:09 pm, Steve Bradshaw <sjb1970_at_gmail.com> wrote:
> 
> Hi Mark,
> 
> Thanks for the reply.
> 
> I've tried setting those in my session, and the query runs as it did previously.
> 
> I'll look into setting these at the instance level, rather than changing the optimizer_features_enable parameter as we currently have.
> 
> Thanks again,
> 
> Steve
> 
> On Thu, Sep 24, 2015 at 1:44 AM, Mark Burgess <mark_at_burgess-consulting.com.au <mailto:mark_at_burgess-consulting.com.au>> wrote:
> Take a look at the optimizer_adaptive_features and optimizer_dynamic_sampling parameters to limit this type of behaviour in 12.1.0.2. It may be an option to consider if your SQL workload is fairly consistent and non-repetitive - ie more “OLTP” in nature.
> 
> We have one customer running with the following values in production for their “OLTP” databases and the behaviour has been good to date:
> 
> optimizer_adpative_features=FALSE
> optimizer_dynamic_sampling=0
> 
> If possible you could implement these settings via ‘alter session’ trigger so you don’t need to set them instance wide.
> 
> Regards,
> 
> Mark
> 
> 
> 

>> On 24 Sep 2015, at 1:23 am, Xiang Rao <xiang_rao_at_hotmail.com <mailto:xiang_rao_at_hotmail.com>> wrote:
>>
>> We experienced some performance degradations on some databases requiring high traffic and low latency after upgraded to 12c. It turned out the cause was 12c adaptive plans, which spent some time to collect statistics and trying different routes. In addition to the cost of statistics collection and trying different plans, the final plans on some queries were not good, too. The option "ADAPTIVE" with dbms_xplan.display_cursor will show the additional steps used by adaptive plans.
>>
>> > From: jonathan_at_jlcomp.demon.co.uk <mailto:jonathan_at_jlcomp.demon.co.uk>
>> > To: contact_at_soocs.de <mailto:contact_at_soocs.de>; sjb1970_at_gmail.com <mailto:sjb1970_at_gmail.com>
>> > CC: oracle-l_at_freelists.org <mailto:oracle-l_at_freelists.org>
>> > Subject: RE: query performance following 12c upgrade
>> > Date: Wed, 23 Sep 2015 14:57:03 +0000
>> >
>> >
>> > 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 <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 Stefan Koehler [contact_at_soocs.de <mailto:contact_at_soocs.de>]
>> > Sent: 23 September 2015 15:13
>> > To: sjb1970_at_gmail.com <mailto: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 <http://berxblog.blogspot.de/2015/06/sql-plan-directives-and-result-cache.html>
>> > - https://dban00b.wordpress.com/2015/04/21/311/ <https://dban00b.wordpress.com/2015/04/21/311/>
>> >
>> > Best Regards
>> > Stefan Koehler
>> >
>> > Freelance Oracle performance consultant and researcher
>> > Homepage: http://www.soocs.de <http://www.soocs.de/>
>> > Twitter: _at_OracleSK
>> >
>> > > Steve Bradshaw <sjb1970_at_gmail.com <mailto: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>
>> >
>> >
>> > --
>> > http://www.freelists.org/webpage/oracle-l <http://www.freelists.org/webpage/oracle-l>
>> >
>> >

>
>

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

Received on Thu Sep 24 2015 - 12:26:19 CEST