| 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; |
