SQL Plan Management (SPM)
Making executions plans more stable plus adaptable to changes (system, dictionary, data) as they occur is the reasoning behind the SPM components of 11g. It is a major improvement to the stored outlines that have been a part of Oracle Database since version 8i. While stored outlines provided stability for a fixed optimized execution plan, it didn't have the features for storing, comparing, and verifying multiple plans. Hints, which are added to the actual SQL, are intended to provide guidance to the query optimizer for picking a certain execution plan. Hints will also require ongoing maintenance to prevent query regression as data changes.
This is the default SPM behavior:
- SQL Plan Baseline Capture (Automatic)
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES ...
Get Oracle Database 11g—Underground Advice for Database Administrators now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
