Re: Oracle 12c R1 Adaptive Patch and workaround

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