RE: Need URGENT help in addressing the query performance degradation after upgrading the database to Oarcle 11gR2
From: Iggy Fernandez <iggy_fernandez_at_hotmail.com>
Date: Thu, 15 Jan 2015 18:48:00 -0800
Message-ID: <BLU179-W17393275743905255BBCB4EB4F0_at_phx.gbl>
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 worseNot performing regression and volume testing using automated test suites; not performing parallel runsChanging other things in conjunction with the workload: e.g. hardware, storage, network, applicationNot using Oracle features for plan stability such as stored outlines and optimizer_features_enableNot having pre and post AWR or Statspack dataNot having pre and post RDA collections for comparisonNot having pre and post execution plansConfusing EXPLAIN PLAN with actual plans obtained using DBMS_XPLAN.DISPLAY_CURSOR or DBMS_XPLAN.DISPLAY_AWRDoes not migrate statistics and the statistics collection strategyDoes not know which initialization parameters have changed and which have new defaultsNow coming to your problem. Here are the hurdles:Very limited information e.g. no "top wait events" from AWR or StatspackAssuming that the problem is plan changes (it could very well be) and asking us to discuss that angle onlyBTW, 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
Date: Thu, 15 Jan 2015 18:48:00 -0800
Message-ID: <BLU179-W17393275743905255BBCB4EB4F0_at_phx.gbl>
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 worseNot performing regression and volume testing using automated test suites; not performing parallel runsChanging other things in conjunction with the workload: e.g. hardware, storage, network, applicationNot using Oracle features for plan stability such as stored outlines and optimizer_features_enableNot having pre and post AWR or Statspack dataNot having pre and post RDA collections for comparisonNot having pre and post execution plansConfusing EXPLAIN PLAN with actual plans obtained using DBMS_XPLAN.DISPLAY_CURSOR or DBMS_XPLAN.DISPLAY_AWRDoes not migrate statistics and the statistics collection strategyDoes not know which initialization parameters have changed and which have new defaultsNow coming to your problem. Here are the hurdles:Very limited information e.g. no "top wait events" from AWR or StatspackAssuming that the problem is plan changes (it could very well be) and asking us to discuss that angle onlyBTW, 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
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Jan 16 2015 - 03:48:00 CET