This tutorial describes how you can manage your SQL execution plans in Oracle Database 11g.
Potential performance risks can occur when the execution plan changes for a SQL statement. A plan change can occur due for a variety of reasons including but not limited to the following types of changes occurring in the system: optimizer version, optimizer statistics, optimizer parameters, schema/metadata definitions, system settings, as well as SQL profile creation. Sometimes a change in the system (e.g. drop an index) causes an irreversible plan change. Not being able to guarantee a plan will change for the good has caused some customers to freeze their execution plans or their optimizer statistics.
In Oracle Database 11g a new feature SQL Plan Management (SPM) is introduced, which provides controlled execution plan evolution. With SPM, the optimizer automatically manages execution plans and ensures only known or verified plans are used. When a new plan is found for a SQL statement it will not be used until it has been verified to have comparable or better performance than the current plan.
Duration: 30 minutes
Release Date: 01-SEP-2009
Release Date: 13-NOV-09
Release Date: 15-AUG-10