Oracle Outlines
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose A set ofattributes used by the optimizer to generate an execution plan. The optimizer can be insructed to use a set of outlines to influence the generation of execution plans when a specific SQL statement is issued, regardless of changes in factors that might otherwise affect optimization. Outline, after creation, can be modified to take into account enviroment and data changes.

Stored outlines are deprecated as of 12cR1. They are still supported for backward compatibility. However, Oracle recommends the use of SQL plan management instead. SQL plan management creates SQL plan baselines, which offer superior SQL performance stability compared with stored outlines. Links to SQL plan management are at page bottom.
Dependencies
ALL_OUTLINES DBA_OUTLINES OUTLN_PKG
ALL_OUTLINE_HINTS DBA_OUTLINE_HINTS OUTLN_EDIT_PKG
CDB_OUTLINE KU$_OUTLINE_VIEW USER_OUTLINES
CDB_OUTLINE_HINTS OL$ USER_OUTLINE_HINTS
Initialization Parameters
OPTIMIZER_FEATURES_ENABLE QUERY_REWRITE_ENABLED STAR_TRANSFORMATION_ENABLED
System Privileges
ALTER ANY OUTLINE CREATE ANY OUTLINE DROP ANY OUTLINE
 
Notes
Cursor sharing and outlines If cursor_sharing = similar

When an outline is created, the stored SQL text is literally as supplied. When the statement is run, it is first rewritten to change any constants to things like ":SYS_B1" - so it no longer matches the text stored in the database.

To use stored outlines with cursor_sharing enabled; the stored outline must match the post-rewrite optimized text not the originally, literal, text.
If cursor_sharing = force

There are a number of articles that state that if cursor_sharing = force stored outlines do not work. This is just not true. Search for articles on the topic by Jonathan Lewis.
Schema and objects required for outlines conn / as sysdba

SELECT username, account_status
FROM dba_users
ORDER BY 1;

ALTER USER outln ACCOUNT unlock;

ALTER USER outln IDENTIFIED BY outln;

conn outln/outln

col object_name format a30

SELECT object_name, object_type
FROM user_objects;
Preparation Before creating a private outline, you must run the OUTLN_PKG.CREATE_EDIT_TABLES procedure to create the required outline tables and indexes in the schema.
 
CREATE
Public Outline (default) CREATE OR REPLACE [PUBLIC] OUTLINE <outline_name>
FOR CATEGORY <category_name>
ON <SQL statement>;
See Outline Demo Below
Private Outline CREATE OR REPLACE PRIVATE OUTLINE <outline_name>
FOR CATEGORY <category_name>
ON <SQL statement>;
conn uwclass/uwclass@pdbdev

exec dbms_outln_edit.create_edit_tables;

set linesize 121
col object_name format a30

SELECT object_name, object_type
FROM user_objects
ORDER BY 2;

ALTER SESSION SET create_stored_outlines=TRUE;

CREATE OR REPLACE PRIVATE OUTLINE
priv_ol_servers
FOR CATEGORY uwclass ON
SELECT srvr_id
FROM servers s
WHERE EXISTS (
   SELECT srvr_id
   FROM serv_inst i
   WHERE s.srvr_id = i.srvr_id);

ALTER SESSION SET create_stored_outlines=FALSE;

set long 100000

col name format a30
col category format a10
col version format a10

SELECT name, category, used, timestamp, version, enabled
FROM user_outlines;

col hint format a40

SELECT name, join_pos, hint
FROM user_outline_hints
WHERE name = 'SYS_OUTLINE_07051820554578101';
Make Existing Private Outline Public CREATE OR REPLACE PRIVATE OUTLINE <outline_name>
FROM <PUBLIC | PRIVATE> <source_outline>;
CREATE OR REPLACE OUTLINE pub_ol_servers
FROM PRIVATE priv_ol_servers;
 
