manageStoredOutlines.html
03-25-2002 Bikle
-----------------
Manage Stored Outlines
I started my study of this by looking at Ch7 of the 9i Perf Tuning Guide:
http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a87503/outlines.htm#13547
Q: What is 'Plan Stability'?
Ans: It allows the DBA to stabilize an Execution Plan of a statement.
Q: What is a 'Stored Outline'?
Ans: It is an object which stores an Execution Plan. Under the covers
it does this by storing a set of hints.
Q: May a Stored Outline apply to more than one SQL statement?
Ans: No. With clever use of bind variables in the statement, however,
you may gain considerable room to move around within this restriction.
You may gain even more flexibility with clever use of the
CURSOR_SHARING, and CREATE_STORED_OUTLINES parameters which can be set
at session or system level.
8i demo:
11:14:10 SQL> connect internal
Connected.
11:14:46 SQL> GRANT CREATE ANY OUTLINE TO scott
11:14:59 2 /
Grant succeeded.
11:15:02 SQL> CONNECT scott/s
Connected.
11:15:11 SQL> ALTER SESSION SET CREATE_STORED_OUTLINES = TRUE
11:16:27 2 /
Session altered.
11:16:29 SQL> SELECT COUNT(*) FROM EMP;
COUNT(*)
----------
14
11:17:06 SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
bikleprod2% sql scott/s
SQL*Plus: Release 8.1.7.0.0 - Production on Mon Mar 25 11:19:35 2002
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
11:19:35 SQL> DESC user_outlines
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(30)
CATEGORY VARCHAR2(30)
USED VARCHAR2(9)
TIMESTAMP DATE
VERSION VARCHAR2(64)
SQL_TEXT LONG
11:19:54 SQL> SELECT name, category, used, timestamp FROM user_outlines;
NAME CATEGORY USED
------------------------------ ------------------------------ ---------
TIMESTAMP
---------
SYS_OUTLINE_020325111706808 DEFAULT UNUSED
25-MAR-02
11:20:55 SQL> SELECT sql_text FROM user_outlines;
SQL_TEXT
--------------------------------------------------------------------------------
SELECT COUNT(*) FROM EMP
11:21:45 SQL> DESC v$sql
Name Null? Type
----------------------------------------- -------- ----------------------------
SQL_TEXT VARCHAR2(1000)
SHARABLE_MEM NUMBER
PERSISTENT_MEM NUMBER
RUNTIME_MEM NUMBER
SORTS NUMBER
LOADED_VERSIONS NUMBER
OPEN_VERSIONS NUMBER
USERS_OPENING NUMBER
EXECUTIONS NUMBER
USERS_EXECUTING NUMBER
LOADS NUMBER
FIRST_LOAD_TIME VARCHAR2(19)
INVALIDATIONS NUMBER
PARSE_CALLS NUMBER
DISK_READS NUMBER
BUFFER_GETS NUMBER
ROWS_PROCESSED NUMBER
COMMAND_TYPE NUMBER
OPTIMIZER_MODE VARCHAR2(10)
OPTIMIZER_COST NUMBER
PARSING_USER_ID NUMBER
PARSING_SCHEMA_ID NUMBER
KEPT_VERSIONS NUMBER
ADDRESS RAW(4)
TYPE_CHK_HEAP RAW(4)
HASH_VALUE NUMBER
CHILD_NUMBER NUMBER
MODULE VARCHAR2(64)
MODULE_HASH NUMBER
ACTION VARCHAR2(64)
ACTION_HASH NUMBER
SERIALIZABLE_ABORTS NUMBER
OUTLINE_CATEGORY VARCHAR2(64)
11:23:43 SQL> SELECT COUNT(*) FROM v$sql WHERE outline_category = 'DEFAULT';
COUNT(*)
----------
0
11:24:55 SQL> ALTER SESSION SET use_stored_outlines = TRUE;
Session altered.
11:26:06 SQL> SELECT COUNT(*) FROM v$sql WHERE outline_category = 'DEFAULT';
COUNT(*)
----------
0
11:26:14 SQL> SELECT outline_category, COUNT(outline_category) FROM v$sql GROUP BY outline_category;
OUTLINE_CATEGORY
----------------------------------------------------------------
COUNT(OUTLINE_CATEGORY)
-----------------------
0
11:27:04 SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
bikleprod2%
This seems fishy; why is v$sql ignoring the outline?
Maybe I need to logout, login, ...
bikleprod2% sql scott/s
SQL*Plus: Release 8.1.7.0.0 - Production on Mon Mar 25 11:29:46 2002
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
11:29:46 SQL> ALTER SESSION SET use_stored_outlines = TRUE;
Session altered.
11:30:00 SQL> SELECT COUNT(*) FROM EMP;
COUNT(*)
----------
14
11:30:37 SQL> SELECT outline_category, COUNT(outline_category) FROM v$sql GROUP BY outline_category;
OUTLINE_CATEGORY
----------------------------------------------------------------
COUNT(OUTLINE_CATEGORY)
-----------------------
DEFAULT
1
0
11:31:25 SQL> SELECT sql_text FROM v$sql WHERE outline_category IS NOT NULL;
SQL_TEXT
--------------------------------------------------------------------------------
SELECT COUNT(*) FROM EMP
11:32:50 SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
bikleprod2%
I had to do three things to make this outline appear in v$sql which in my
mind is proof enough that it is active:
1. logout/login
2. ALTER SESSION SET use_stored_outlines = TRUE;
3. Issue the SQL command which is tied to the outline
Q: How do I name an outline?
Ans: Notice the outline I created is named: SYS_OUTLINE_020325111706808
Like system named constraints, it's not very helpful; I'll rename it.
Demo:
bikleprod2% sql scott/s
SQL*Plus: Release 8.1.7.0.0 - Production on Mon Mar 25 11:44:35 2002
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
11:44:35 SQL> ALTER OUTLINE SYS_OUTLINE_020325111706808 RENAME TO emp_count_star;
Outline altered.
11:46:04 SQL> SELECT name, category, used, timestamp FROM user_outlines;
NAME CATEGORY USED
------------------------------ ------------------------------ ---------
TIMESTAMP
---------
EMP_COUNT_STAR DEFAULT USED
25-MAR-02
11:46:31 SQL>
Also I can make use of the CREATE OUTLINE command:
11:46:31 SQL> ALTER SESSION SET use_stored_outlines = TRUE;
Session altered.
11:48:55 SQL> CREATE OUTLINE dept_count_star ON SELECT COUNT(*) FROM dept;
Outline created.
11:51:10 SQL> SELECT name, category, used FROM user_outlines;
NAME CATEGORY USED
------------------------------ ------------------------------ ---------
EMP_COUNT_STAR DEFAULT USED
DEPT_COUNT_STAR DEFAULT UNUSED
11:51:35 SQL> SELECT COUNT(*) FROM dept;
COUNT(*)
----------
4
11:51:49 SQL> SELECT name, category, used FROM user_outlines;
NAME CATEGORY USED
------------------------------ ------------------------------ ---------
EMP_COUNT_STAR DEFAULT USED
DEPT_COUNT_STAR DEFAULT USED
11:51:57 SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
bikleprod2%
Notice how I switched the value of the user_outlines.used column
by issuing the corresponding SQL statement:
SELECT COUNT(*) FROM dept;
Q: Suppose I create an outline named 'emp_ename' and I put it in a category
named 'app1'; how do I alter a session so that 'emp_ename' gets used.
Ans: Use this syntax:
ALTER SESSION SET use_stored_outlines = app1;
Demo:
12:22:58 SQL> CREATE OUTLINE emp_ename FOR CATEGORY app1 ON SELECT COUNT(ename) FROM emp;
Outline created.
12:24:28 SQL> SELECT name, category, used FROM user_outlines;
NAME CATEGORY USED
------------------------------ ------------------------------ ---------
EMP_COUNT_STAR DEFAULT USED
DEPT_COUNT_STAR DEFAULT USED
EMP_ENAME APP1 UNUSED
12:24:41 SQL> ALTER SESSION SET use_stored_outlines = app1;
Session altered.
12:25:30 SQL> SELECT COUNT(ename) FROM emp;
COUNT(ENAME)
------------
14
12:25:41 SQL> SELECT name, category, used FROM user_outlines;
NAME CATEGORY USED
------------------------------ ------------------------------ ---------
EMP_COUNT_STAR DEFAULT USED
DEPT_COUNT_STAR DEFAULT USED
EMP_ENAME APP1 USED
12:25:49 SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
Q: How do I inspect the actual hints stored in the outlines?
Ans: Query user_outline_hints.
Demo:
12:29:29 SQL> DESC user_outline_hints
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(30)
NODE NUMBER
STAGE NUMBER
JOIN_POS NUMBER
HINT VARCHAR2(512)
12:30:01 SQL> COLUMN hint FORMAT A22
12:30:39 SQL> SET LINES 99
12:30:52 SQL> L
1* SELECT * FROM user_outline_hints
12:30:56 SQL> /
NAME NODE STAGE JOIN_POS HINT
------------------------------ ---------- ---------- ---------- ----------------------
EMP_COUNT_STAR 1 3 0 NO_EXPAND
EMP_COUNT_STAR 1 3 0 ORDERED
EMP_COUNT_STAR 1 3 0 NO_FACT(EMP)
EMP_COUNT_STAR 1 3 1 FULL(EMP)
EMP_COUNT_STAR 1 2 0 NOREWRITE
EMP_COUNT_STAR 1 1 0 NOREWRITE
EMP_COUNT_STAR 1 1 0 RULE
DEPT_COUNT_STAR 1 3 0 NO_EXPAND
DEPT_COUNT_STAR 1 3 0 ORDERED
DEPT_COUNT_STAR 1 3 0 NO_FACT(DEPT)
DEPT_COUNT_STAR 1 3 1 FULL(DEPT)
NAME NODE STAGE JOIN_POS HINT
------------------------------ ---------- ---------- ---------- ----------------------
DEPT_COUNT_STAR 1 2 0 NOREWRITE
DEPT_COUNT_STAR 1 1 0 NOREWRITE
DEPT_COUNT_STAR 1 1 0 RULE
EMP_ENAME 1 3 0 NO_EXPAND
EMP_ENAME 1 3 0 ORDERED
EMP_ENAME 1 3 0 NO_FACT(EMP)
EMP_ENAME 1 3 1 FULL(EMP)
EMP_ENAME 1 2 0 NOREWRITE
EMP_ENAME 1 1 0 NOREWRITE
EMP_ENAME 1 1 0 RULE
21 rows selected.
12:30:59 SQL>
Q: What does the DBMS_OUTLN_EDIT package do?
Ans: It does not exist in 8i.
Here's some text from the 9i Supplied PL/SQL Ref.:
CHANGE_JOIN_POS
Changes the join position for the hint identified by
outline name and hint number to the position specified
by newpos.
Q: What's the 'join position'?
Ans: It looks like it corresponds to the user_outline_hints.join_pos column
and it must have something to do with joins. I'll study this later.
It is probably useful for tinkering with the performance of table joins.
CREATE_EDIT_TABLES
Creates outline editing tables in calling a user's schema.
Q: What are editing tables?
Ans: They are used to store private outlines.
Some useful 9i syntax which I created after reading Metalink doc. 144194.1:
CONNECT scott
-- SET AUTOTRACE ON EXPLAIN
-- In 144194.1 they use above syntax to see their query do a nested loops join
-- Also, they remember to ANALYZE the tables
EXECUTE dbms_outln_edit.create_edit_tables
CREATE PRIVATE OUTLINE priv_emp_ename FROM emp_ename;
-- edit priv_emp_ename here
-- In 144194.1 they demonstrate how to do the edit by updating scott.ol$hints
-- UPDATE ol$hints SET hint_text=... WHERE ...
-- I'm more interested in using dbms_outln_edit.change_join_pos to do the edits.
EXECUTE dbms_outln_edit.refresh_private_outline('PRIV_EMP_ENAME')
ALTER SESSION SET use_private_outlines=TRUE;
-- SET AUTOTRACE ON EXPLAIN
-- In 144194.1 they use above syntax to see their query do a hash join
CREATE OR REPLACE OUTLINE emp_ename FROM PRIVATE private_emp_ename;
-- Above command will 'publicize' the hints I edited