Question
Charles Schwab
US
Last activity: 20 Mar 2018 6:41 EDT
Oracle 12C - ‘optimizer_adaptive_features’ Question.
We have just upgraded our Development database to Orace 12c. We are experiencing some performance issues. Oracle 12c has a new feature/parameter ‘optimizer_adaptive_features’, if set to TRUE this an extension to dynamic sampling and can dynamically refresh statistics and alter execution plans during SQL execution. The idea of this is to have the optimizer always choosing the best plan. The default in Oracle 12c is TRUE. What is the recommendation from pega on the value of this Flag. Also have there been any issues using this flag to TRUE in high volume/high use applications.
Feedback would be highly appreciated
JPMorgan Chase & Company
US
Hi,
What are the performance issue you are facing currently, as my understanding optimizer_adaptive_features won't cause these issues.
Charles Schwab
US
There was generally long response times. They have gone away now with some tuning. Do you know anything about the value optimizer_adaptive_features=true . Does it Cause any performance issues or not ..potentially ?
Pegasystems Inc.
US
You may want to make sure statistics is correct. You may also want to check the SQL' execution plans. Is the SQL sticks to the bad plan or not ? Is the bad plan indeed an adaptive plan (v$sql is_resolved_adaptive_plan)? If issue persists, you may contact Oracle support for any patches. Last resort is to set optimizer level back to Proprietary information hidden.