Hi Dominic,
Comments in-line:
On 10/20/2015 02:20 AM, Dominic Brooks wrote:
A bit of a pedantic comment particularly about dual queries.
My point about system-wide is that if it is multiple things across the system reporting problems then start with a system-wide view such as AWR.
If you don't find them useful then I think you're in the minority but that's ok.
Of course I'm not going to question the value of tracing for specific sql statements. Nor giving specific evidence when describing a problem.
Even if a problem is not isolated to a small number of statements, starting to investigate a specific problem via trace will often lead you to a cause with a wider impact.
But on upgrade it is definitely worth keeping AWR from before for quick high level comparisons.
If no specific information is provided about a problem by a poster then you're going to tend to get a few "stab in the dark" answers.
For direct path reads, it's worth it. It's one of the most common cause of problems on this upgrade path.
And I've seen significant volumes of concurrent direct path reads cause "system-wide" performance problems across quite a few systems, largely due to degradation of all IO service times.
Regards
Dominic
Sent from my Windows Phone
------------------------------------------------------------------------
From: Mladen Gogala <mailto:gogala.mladen@xxxxxxxxx>
Sent: 20/10/2015 04:15
To: oracle-l@xxxxxxxxxxxxx <mailto:oracle-l@xxxxxxxxxxxxx>
Subject: Re: Database perfortmance degraded drastically after upgrading the database from Oracle 102.0.4 to Oracle 11.2.0.3
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
