Adaptive plans in EBS database performance

[satish]

Hi erman,

We are on R12.2.5 and 19.17 database versions

Do you recommend to disable adaptive plans fearure in above versions?

optimizer_adaptive_plans

SQL> sho parameter adaptive;

NAME TYPE VALUE

optimizer_adaptive_plans boolean TRUE optimizer_adaptive_reporting_only boolean FALSE optimizer_adaptive_statistics boolean FALSE parallel_adaptive_multi_user boolean FALSE

can we disable optimizer_adaptive_plans for better performance.


Thanks,
SG

[ErmanArslansOracleBlog]

You need to check the initialization parameters for EBS.. Ref Doc: Database Initialization Parameters for Oracle E-Business Suite Release 12 (Doc ID 396009.1)

If it is instructed to be set to FALSE ( with the #MP comment), you must not set it to TRUE.. I hope you understand what I mean.. MP means Mandatory Parameter.

For instance for EBS DB 12c1 ->

#########
#
# Optimizer parameters.
#
# Release 12 uses cost based optimization. The following optimizer
# parameters must be set as shown, and should not be changed.
# It is recommended to disable the adaptive optimizer features: adaptive plans,
# automatic re-optimization, and SQL plan directives.
#
# For Windows platform, 12.1.0.2.170831 (Aug 2017) Bundle Patch or later is required.
# For other platforms, 12.1.0.2.171017 (Oct 2017) Bundle Patch or later is required.
#
# Remove optimizer_adaptive_features from pfile or spfile, and replace it with
# optimizer_adaptive_plans=FALSE #MP and optimizer_adaptive_statistics=FALSE #MP
# For more details, refer to MOS Doc ID 1594274.1.
#
#########

optimizer_adaptive_features = FALSE #MP

[ErmanArslansOracleBlog]

However, for 19C here it is ->

#
# Optimizer parameters.
#
# In Oracle Database 12.2 or higher, the Parameter optimizer_adaptive_features has been obsoleted,
# replaced and controlled by two new parameters, optimizer_adaptive_plans, which defaults to TRUE and
# optimizer_adaptive_statistics, which defaults to FALSE. For more details Refer MOS DOC ID 2031605.1.
#
##########

optimizer_adaptive_plans = TRUE #MP
optimizer_adaptive_statistics = FALSE #MP