Can I use Stored Outlines with Standard Edition?

YES since 10g.

This could seem a simple answer, but that’s not the real thing as this is a poorly and confusedly documented Oracle functionality.

 

Plan Stability vs SQL Plan Management

There are two concepts to keep in mind related to Stored Outlines. Plan Stability (pre 11g) and SQL Plan Management or SPM (>= 11g). Stored Outlines are a component of Plan Stability, as we can check in Oracle’s official documentation as linked by Charle’s Hooper in his blog. Plan Stability is, in 8i and 9i a feature explicitly documented as Enterprise Edition only, and is not suitable for its use with Standard Edition.

SPM is Plan Stability evolution, but does not substitutes it. It just offers new components, like SQL Baselines, for improving the stability of specific execution plans for our queries. So, 11g onwards, we can select between using PS or SPM.

Now, in a 12c database where we can choose SP or SPM, we find in an Standard Edition database, an important difference between both components:

select parameter, value
from v$option
where parameter in ('Plan Stability','SQL Plan Management')

PARAMETER	     VALUE
-------------------- -----
Plan Stability	     TRUE
SQL Plan Management  FALSE

Plan Stability is enabled, but this is not the case with SPM. We know by means of reviewing Oracle’s licensing information that SPM is only EE available, and in a logical way it comes disabled in our SE. Most of EE funcionalities are not enabled in a SE like Spatial, Partitioning, Parallel Execution or Basic Compression. Even so, we know that we can find some surprises related to Oracle’s features available (by default or by changind DB parameters) when they should not be, like when using Diagnostics and Tuning EM packs, or even the access to AWR tables or views even with no EM package configured.

 

Where’s the confirmation regarding use of Stored Outlines with SE?

I did not find any official document where this is explicitly written for 10g version onward. We can justify this usage by other means:

  • 8i and 9i include Plan Stability (Stored Outlines) as specific EE features (not available with SE). This mention disappears in 10g documentation.
  • When SPM is introduced in 11g, Oracle’s documentation presents it as a EE feature not available with SE, just like PS in 8i and 9i. Again, no mention to Stored Outlines as EE specific. SPM substitutes Stored Outlines and these become deprecated.
  • At least 10g onwards, SE includes Plan Stability as an default enabled feature. A new database will always have the option to use them.
  • As an Oracle partner, I raised a question to our sales contact. The question got scaled internally and the answer was just YES, we can use Stored Outlines in SE.

These are the reasons to conclude Stored Outlines can be used in SE.Anyway, please, contact your sales representative and confirm with her / him if this is not as clear for you.

 

But Stored Outlines are deprecated, why should I use them?

This is ture, 11g was the first Oracle Database version where Stored Outlines were considered a deprecated feature and the new SPM components substituted them. So there’s no new development for this componente, but 11g and 12c still support them,and MOS note talk about them as “legacy technique for providing plan stability”.

So, if your ecosystem is Standard Edition, you’ll soon find there are not many options to force an execution plan, so this feature becomes luxury when you need to solve a tuning issue with SE, even in 12c. With the exception of hints, which need code changes, we don’t hace any other tool to guarantee an execution plan for a SQL.

 

Stored Outline creation sample

This is a sample in a 12.1.0.1 database where, for non identified reasons (several solutions where tried, like PSU application, specific interim patch for a support identified bug, and disabling several optimizer features again with the official support of Oracle through a SR), sometimes a query which uses to take less than 5 seconds to execute, changes its plan and takes minutes to complete. After no success with other options, we finally choosed to fix a specific plan which was valid for all the monitored executions with independency of the value of the bind variables.

The SQL we are chasing has sqlid=81uujczagk9bf. Cursor for this query is still inside the shared pool, where we take this information:

select sql_id, hash_value, child_number, plan_hash_value, loaded_versions, open_versions, users_opening, users_executing, fetches, executions, loads, parse_calls, invalidations, first_load_time
from v$sql
where sql_id='81uujczagk9bf'  2    3  ;

SQL_ID	      HASH_VALUE CHILD_NUMBER PLAN_HASH_VALUE LOADED_VERSIONS OPEN_VERSIONS USERS_OPENING USERS_EXECUTING    FETCHES EXECUTIONS      LOADS PARSE_CALLS INVALIDATIONS FIRST_LOAD_TIME
------------- ---------- ------------ --------------- --------------- ------------- ------------- --------------- ---------- ---------- ---------- ----------- ------------- ------------------------------
81uujczagk9bf 3573097838	    0	    317020229		    1		  0		0		0	   5	      1 	 5	     1		   2 2017-07-06/10:20:13
81uujczagk9bf 3573097838	    1	   3438832455		    1		  0		0		0	 166	     37 	 5	    33		   3 2017-07-06/10:20:13

We can see there are two different childs with different execution plans. After monitoring query behaviour, we are sure the second plan, with 37 executions, is the valid one. When issue reproduces, this plan (or a valid similar one) is invalidated and the wrong plan substitutes it. To avoid this, we create the following stored outline to force the sql plan with plan_hash_value 3438832455,which is child number one of cursor 3573097838.

begin
 	dbms_outln.create_outline(
		hash_value => '3573097838',
		child_number => 1,
		category => 'default'
	);
end;
/

We have just invoked procedure dbms_outln.create_online, specifying the SQL we want to use (with its hash_value and not its sql_id), child cursor number, and the category, using the default one as there’s no special usage for us at this time for this property. We see now the object in the DB:

column name format a30
column owner format a15
column category format a10
column version format a10
select name, owner, category, used, enabled, version
from dba_outlines
/

NAME			       OWNER	       CATEGORY   USED	 ENABLED  VERSION
------------------------------ --------------- ---------- ------ -------- ----------
SYS_OUTLINE_17071016074710901  ARUMEL	       default	  UNUSED ENABLED  12.1.0.1.0

Because we used dbms_outl.create_online it’s not possible to specify a name for the stored outline, and we get a system generated one with format SYS_OUTLINE_timestamp. Now we just modify the name for it to be more descriptive:

alter outline SYS_OUTLINE_17071016074710901 rename to fixacion_plan_batch_arumel;

Outline altered.


column name format a30
column owner format a15
column category format a10
column version format a10
select name, owner, category, used, enabled, version
from dba_outlines
/

NAME			       OWNER	       CATEGORY   USED	 ENABLED  VERSION
------------------------------ --------------- ---------- ------ -------- ----------
FIXACION_PLAN_BATCH_ARUMEL     MUT_WEB	       default	  UNUSED ENABLED  12.1.0.1.0