One of the critical aspects of any database upgrade is to ensure optimizer plan stability where tried and tested execution plans are not altered in any way by the Oracle optimizer after a database upgrade has taken place.
One of the 11g new features which is SQL Plan Management and SQL Plan baselines enables us to ‘lock in’ proven execution plans and ensure that the optimizer only chooses different execution plans to those which have been baselined if they are found to offer better performance as compared to existing plans.
In the example below, we will see how execution plans can potentially change after an upgrade to 11g and how by using SQL Plan Management feature of 11g, we can ensure that only existing 10g plans are still used even after the upgrade.
In the 10g database, the optimizer_mode parameter has been set to RULE and we see for this particular SQL statement,
Continue reading 11g Optimizer Plan Stability
Popular Posts