Insights into Statistics, Query Optimization and the Oracle Optimizer
-
SQL Plan Management
October 25, 2011
Does the use of SQL Plan Management and the DBMS_SPM database package require a tuning or diagnostic pack license?
Recently during the Open World conference a lot of people asked me about what additional licenses are need to use SQL Plan Management. No additional licenses are needed to use SQL Plan Management (SPM) or any of the procedures in the DBMS_SPM package. SPM is available as part of Oracle Database Enterprise Edition 11g and 12c. It is also available in Oracle Database Standard Edition from Oracle Database 18c onwards.
I believe the confusion arises from the wording in the Oracle Database Licensing documentation that says the Tuning Pack includes "Automatic Plan Evolution of SQL Plan Management". What this is actually referring to is the interaction between the SQL Tune Advisor's nightly tuning task and SPM. This interaction does require you to have a license to use the Tuning pack.
In Oracle Database 11g, the SQL Tuning Advisor, runs automatically during the maintenance window. This automatic SQL tuning task targets high-load SQL statements. These statements are identified by the execution performance data collected in the Automatic Workload Repository (AWR) snapshots and not from SPM. If the SQL Tuning Advisor finds a better execution plan for a SQL statement it will recommend a SQL profile. Some of these high-load SQL statements may already have SQL plan baselines created for them. If a SQL profile recommendation is implemented, the execution plan found by the SQL Tuning Task will be added as an accepted plan to the existing SQL plan baseline.
The Tuning pack or RAT pack are also required if you want to load plans from a SQL Tuning Set (STS) into SPM. Again this is because an STS can only be created if you have licensed one of these packs. It has nothing to do with SPM.
Maria Colgan+
Thanks for the reminder about SPM
Recently during the Open World conference a lot of people asked me about what additional licenses are need to use SQL Plan Management. No additional licenses are needed to use SQL Plan Management (SPM) or any of the procedures in the DBMS_SPM package. SPM is available as part of Oracle Database Enterprise Edition 11g.
Customer Question (Bundesministerium der Finanzen)
Does the above also apply for Oracle database version 12c?
Thank you
https://blogs.oracle.com/optimizer/plan-stability-in-oracle-database-18c-standard-edition