Re: Oracle 12c R1 Adaptive Patch and workaround

From: Ludovico Caldara <ludovico.caldara_at_gmail.com>
Date: Mon, 5 Mar 2018 19:02:04 +0100
Message-ID: <CALSQGrJLxg498W7kfy9inB7gh98gveTNFBE5p=wFjnqy40WR9g_at_mail.gmail.com>



Hi,
if you read carefully the note, you will see that the patch installs the fix but does not activate it. That means that if you want to benefit from the two new parameters, you HAVE to set the _fix_control as suggested. If you skip this step, the parameter optimizer_adaptive_features will be used just like before applying the patch.

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-l
Received on Mon Mar 05 2018 - 19:02:04 CET

Original text of this message