ALTER
Change Category ALTER OUTLINE <PUBLIC | PRIVATE> <outline_name>
CHANGE CATEGORY TO <new_category_name>;
ALTER OUTLINE ol_demo
CHANGE CATEGORY TO new_cat;
Disable ALTER OUTLINE <PUBLIC | PRIVATE> <outline_name> DISABLE;
ALTER OUTLINE ol_demo DISABLE;
Enable ALTER OUTLINE <PUBLIC | PRIVATE> <outline_name> ENABLE;
ALTER OUTLINE ol_demo ENABLE;
Rebuild ALTER OUTLINE <PUBLIC | PRIVATE> <outline_name> REBUILD;
ALTER OUTLINE ol_demo REBUILD;
Rename ALTER OUTLINE <PUBLIC | PRIVATE> <outline_name> RENAME TO <new_name>;
ALTER OUTLINE ol_demo RENAME TO new_outln;
 
DROP
Drop an outline DROP OUTLINE <outline_name>;
See Outline Demo Below
 
Demo
Create, use and drop a Stored Outline conn sys@pdbdev as sysdba

GRANT alter system TO uwclass;

conn uwclass/uwclass@pdbdev

-- if server and serv_inst tables do not exist CLICK HERE for the build script or use the link at page bottom to SETUP to create the UWCLASS schema.

set linesize 121

-- gather current optimizer statisitics
exec dbms_stats.gather_table_stats(OWNNAME=>'UWCLASS', TABNAME=>'SERVERS', CASCADE=>TRUE);
exec dbms_stats.gather_table_stats(OWNNAME=>'UWCLASS', TABNAME=>'SERV_INST', CASCADE=>TRUE);

-- create a configuration favoring a hash join
ALTER SYSTEM SET optimizer_index_cost_adj = 10000 SCOPE=MEMORY;
ALTER SYSTEM SET optimizer_index_caching = 1 SCOPE=MEMORY;

set autotrace traceonly explain

SELECT DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;

-- create a configuration favoring a nested loop join
ALTER SYSTEM SET optimizer_index_cost_adj = 50 SCOPE=MEMORY;
ALTER SYSTEM SET optimizer_index_caching = 50 SCOPE=MEMORY;

SELECT DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;

-- which is the best? the nested loop has a lower cost so lets go with it.
-- but our default configuration is the one that will result in the less efficient hash join.
-- so lets keep the configuration that favors the nested loop join

-- begin outline creation

set autotrace off

ALTER SESSION SET create_stored_outlines = uw_outlines;
/* alternative
alter session set create_stored_outlines=TRUE;
*/

-- create an outline based on the better configuration

CREATE OR REPLACE OUTLINE ol_demo FOR category uw_outlines ON
SELECT DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;

-- stop outline creation
ALTER SESSION SET create_stored_outlines=FALSE;

-- view data dictionary info.
desc user_outlines

col name format a8
col category format a12

SELECT name, category, used, timestamp, signature, enabled, format
FROM user_outlines;

set long 100000

SELECT name, sql_text
FROM user_outlines;

desc user_outline_hints

col hint format a50

SELECT * FROM user_outline_hints;

-- change to the standard config that produces hash joins
ALTER SYSTEM SET optimizer_index_cost_adj = 10000 SCOPE=MEMORY;
ALTER SYSTEM SET optimizer_index_caching = 1 SCOPE=MEMORY;

-- test without invoking outline
set autotrace traceonly explain

SELECT DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;

-- tell Oracle to use the outline
ALTER SESSION SET use_stored_outlines=uw_outlines;

SELECT DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;

ALTER SESSION SET use_stored_outlines=FALSE;

set autotrace off

SELECT name, used
FROM user_outlines;

DROP OUTLINE ol_demo;

exec dbms_outln_edit.drop_edit_tables;

Related Topics
DBMS_ADVANCED_REWRITE
DBMS_OUTLN
DBMS_OUTLN_EDIT
DBMS_SPM (SQL Plan Management)
Hints
OUTLN_PKG
OUTLN_EDIT_PKG
Session
SETUP
System
System Triggers

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved