Oracle Outlines and Stored Outlines

Oracle Outlines Version 11.2.0.3

General Information

Noterow

Data Dictionary Objects

ALL_OUTLINES	KU$_OUTLINE_VIEW	OUTLN_EDIT_PKG
ALL_OUTLINE_HINTS	OL$	USER_OUTLINES
DBA_OUTLINES	OUTLN_PKG	USER_OUTLINE_HINTS
DBA_OUTLINE_HINTS

Initialization parameters

OPTIMIZER_FEATURES_ENABLE

QUERY_REWRITE_ENABLED

STAR_TRANSFORMATION_ENABLED

Object Privileges

CREATE ANY OUTLINE

DROP ANY OUTLINE

GRANT create any outline, drop any outline TO uwclass;

System Privileges

ALTER ANY OUTLINE

CREATE ANY OUTLINE

DROP ANY OUTLINE

Notes

Cursor sharing and outlines

If cursor_sharing = similar

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

You have to work to CAPTURE the outline if you want to use stored outlines with cursor_sharing enabled; or you have to work out / discover the actual text optimized, and "create stored outline" for that 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 OUTLINE

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

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 OUTLINE

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 OUTLINE

Drop single outline

DROP OUTLINE <outline_name>;

See Outline Demo Below

OUTLINE Demo

conn / as sysdba

GRANT alter system TO uwclass;

conn uwclass/uwclass

-- if server and serv_inst tables do not exist CLICK HERE for the build script.

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;