Re: Oracle 12c R1 Adaptive Patch and workaround
Date: Mon, 5 Mar 2018 19:02:04 +0100
Message-ID: <CALSQGrJLxg498W7kfy9inB7gh98gveTNFBE5p=wFjnqy40WR9g_at_mail.gmail.com>
So yes, set the _fix_control and the other two new parameters *explicitly* (read here why: http://www.ludovicocaldara.net/dba/bp-and-patch-22652097/), bounce your instance(s) and test carefully :-)
HTH 2018-03-05 18:53 GMT+01:00 Sanjay Mishra <dmarc-noreply_at_freelists.org>:
> 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_at_umn.edu> 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_at_freelists.org
> > 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
>
> TIA
> Sanjay
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Mar 05 2018 - 19:02:04 CET