Stored Outlines (Plan Stability) are an Enterprise Edition Feature? A Self-Conflicting Story

2 12 2011

December 2, 2011

An innocent question was asked in an OTN thread regarding the availability of plan stability options in the Standard Edition of Oracle Database.  If we check the documentation for the latest release (11.2.0.x) of Oracle Database, we will find the following statement:

“The use of stored outlines is deprecated in Oracle Database 11g Release 1 (11.1). Instead, you should use the SQL plan management feature that enables the optimizer to maintain a history of execution plans for a SQL statement.”

OK, so stored outlines are deprecated, even though stored outlines continue to work.  The documentation suggests using SQL Plan Management as a replacement for stored outlines.  That seems somewhat logical, because the Oracle Database documentation for 9.2 includes in its available feature list for the various Oracle Editions:

Plan Stability:

  • Standard Edition: Not available
  • Enterprise Edition: Available
  • Personal Edition: Available

“Allows execution plans for SQL to be stored so that the plan remains consistent throughout schema changes, database reorganizations, and data volume changes.”

The same documentation also suggest checking the V$OPTION view to see which options are enabled for the particular Edition of Oracle Database that is installed.

Based on the information found in the documentation for the older Oracle Database version, using Outlines (Plan Stability) requires the Enterprise Edition of Oracle Database, so it seems as though the quote from the first documentation link offers a good suggestion to use the Enterprise Edition feature of SQL Plan Management in place of stored outlines.  A couple of people have pointed out on this blog various documentation errors, so let’s check with Oracle support (that was a good suggestion offered by one of the responders in the OTN thread).

An easy to find article in My Oracle Support is Metalink (MOS) Doc ID 100911.1, “V$OPTION Fixed Table and Support Releases and Options”.  That document states that an Enterprise Edition license is needed to use stored outlines.  The document mentions Oracle Database 8i, if I recall correctly.

A quick check of the book “Performance Tuning Recipes” finds on page 412 an indication that stored outlines are only supported on the Enterprise Edition of Oracle Database.

Based on the above, the situation does not appear to be too positive for the OP in the OTN thread.  Maybe we should spend some significant time digging through My Oracle Support.  If we are lucky, we might stumble across the following two articles that offer a different opinion:

If we continue searching, we will find an indication in the “Troubleshooting Oracle Performance” book on page 248 a statement that the Standard Edition is sufficient for the use of stored outlines.

We might even take the suggestion of the Oracle Database documentation and check the V$OPTION view, after confirming that we are connected to a Standard Edition 11.2.0.2 database:

SELECT
  *
FROM
  V$VERSION;

BANNER
---------------------------------------------------------
Oracle Database 11g Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SELECT
  PARAMETER,
  VALUE
FROM
  V$OPTION
ORDER BY
  DECODE(PARAMETER,'Plan Stability','1',PARAMETER);

PARAMETER                          VALUE
---------------------------------- -----
Plan Stability                     TRUE    <---------
Active Data Guard                  FALSE
Advanced Compression               FALSE
Advanced replication               FALSE
Application Role                   FALSE
Automatic Storage Management       FALSE
Backup Encryption                  FALSE
Basic Compression                  FALSE
Bit-mapped indexes                 FALSE
Block Change Tracking              FALSE
Block Media Recovery               FALSE
Change Data Capture                FALSE
Coalesce Index                     TRUE
Connection multiplexing            TRUE
Connection pooling                 TRUE
DICOM                              TRUE
Data Mining                        FALSE
Database queuing                   TRUE
Database resource manager          FALSE
Deferred Segment Creation          FALSE
Duplexed backups                   FALSE
Enterprise User Security           FALSE
Export transportable tablespaces   FALSE
Fast-Start Fault Recovery          FALSE
File Mapping                       FALSE
Fine-grained Auditing              FALSE
Fine-grained access control        FALSE
Flashback Data Archive             FALSE
Flashback Database                 FALSE
Flashback Table                    FALSE
Incremental backup and recovery    TRUE
Instead-of triggers                TRUE
Java                               TRUE
Join index                         FALSE
Managed Standby                    FALSE
Materialized view rewrite          FALSE
OLAP                               FALSE
OLAP Window Functions              TRUE
Objects                            TRUE
Online Index Build                 FALSE
Online Redefinition                FALSE
Oracle Data Guard                  FALSE
Oracle Database Vault              FALSE
Oracle Label Security              FALSE
Parallel backup and recovery       FALSE
Parallel execution                 FALSE
Parallel load                      TRUE
Partitioning                       FALSE
Point-in-time tablespace recovery  FALSE
Proxy authentication/authorization TRUE
Real Application Clusters          FALSE
Real Application Testing           FALSE
Result Cache                       FALSE
SQL Plan Management                FALSE
Sample Scan                        TRUE
SecureFiles Encryption             FALSE
Server Flash Cache                 FALSE
Spatial                            FALSE
Streams Capture                    FALSE
Transparent Application Failover   TRUE
Transparent Data Encryption        FALSE
Trial Recovery                     FALSE
Unused Block Compression           FALSE
XStream                            TRUE 

The above shows that Plan Stability is enabled for the Standard Edition, and thus usable with Standard Edition.  The innocent OTN question has thus led to a lot of effort to demonstrate that not only can the Oracle Database documentation be self-conflicting, but so can the My Oracle Support site.

It is interesting to note that AWR collection and the related features in Enterprise Manager are enabled by default in the Standard Edition of Oracle Database 10.1.0.x and 10.2.0.x.  However, just because these AWR related features are enabled by default does not mean that the features may be legally used in the Standard Edition of Oracle Database (or the Enterprise Edition without the additional cost Diagnostics Pack license).  I recall discussions in a couple of OTN threads where posters claimed that AWR related features were “free” with the Standard Edition, because those features were enabled by default – sorry, it does not work that way (if I recall correctly, partitioning is enabled by default in the Enterprise Edition, even though it is an additional cost option). 

In the OTN thread Pierre Forstmann offered the following helpful demonstration test case, which checks another item found in the V$OPTION view that is set to FALSE for the Standard Edition:

SQL> alter index emp_job_ix rebuild online;
alter index emp_job_ix rebuild online
*
ERROR at line 1:
ORA-00439: feature not enabled: Online Index Build 

As such, if stored outlines were not available on the Standard Edition of Oracle Database (just as OLAP Window Functions were not in 8.1.7.4 and Online Index Build are not in 11.2.0.2), using those features should result in an ORA-00439.

A couple of helpful articles that are related to stored outlines (most written by OakTable Network members):
http://oracle-randolf.blogspot.com/2009/03/plan-stability-in-10g-using-existing.html
http://kerryosborne.oracle-guy.com/2008/12/oracle-outlines-aka-plan-stability/
http://www.oracle-base.com/articles/misc/Outlines.php
http://www.jlcomp.demon.co.uk/outline_hack.html
http://jonathanlewis.wordpress.com/2010/03/11/dropping-outln/
http://www.oracle.com/technetwork/database/focus-areas/bi-datawarehousing/twp-upgrading-10g-to-11g-what-to-ex-133707.pdf

—

Any other good self-conflicting stories related to Oracle Database?

« »


Actions

Information

Leave a comment