• Controlling Execution Plan Evolution Using SQL Plan Management for Database 11g Release 2

    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

Reviews (2)

  • 10.7 years ago
    Only Step 18 below MANAGING PLANS contains an error. It says "From the execution plan, you see the plan being used is the index scan instead of the full table scan so this is the second baseline.", but it is the FTS plan that is shown, which is correct by the way :).
    • 10.8 years ago