0

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

migrated from stackoverflow.com Mar 29 '17 at 11:15

This question came from our site for professional and enthusiast programmers.

  • We had similar problems, of which not few were created by Oracle 12c bugs, especially QUERY_REWRITE errors in our data warehouse. Do you have the latest patchsets installed? This link was helpful, also the linked pages on the bottom of the page. – Lenniey Mar 29 '17 at 11:23
  • 1
    According to the AWR report, latch free wait event is taking most of the DB time. In order to get the problematic latch use the queries given here and edit your question to add the result. Or go to the Latch Statistics section of AWR report to get the details. – JSapkota Mar 30 '17 at 4:12
  • Thanks for your suggestions Lenniey and JSapkota. @Lenniey, Yes latest patches have been installed. Let me go through the links that you guys provided. – Naveen Kumar Mar 31 '17 at 3:27
1

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.