RE: Need URGENT help in addressing the query performance degradation after upgrading the database to Oarcle 11gR2

Thanks,
Ashoke

From: Jithin Sarath [mailto:jithinsarath_at_gmail.com] Sent: Thursday, January 15, 2015 9:04 PM To: iggy_fernandez_at_hotmail.com
Cc: Mandal, Ashoke; oracle-l_at_freelists.org Subject: RE: Need URGENT help in addressing the query performance degradation after upgrading the database to Oarcle 11gR2

I agree with Iggy's comments, awr / statspack or even the free & awesome ashv will be of much help.

I'm curious, did you gather dictionary stats post upgrade?

Regards, Jithin Sarath
On Jan 15, 2015 9:48 PM, "Iggy Fernandez" <iggy_fernandez_at_hotmail.com<mailto:iggy_fernandez_at_hotmail.com>> wrote: OK; I'll bite. But first let me say why I believe that things go so wrong with migration projects. These are from my experience; they may not apply in your case.

• Not anticipating that performance could change for the worse
• Not performing regression and volume testing using automated test suites; not performing parallel runs
• Changing other things in conjunction with the workload: e.g. hardware, storage, network, application
• Not using Oracle features for plan stability such as stored outlines and optimizer_features_enable
• Not having pre and post AWR or Statspack data
• Not having pre and post RDA collections for comparison
• Not having pre and post execution plans
• Confusing EXPLAIN PLAN with actual plans obtained using DBMS_XPLAN.DISPLAY_CURSOR or DBMS_XPLAN.DISPLAY_AWR
• Does not migrate statistics and the statistics collection strategy
• Does not know which initialization parameters have changed and which have new defaults

Now coming to your problem. Here are the hurdles:

• Very limited information e.g. no "top wait events" from AWR or Statspack
• Assuming that the problem is plan changes (it could very well be) and asking us to discuss that angle only

BTW, the query plans that you compared look essentially the same to me. However, these are "EXPLAIN PLAN" (dbms_xplan.display) not real plans (dbms_xplan.display_cursor or dbms_xplan.display_AWR).

re: "multiple SQL queries are running 20 times slower after the upgrade. even generating explain plan of one of these queries takes 5 sec in the 11g version whereas the same explain plan generation takes only few (probably 50) milliseconds in 10g version of the database"

Could we start with the heading of a sample pre and post AWR or Statspack reports i.e. everything upto the top-5 wait events section.

I hope this helps.

Iggy

[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

--
http://www.freelists.org/webpage/oracle-l