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

This sounds remarkably similar to a question asked recently in another forum, where the Original Poster had (eventually) admitted to reducing SGA_MAX_SIZE from 6GB to 1GB. Something like that probably would explain the problems you are seeing -- if the database is so starved for memory that it cannot effectively cache the data dictionary, it would explain the drastic increase in compile time.

AWR reports -- particularly Top-5-Waits -- for pre- and post-upgrade would indeed be very helpful. Even better, show us the complete "first page" of each report.

On Thu, Jan 15, 2015 at 10:11 PM, Mandal, Ashoke < ashoke.k.mandal_at_medtronic.com> wrote:

> Hi Jithin, I gathered the dictionary after the upgrade to 11g.
>
>
>
> 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>
> 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