Baselines are the latest evolution in Oracle’s efforts to allow a plan to be locked. The previous efforts were Outlines and SQL Profiles. Both of those efforts were based on the idea that hints could be applied to limit the optimizers choices down to one. That approach seems a little flawed. Why not just save the plan and be done with it? I believe that’s what Baselines are intended to do. Unfortunately, they don’t appear to do it yet. But they do have an advantage over Outlines and SQL Profiles in that they at least save the plan_hash_value, so they know if they are reproducing the correct plan or not.
Oracle SQL plan management (SPM) relieves the problem of environmental changes causing thousands of SQL statements to change their explain plan steps.
By default, the parameters optimizer_capture_sql_plan_baselines is set to FALSE and optimizer_use_sql_plan_baselines is set to TRUE.
Oracle notes that when SQL plan management is enabled, only known and verified plans are used, and all plan changes are automatically verified. During execution plan verification, only "better" execution plans will be implemented.
Oracle SQL plan management also offers a new package called dbms_spm and a new DBA view dba_sql_plan_baseline to allow the DBA to manage their SQL plans.
dbms_spm.evolve_sql_plan_baseline - This SPM procedure tests a new execution plan against a "verified" plan to determine if the new plan has comparable (or better) execution performance.
dbms_spm.load_plans_from_cursor_cache - This SPM procedure will extract revised SQL explain plans directly from the library cache.
dbms_spm.load_plans_from_sqlset - This SPM procedure allow the DBA to take pre-tested execution plans from a SQL tuning Set (STS), after running a workload test, and load them for production use.
No comments:
Post a Comment