Oracle 12C peformance issue – waiting time increased drastically

oracle

Recently we upgraded oracled DB from 11G to 12C. During first week after upgrade we did not notice any performance issue. However, suddenly decrease in performance (not even gradual decrease) popped up. Here are the details observed. Request your guidance to debug the issue. Let me know if any details needed further

1. AWR report shows a sudden increase in wait time. Report was taken for 2 hours period

AWR -Foreground Wait Event

2. AWR before problem:

AWR -Foreground Wait Event

Best Answer

There are some known performance issues after upgrading to 12c that are related to the new optimizer features. Try the following to see if it corrects your issue. Note that this will turn off these features for now.

ALTER SYSTEM SET OPTIMIZER_ADAPTIVE_FEATURES=false scope=both;
ALTER SYSTEM SET OPTIMIZER_DYNAMIC_SAMPLING=0 scope=both;
ALTER SYSTEM SET "_fix_control"='12914055:Off' scope=both;
Related Question