Re: Database perfortmance degraded drastically after upgrading the database from Oracle 102.0.4 to Oracle 11.2.0.3

  • From: Mladen Gogala <gogala.mladen@xxxxxxxxx>
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Mon, 19 Oct 2015 23:13:55 -0400

Dominic, I doubt that it is "database wide". I have done many problematic upgrades as a DBA and it has always been a small portion of queries. After all, what does the phrase "database wide performance degradation" really mean? Are the queries like "select sysdate from dual" much slower? If not that query, how many? Is it possible to quantify? I am a great believer in starting from the application side, not AWR reports.
As a DBA, I have received my fair share of calls from people telling me that "the database is slow today". Such claims used to remind me of Obi van Kenobi's characterizations that something is wrong with the Force today. What those complaints really meant is that the application used by those people was slow for some reason. And the right approach, just as advocated by Cary Millsap, is still tracing, not AWR reports.
Upgrades are projects and should be treated as such. The rule is: test, test and then test again. Personally, I don't test often, but when I do, I do it in production.
Regards

On 10/19/2015 3:40 PM, Dominic Brooks wrote:

If it's database wide, check AWR reports.
Look out in particular for adaptive direct path reads.

Sent from my Windows Phone
------------------------------------------------------------------------
From: Mandal, Ashoke <mailto:ashoke.k.mandal@xxxxxxxxxxxxx>
Sent: ‎19/‎10/‎2015 20:23
To: oracle-l@xxxxxxxxxxxxx <mailto:oracle-l@xxxxxxxxxxxxx>
Cc: oracle-l-bounce@xxxxxxxxxxxxx <mailto:oracle-l-bounce@xxxxxxxxxxxxx>
Subject: Database perfortmance degraded drastically after upgrading the database from Oracle 102.0.4 to Oracle 11.2.0.3

Greetings All,

After the upgrade of one of my databases from Oracle 10.2.0.4 to Oracle 11.2.0.3 the users are experiencing poor query performance. The response time of the queries have increased by 15 times.

I have tried the following without any luck but checking if you have any other suggestions

1.Changed optimizer_dynamic_sampling from 2 to 1 => No performance improvement

2.Changed optimizer_features_enable to '10.2.0.4' => No performance improvement

3.Try setting cursor_sharing to force => No performance improvement

4.Set “_optimizer_skip_scan_enabled" = FALSE => No performance improvement

Please let me know if you any suggestions to resolve this issue

Appreciate your help in advance,
Ashoke


[CONFIDENTIALITY AND PRIVACY NOTICE] Information transmitted by this email is proprietary to Medtronic and is intended for use only by the individual or entity to which it is addressed, and may contain information that is private, privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please delete this mail from your records. To view this notice in other languages you can either select the following link or manually copy and paste the link into the address bar of a web browser: http://emaildisclaimer.medtronic.com

Other related posts: