Oracle Outline
1)outline1@@@@information
@@@
select * from dba_outlines;
select * from dba_outline_hints;
select * from dba_view where view_name='DBA_OUTLINES'
select * from outln.ol$; @@@base table
select * from outln.ol$hints;@@@base table
@@@
@@@referencing
http://www.oracle-base.com/articles/misc/outlines.php
2@@@@a basic example for outline by scott
preview:
grant privilege to scott for lab.
create a outline "emp_dept" to a category "scott_outlines"
~~create a outline using general way.
create a second outline in the same category.
~~use "dbms_outln.create_outline" to create outline for that sql statement.
check the outline your created.
~~user_outlines
~~user_outlines_hints
begin to using outline for these sql statement.
drop outline
summary
@@@
@@@grant privilege to scott for lab.
@@@
SQL> conn / as sysdba;
SQL> grant create any outline to scott;
SQL> grant resource to scott;
SQL> grant execute_catalog_role to scott;
SQL>>
SQL>> @@@Outlines can be created automatically by Oracle or manually
@@@for specific statements.
@@@
@@@create a outline "emp_dept" to a category "scott_outlines"
@@@
@@@create a outline using general purpose
@@@one category map multi-oultine.
SQL> conn scott/scott
SQL> ed
1CREATE OUTLINEemp_dept
2FOR CATEGORY scott_outlines
3ON
4SELECT e.empno, e.ename, d.dname
5FROMemp e, dept d
6* WHERE e.deptno = d.deptno
SQL> /
@@@
@@@check using "user_outlines"
SQL> col name format a30
SQL> ed
1SELECT name, category, sql_text
2FROM user_outlines
3* WHERE category='SCOTT_OUTLINES'
SQL> /
NAME CATEGORY
------------------------------ -----------------
SQL_TEXT
----------------------------------------------
EMP_DEPT SCOTT_OUTLINES
SELECT e.empno, e.ename, d.dname
FROMemp e, dept d
WHERE e.deptno = d.deptno
@@@
@@@check using "user_outline_hints"
SQL> set linesize 200
SQL> ed
1SELECT node, stage, join_pos, hint
2FROM user_outline_hints
3* WHERE name='EMP_DEPT'
SQL> /
NODE STAGE JOIN_POS HINT
---------- ---------- ---------- ----------------------------------------------
1 1 0 USE_NL(@"SEL$1" "D"@"SEL$1")
1 1 0 LEADING(@"SEL$1" "E"@"SEL$1" "D"@"SEL$1")
1 1 2 INDEX(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))
1 1 1 FULL(@"SEL$1" "E"@"SEL$1")
1 1 0 OUTLINE_LEAF(@"SEL$1")
1 1 0 ALL_ROWS
1 1 0 OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
1 1 0 IGNORE_OPTIM_EMBEDDED_HINTS
8 rows selected.
@@@
@@@create a second outline in the same category.
@@@
@@@create the outline for this sql statement.
SQL> ed
1SELECT e.empno, e.ename, d.dname, e.job
2FROM emp e, dept d
3WHERE e.deptno = d.deptno
4* AND d.dname = 'SALES'
SQL> /
EMPNO ENAME DNAME JOB
---------- ---------- -------------- ---------
7499 ALLEN SALES SALESMAN
7521 WARD SALES SALESMAN
7654 MARTIN SALES SALESMAN
7698 BLAKE SALES MANAGER
7844 TURNER SALES SALESMAN
7900 JAMES SALES CLERK
@@@
SQL> ed
1SELECT hash_value, child_number, sql_text
2FROM v$sql
3* WHERE sql_text LIKE 'SELECT e.empno, e.ename, d.dname, e.job%'
SQL> /
HASH_VALUE CHILD_NUMBER
---------- ------------
SQL_TEXT
--------------------------------------------------------------------------------
3985699533 0
SELECT e.empno, e.ename, d.dname, e.job FROM emp e, dept d WHERE e.deptno = d.de
ptno AND d.dname = 'SALES'
@@@
@@@use "dbms_outln.create_outline"to create outline for that sql statement.
@@@according to the hash_value
SQL> ed
1BEGIN
2 DBMS_OUTLN.create_outline(
3 hash_value => 3985699533,
4 child_number => 0,
5 category => 'SCOTT_OUTLINES');
6* END;
SQL> /
PL/SQL procedure successfully completed.
@@@
@@@check the outline your created.
@@@
@@@check the outline using "user_outlines"
SQL> col name format a30
SQL> ed
1SELECT name, category, sql_text
2FROM user_outlines
3* WHERE category = 'SCOTT_OUTLINES'
SQL> /
NAME CATEGORY
------------------------------ ------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
EMP_DEPT SCOTT_OUTLINES
SELECT e.empno, e.ename, d.dname
FROMemp e, dept d
WHERE e.deptno = d.deptno
SYS_OUTLINE_12070714433606601SCOTT_OUTLINES
SELECT e.empno, e.ename, d.dname, e.job
FROM emp e, dept d
WHERE e.deptno = d.de
@@@
@@@check the outline using "user_outlines_hints"
SQL> col hint format a50
SQL> ed
1SELECT node, stage, join_pos, hint
2FROM user_outline_hints
3* WHERE name='SYS_OUTLINE_12070714433606601'
SQL> /
NODE STAGE JOIN_POS HINT
---------- ---------- ---------- --------------------------------------------------
1 1 0 USE_NL(@"SEL$1" "D"@"SEL$1")
1 1 0 LEADING(@"SEL$1" "E"@"SEL$1" "D"@"SEL$1")
1 1 2 INDEX(@"SEL$1" "D"@"SEL$1" ("DEPT"."DEPTNO"))
1 1 1 FULL(@"SEL$1" "E"@"SEL$1")
1 1 0 OUTLINE_LEAF(@"SEL$1")
1 1 0 ALL_ROWS
1 1 0 OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
1 1 0 IGNORE_OPTIM_EMBEDDED_HINTS
8 rows selected.
@@@
SQL> SELECT name, category , used FROM user_outlines;
NAME CATEGORY USED
------------------------------ ------------------------------ ------
EMP_DEPT SCOTT_OUTLINES UNUSED
SYS_OUTLINE_12070714433606601SCOTT_OUTLINES UNUSED
@@@
SQL> SELECT e.empno, e.ename , d.dname FROM emp e, dept d WHERE e.deptno=d.deptno;
SQL> SELECT e.empno, e.ename, d.dname, e.job FROM emp e, dept d
WHERE e.deptno = d.deptno AND d.dname = 'SALES';
@@@
SQL> SELECT name, category , used FROM user_outlines;
NAME CATEGORY USED
------------------------------ ------------------------------ ------
EMP_DEPT SCOTT_OUTLINES UNUSED
SYS_OUTLINE_12070714433606601SCOTT_OUTLINES UNUSED
@@@
@@@begin to using outline for these sql statement.
@@@
@@@open the execute plan to check
SQL>>
SQL>> SQL> set autot on
SQL> SELECT e.empno, e.ename , d.dname FROM emp e, dept d WHERE e.deptno=d.deptno;
Note
-----
- outline "EMP_DEPT" used for this statement
SQL> SELECT e.empno, e.ename, d.dname, e.job FROM emp e, dept d
WHERE e.deptno = d.deptno AND d.dname = 'SALES';
Note
-----
- outline "SYS_OUTLINE_12070714433606601" used for this statement
@@@
SQL> SELECT name, category , used FROM user_outlines;
NAME CATEGORY USED
------------------------------ ------------------------------ ------
EMP_DEPT SCOTT_OUTLINES USED
SYS_OUTLINE_12070714433606601SCOTT_OUTLINES USED
@@@
@@@drop outline
@@@
SQL> exec dbms_outln.drop_by_cat(cat=> 'SCOTT_OUTLINE');
PL/SQL procedure successfully completed.
@@@
@@@summary
@@@
keep one execution plan with runing a sql statement using current statistic,
when you set "alter session/system set use_stored_outline="
parameter, you would use privous excution and ignore current statistic.
3@@@@clone outline
@@@may be it is replacing.
SQL> select name,category from user_outlines;
NAME CATEGORY
------------------------------ ------------------------------
EMP_DEPT SCOTT_OUTLINES
SYS_OUTLINE_12070714433606601SCOTT_OUTLINES
SQL> CREATE OR REPLACE OUTLINE clone_outline01
FROM emp_dept
FOR CATEGORY scott_outlines;
Outline created.
SQL> select name,category from user_outlines;
NAME CATEGORY
------------------------------ ------------------------------
CLONE_OUTLINE01 SCOTT_OUTLINES
SYS_OUTLINE_12070714433606601SCOTT_OUTLINES
4@@@@private outline
@@@
SQL> CREATE PRIVATE OUTLINE private_dev01 FROM CLONE_OUTLINE01;
Outline created.
SQL> select name,category from user_outlines;
NAME CATEGORY
------------------------------ ------------------------------
CLONE_OUTLINE01 SCOTT_OUTLINES
SYS_OUTLINE_12070714433606601SCOTT_OUTLINES
SQL> CREATE OR REPLACEPRIVATE OUTLINE private_dev02 FROM clone_outline01;
Outline created.
@@@this is a implict parameter.
@@@true => use private outline
@@@false => use public outline
SQL>>
Session> 5@@@@outline editing
@@@use /*+ xxxxx */ hint to leading the excution plan in the outline
Editable Attributes
· Join order
· Join methods
· Access methods
· Distributed execution plans
· Distribution methods for parallel query execution
· Query rewrite
· View and subquery merging
@@@to be continue......................
页:
[1]