CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Outlines
Version 10.2
 
General
Data Dictionary Objects Related To Outlines
dba_outlines all_outlines user_outlines
dba_outline_hints all_outline_hints user_outline_hints
System Privileges Related To Outlines
ALTER ANY OUTLINE CREATE ANY OUTLINE DROP ANY OUTLINE

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;
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.
Initialization parameters OPTIMIZER_FEATURES_ENABLE
QUERY_REWRITE_ENABLED
STAR_TRANSFORMATION_ENABLED
Object privileges GRANT create any outline TO <schema_name>;
GRANT drop any outline TO <schema_name>;
GRANT create any outline, drop any outline TO uwclass;
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>;
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
 
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;
 
Related Topics
DBMS_OUTLN
DBMS_OUTLN_EDIT
Hints
OUTLN_PKG
OUTLN_EDIT_PKG
Session
System
System Triggers
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [84 users online]    © 2010 psoug.org