SQL Plan Management with Oracle Database 12c Release 2 (White Paper)

Oracle White Paper
SQL Plan Management with Oracle Database 12c Release 2

Introduction

The performance of any database application heavily relies on consistent query execution. While the Oracle Optimizer is perfectly suited to evaluate the best possible plan without any user intervention, a SQL statement’s execution plan can change unexpectedly for a variety of reasons including regathering optimizer statistics, changes to the optimizer parameters or schema/metadata definitions.
The lack of a guarantee that a changed plan will always be better leads some customers to freeze their execution plans (using stored outlines) or lock their optimizer statistics. However, doing so prevents them from ever taking advantage of new optimizer functionality (like new access paths), that would result in improved plans. An ideal solution to this conundrum would preserve the current execution plans amidst environment changes, yet allow changes only for better plans.
SQL Plan Management (SPM) provides such a framework and allows for complete controlled plan evolution. With SPM the optimizer automatically manages execution plans and ensures that 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 perform better than the current plan.
This paper provides an in-depth explanation of how SPM works and why it should be a critical part of every DBAs toolkit. The paper is divided into three sections. The first section describes the fundamental aspects of SPM and how they work together to provide plan stability and controlled plan evolution. It then discusses how SPM interacts with other Oracle Database features that influence the optimizer plan selection. The final section provides a more step-by-step guide on how to use SPM to ensure consistent database and application performance during some of the more daunting tasks of a DBA, including upgrades.

http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-sql-plan-mgmt-12c-1963237.pdf