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
TIA
Sanjay
