Andy
Thanks for the sharing the details. I am still seeing
optimizer_adaptive_features parameter along with optimizer_adaptive_plans and
optimizer_adaptive_statistics and all defaults to TRUE.
So there is confusion as if I still also had to apply the Fixset as per the doc
or just Patch will take care. what I read as per doc and some of the issue I am
encountering
· use: alter system set
"_fix_control"='26664361:7','16732417:1','20243268:1' scope=both;Where
26664361:7 Has Value of 7 points to Patch 22652097 ( Value 1 and Splits the
parameter optimizer_adaptive_features into two) + Patch 21171382( Value 2 -
disables the automatic creation of extended statistics) and Patch 20243268(
Value 4) - Suboptimal execution plan for SQL that has cardinality mis-estimate
directives auto-added
Now parameter optimizer_adaptive_features is already split and so not sure if
need to use Fixset and some other fixes.
So the reason was checking if anyone using 12.1.0.2 and has applied the bundle
patch is also aplying the other suggest Oracle recommendation.
TIA Sanjay
On Monday, March 5, 2018, 10:55:33 AM EST, Andy Wattenhofer
<watt0012@xxxxxxx> wrote:
Just applying the patch will have no effect on performance. The patch
installation is designed so that the new optimizer parameters default to values
that match whatever you had set prior to the patch. It is confusing because
those default values will differ based on whether you had prior installed
patches for any of the bugs cited in the support doc. But for a base 12.1
installation with no one-off patches installed, you can boil it down to this:
- Before patch: optimizer_features_enable=TRUE (covers both adaptive plans
and adaptive statistics)
- After patch:
optimizer_adaptive_features parameter is removed
optimizer_adaptive_plans=TRUE
optimizer_adaptive_statistics= TRUE
Now you have the ability to control OAP and OAS separately, just as with
database 12.2.
My experience with some 12.1 databases is that OAS can cause some performance
problems. It is telling that the default value for that parameter in 12.2 is
FALSE. In 12.1, the common symptom I have seen is the one you named: many many
SQLs with dynamic sampling comments ("/* DS_SVC */") in them. And of course
users will be complaining about performance. By setting OAS=FALSE, the
performance problems are resolved and the number of DS_SVC calls drops.
So after you install the patch, you probably must "alter system set
optimizer_adaptive_statistics= FALSE ..."
Andy
On Sun, Mar 4, 2018 at 5:45 PM, Sanjay Mishra <dmarc-noreply@xxxxxxxxxxxxx>
wrote:
Hi Everyone
Need some insight from lots of experts using Oracle 12c (12.1.0.2) and
applying bundle and fixes as per Metalink Doc Recommendations for Adaptive
Features in Oracle Database 12c Release 1 (Adaptive Features, Adaptive
Statistics and 12c SQL Performance) (Doc ID 2312911.1)
Are we implementing the fixes as per the above docs or only if there are some
issue which sometime might not be easy to check when we are supporting hundred
of database and application might not be able to see difference. I personally
saw one issue in mutlple environment before patch where Dynamic sampling calls
are very high and been stopped with some underscore fixset setting without
using the patch and saw dramatic performance improvement.
So if we are putting all fixes as per metalink doc or just applying the patch
and not running any alter ssystem commands.
Any thought or suggestion
TIASanjay