SQL Plan Baselines is a feature introduced with Oracle DB 11g that enables the system to automatically control SQL plan evolution. With this enabled, a newly generated SQL plan can integrate a SQL plan baseline only if it has been proven that doing so will not result in performance regression. During execution of a SQL statement, only a plan that is part of the corresponding SQL plan baseline can be used.
So this feature very useful when you upgrade your Oracle version as you can guarantee that no performance regression will ever happen.
What you need to practice here is:
- How to enable this feature.
- How to accept/enable/fix a plan.
- How to use DBMS_SPM and EM to manage it.
- Evolving a plan from history to baseline.
- Querying the dictionary views (specially dba_sql_plan_baselines).
- Display the plan using DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE.
- Using SQL Tuning and SQL Tuning Sets with Baseline Plans
- Configure Plan retention and space in SYSAUX that it’s allowed to use.
- Configuring the Automatic SPM Evolve Advisor Task
Enterprise Manager is the best for that. Doing everything described here in SQL*Plus mode is hard, but not impossible. So use EM whenever possible for this topic.
Path to Documentation:
SQL Tuning Guide -> 23 Managing SQL Plan Baselines
Click here to go back to the Main OCM 12c Preparation page.
Have you enjoyed? Please leave a comment or give a 👍!
