RE: DB performance after upgrade from 9i to 11gR2
From: Iggy Fernandez <iggy_fernandez_at_hotmail.com>
Date: Thu, 8 May 2014 08:53:06 -0700
Message-ID: <BLU179-W88E50881D32C02D3B71B78EB490_at_phx.gbl>
In the absence of data, we are being forced to make blind guesses. The conclusion that the root cause is that query plans have changed is being forced on us but it is not supported by data that has been presented. Yes, of course, the plans can be different in the production and QA environments (for several reasons), but we need data that proves that the performance problem is really being caused by changes to plans. Do you have representative Statspack reports for the production 9i environment and either Statspack or AWR reports for both the QA and production 9i environments and would you be willing to share them? You could upload them to a filesharing site and post links here. If not, we can only continue to make blind guesses. A comparison of the the three Statspack and/or AWR reports will help us narrow down the possible causes for the performance problems. But certainly, if you are convinced (through a study of the Statspack and/or AWR reports) that the root cause is the changes to execution plans, then solutions can be constructed (e.g. statistics collection methods, optimizer_features_enabled, SQL Plan Management, etc)
Date: Thu, 8 May 2014 08:53:06 -0700
Message-ID: <BLU179-W88E50881D32C02D3B71B78EB490_at_phx.gbl>
In the absence of data, we are being forced to make blind guesses. The conclusion that the root cause is that query plans have changed is being forced on us but it is not supported by data that has been presented. Yes, of course, the plans can be different in the production and QA environments (for several reasons), but we need data that proves that the performance problem is really being caused by changes to plans. Do you have representative Statspack reports for the production 9i environment and either Statspack or AWR reports for both the QA and production 9i environments and would you be willing to share them? You could upload them to a filesharing site and post links here. If not, we can only continue to make blind guesses. A comparison of the the three Statspack and/or AWR reports will help us narrow down the possible causes for the performance problems. But certainly, if you are convinced (through a study of the Statspack and/or AWR reports) that the root cause is the changes to execution plans, then solutions can be constructed (e.g. statistics collection methods, optimizer_features_enabled, SQL Plan Management, etc)
re: Did anyone come across similar problem in the past?
Yes, of course. Performance degradation after a database upgrade is an extremely common problem. The difficulty is usually that the problem is not anticipated and therefore diagnostic data is not collected (e.g. Statspack and AWR reports), the testing is inadequate (because the problem was not anticipated), defensive techniques are not used (e.g. init.ora parameters), and no mitigations are prepared in advance (e.g. stored outlines).
Kindest regards,
Iggy
-- http://www.freelists.org/webpage/oracle-lReceived on Thu May 08 2014 - 17:53:06 CEST