Re: query performance following 12c upgrade

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