Re: query performance following 12c upgrade

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

• 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