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@xxxxxxxxxxxxxxxxxx
To: contact@xxxxxxxx; sjb1970@xxxxxxxxx
CC: oracle-l@xxxxxxxxxxxxx
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
@jloracle
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Stefan Koehler [contact@xxxxxxxx]
Sent: 23 September 2015 15:13
To: sjb1970@xxxxxxxxx
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: @OracleSK
Steve Bradshaw <sjb1970@xxxxxxxxx> 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
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
