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
