Oracle | Outlines

Oracle Outlines Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose A set ofattributes used by the optimizer to generate an execution plan. The optimizer can be instructed 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; USERNAME ACCOUNT_STATUS ------------------------ --------------- ANONYMOUS LOCKED APPQOSSYS LOCKED AUDSYS LOCKED C##CONVERT OPEN C##UWCLASS OPEN CTXSYS LOCKED DBSFWUSER LOCKED DBSNMP LOCKED DGPDB_INT LOCKED DIP LOCKED DVF LOCKED DVSYS LOCKED GGSYS LOCKED GSMADMIN_INTERNAL LOCKED GSMCATUSER LOCKED GSMROOTUSER LOCKED GSMUSER LOCKED LBACSYS LOCKED MDDATA LOCKED MDSYS LOCKED OJVMSYS LOCKED OLAPSYS LOCKED ORACLE_OCM LOCKED ORDDATA LOCKED ORDPLUGINS LOCKED ORDSYS OPEN OUTLN LOCKED REMOTE_SCHEDULER_AGENT LOCKED SI_INFORMTN_SCHEMA LOCKED SYS OPEN SYS$UMF LOCKED SYSBACKUP LOCKED SYSDG LOCKED SYSKM LOCKED SYSRAC OPEN SYSTEM OPEN WMSYS OPEN XDB LOCKED XS$NULL LOCKED ALTER USER outln ACCOUNT unlock; User altered. ALTER USER outln IDENTIFIED BY Out1n#Out1n#; User altered. 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;