SQL Plan Management is a new feature introduced with Oracle DB 11g that enables the system to automatically control SQL plan evolution by maintaining SQL plan baselines. 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 is new and 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.
Enterprise Manager is the best for that. Doing everything described here in CLI mode is hard, but not impossible. So use GUI whenever possible for this topic.
Path to Documentation:
Performance Tuning Guide -> 15 Using SQL Plan Management
Click here to go back to the Main OCM 11g Preparation page.
Have you enjoyed? Please leave a comment or give a 👍!
