Re: Oracle 12c R1 Adaptive Patch and workaround
Date: Mon, 5 Mar 2018 17:53:11 +0000 (UTC)
Message-ID: <2098729185.8479187.1520272391854_at_mail.yahoo.com>
Andy
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:
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
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
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
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Mar 05 2018 - 18:53:11 CET