last year we have upgrade all Oracle instances to Oracle 19.
We encounter still performance issue, sometimes timeouts, in our production system, so I would like to ask for your help.
I have already done a lot of research concerning the new Optimizer behavior as of Oracle 12, and I see that SQL Plan Directives can cause High CPU after 12c upgrade.
This is the Adaptive Query Optimization feature which is very complex if you want to fine tune the related database parameters.
I can give you the values of some parameters:
RESULT_CACHE_MODE = FORCE
_optimizer_ads_use_result_cache = FALSE
_OPTIMIZER_DSDIR_USAGE_CONTROL" => no value
_sql_plan_directive_mgmt_control" => no value
optimizer_adaptive_plans TRUE
optimizer_adaptive_statistics FALSE
For now we encounter from time to time, let’s say once per 2 months, timeouts occur during a timeframe in the morning, from 9:00 to 11:30, there is a lot of CPU / SGA memory usage and we can identify connection failures and timeouts.
As you can see, I am not an expert in Oracle performance tuning, so I don’t know if I gave you enough information to give some feedback and recommendations.
Please let me know if you need more information.
Thanks a lot in advance!
Johan