X

Insights into Statistics, Query Optimization and the Oracle Optimizer

Does the use of SQL Plan Management and the DBMS_SPM database package require a tuning or diagnostic pack license?

Maria Colgan
Master Product Manager

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+

Join the discussion

Comments ( 4 )
  • Foued Tuesday, March 1, 2016

    Thanks for the reminder about SPM


  • guest Monday, May 9, 2016

    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


  • Ian Lewis Monday, April 1, 2019
    Is SPM an Enterprise Edition only tool? Therefore it cannot be used with Standard Edition (SE, SE1 or SE2)?
  • Nigel Bayliss Monday, April 1, 2019
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.