Store Outlines - 10g
==============
A stored outline is a collection of hints associated with a specific SQL statement that allows a
standard execution plan to be maintained, regardless of changes in the system environment or associated statistics.
Plan stability is based on the preservation of execution plans at a point in time where the performance of a statement is considered acceptable.
The outlines are stored in the OL$, OL$HINTS, and OL$NODES tables, but the [USER|ALL|DBA]_OUTLINES and [USER|ALL|DBA]_OUTLINE_HINTS views should be used to
display information about existing outlines.
Creating outlines:-
Outlines can be created automatically by oracle by setting the parameter create_stored_outlines or
manually for sql statements.
The parameter create_stored_outlines can be set at session / Instance level.
For creating the outlines manually we can use the create outline DDL or dbms_outln packge.
Eg:-
SQL> grant execute_Catalog_role to manzoor;
Grant succeeded.
SQL> grant create any outline to manzoor;
Grant succeeded.
sql>conn manzoor/manzoor
connected
SQL> create table emp (emp_no number, e_name varchar2(30), depart_id number);
Table created.
SQL> create table department (depart_id number primary key, department_name varchar2(30));
Table created.
SQL> alter table emp add constraint emp_pk primary key (emp_no);
Table altered.
SQL> alter table emp add constraint dep_fk foreign key (depart_id) references department (depart_id);
Table altered.
## Insert some values to department and emp tables ######
SQL> select * from department;
DEPART_ID DEPARTMENT_NAME
---------- ------------------------------
100 Software
101 Hardware
102 HR
103 Admin
104 Transport
105 Health
106 Electricity
107 Wealth
108 Miscellanious
9 rows selected.
begin
for i in 1003..300000 loop
insert into emp values (i,dbms_random.string('U',30),round(dbms_random.value(100,108)));
if i mod 10000 = 0 then
commit;
end if;
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL> select count(*) from emp;
COUNT(*)
----------
299001
Now we are going to create an outline for the below query:-
SQL> create outline emp_dept for category empanddept on
select a.emp_no, a.e_name, b.department_name from emp a , department b where a.depart_id = b.depart_id and rownum < 10;
Check the list of hints crated of this outline.
SQL> SELECT node, stage, join_pos, hint FROM user_outline_hints WHERE name = 'EMP_DEPT';
NODE STAGE JOIN_POS HINT
---------- ---------- ---------- --------------------------------------------------------------------------------
1 1 0 USE_HASH(@"SEL$1" "A"@"SEL$1")
1 1 0 LEADING(@"SEL$1" "B"@"SEL$1" "A"@"SEL$1")
1 1 2 FULL(@"SEL$1" "A"@"SEL$1")
1 1 1 FULL(@"SEL$1" "B"@"SEL$1")
1 1 0 OUTLINE_LEAF(@"SEL$1")
1 1 0 ALL_ROWS
1 1 0 OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
1 1 0 IGNORE_OPTIM_EMBEDDED_HINTS
Now execute the query.
SQL> select a.emp_no, a.e_name, b.department_name from emp a , department b where a.depart_id = b.depart_id and rownum < 10;
EMP_NO E_NAME DEPARTMENT_NAME
---------- ------------------------------ ------------------------------
1000 Manzoor Software
1019 OPMQIQAMXYJHBTVRVFIWZJLZAOYTCM Software
1032 KZTBKZKSZKGHHOMXUVANIIWVQQYBOE Software
1048 GDBQSACOZRCONFFPMMAGHXZDSGAATC Software
1072 UCYVYCALRSNXMLGPYAAQOJGEPQYKKD Software
1073 YVCATTWMYUNRYUHPKIPHZHCJKSXFJF Software
1083 DQRRSUSNHHYUCAJGFBDAVXOBDNDSGE Software
1085 ZHXULTHXKGKTWOINPSCZXOGJTCLGQO Software
1089 JFJGRUXLLRKASMVVPSNYVJYRJYKHFL Software
Check whether the outline is used or not.
SQL> select name, owner, category, used,enabled from dba_outlines where name = 'EMP_DEPT';
NAME OWNER CATEGORY USED ENABLED
------------------------------ ------------------------------ ------------------------------ ------ --------
EMP_DEPT MANZOOR EMPANDDEPT UNUSED ENABLED
The above query shows that the outline is unsed.
Now let's have a look on the current explain plan.
SQL> select sql_id from V$sql where sql_text like '%a.emp_no, a.e_name%';
SQL_ID
-------------
5q10ntkvp78qh
SQL> SET LONG 10000;
SQL> select * from table (dbms_xplan.display_cursor('5q10ntkvp78qh',0));
Plan hash value: 865241754
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | NESTED LOOPS | | 288K| 20M| 6 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPARTMENT | 9 | 270 | 2 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| EMP | 32090 | 1347K| 0 (0)| |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<10 p="p"> 4 - filter("A"."DEPART_ID"="B"."DEPART_ID")
Note
-----
- dynamic sampling used for this statement
## The plan also shows that the outline is not used.
To enable the outlines we need to enable query rewrites and indicate which outline category the instance or
session should use. This is done using the ALTER SYSTEM and ALTER SESSION commands. In the following example we
will enable stored outlines for the current session.
SQL> alter session set QUERY_REWRITE_ENABLED = TRUE;
Session altered.
SQL> ALTER SESSION SET use_stored_outlines=EMPANDDEPT;
Session altered.
Now execute the query:-
SQL> select a.emp_no, a.e_name, b.department_name from emp a , department b where a.depart_id = b.depart_id and rownum < 10;
EMP_NO E_NAME DEPARTMENT_NAME
---------- ------------------------------ ------------------------------
1000 Manzoor Software
1019 OPMQIQAMXYJHBTVRVFIWZJLZAOYTCM Software
1032 KZTBKZKSZKGHHOMXUVANIIWVQQYBOE Software
1048 GDBQSACOZRCONFFPMMAGHXZDSGAATC Software
1072 UCYVYCALRSNXMLGPYAAQOJGEPQYKKD Software
1073 YVCATTWMYUNRYUHPKIPHZHCJKSXFJF Software
1083 DQRRSUSNHHYUCAJGFBDAVXOBDNDSGE Software
1085 ZHXULTHXKGKTWOINPSCZXOGJTCLGQO Software
1089 JFJGRUXLLRKASMVVPSNYVJYRJYKHFL Software
9 rows selected.
SQL> SELECT name, category, used FROM user_outlines;
NAME CATEGORY USED
------------------------------ ------------------------------ ------
EMP_DEPT EMPANDDEPT UNUSED
Now we can see that the stored outlines is used. Let look at the plan now.
SQL> select sql_id, child_number from V$sql where sql_Text like '%a.emp_no, a.e_name, b.department_%';
SQL_ID CHILD_NUMBER
------------- ------------
5q10ntkvp78qh 0
5q10ntkvp78qh 1
5q10ntkvp78qh 2
There are 3 childs are available for this sql now. Lets look the plan for each.
SQL> select * from table (dbms_xplan.display_cursor('5q10ntkvp78qh',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5q10ntkvp78qh, child number 0
-------------------------------------
select a.emp_no, a.e_name, b.department_name from emp a , department b
where a.depart_id = b.depart_id and rownum < 10
Plan hash value: 865241754
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | NESTED LOOPS | | 288K| 20M| 6 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPARTMENT | 9 | 270 | 2 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| EMP | 32090 | 1347K| 0 (0)| |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<10 p="p"> 4 - filter("A"."DEPART_ID"="B"."DEPART_ID")
Note
-----
- dynamic sampling used for this statement
27 rows selected.
SQL> select * from table (dbms_xplan.display_cursor('5q10ntkvp78qh',1));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5q10ntkvp78qh, child number 1
-------------------------------------
select a.emp_no, a.e_name, b.department_name from emp a , department b
where a.depart_id = b.depart_id and rownum < 10
Plan hash value: 865241754
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | NESTED LOOPS | | 288K| 20M| 6 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPARTMENT | 9 | 270 | 2 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| EMP | 32090 | 1347K| 0 (0)| |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<10 p="p"> 4 - filter("A"."DEPART_ID"="B"."DEPART_ID")
Note
-----
- dynamic sampling used for this statement
27 rows selected.
SQL> select * from table (dbms_xplan.display_cursor('5q10ntkvp78qh',2));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5q10ntkvp78qh, child number 2
-------------------------------------
select a.emp_no, a.e_name, b.department_name from emp a , department b
where a.depart_id = b.depart_id and rownum < 10
Plan hash value: 3200020666
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
|* 1 | COUNT STOPKEY | | | | | |
|* 2 | HASH JOIN | | 82 | 5986 | 5 (20)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPARTMENT | 82 | 2460 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMP | 82 | 3526 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<10 p="p"> 2 - access("A"."DEPART_ID"="B"."DEPART_ID")
Note
-----
- outline "EMP_DEPT" used for this statement
27 rows selected.
## We could see that the child 1 and the child 2 plans are not using the stored outlines
and also the cost is high when compared to child 3 plan which is using the outline.
Now lets create index on the depart_id on emp table and check.
SQL> create index dept_idx on emp(depart_id);
Index created.
Now lets execute the same query.
SQL> select a.emp_no, a.e_name, b.department_name from emp a , department b where a.depart_id = b.depart_id and rownum < 10;
EMP_NO E_NAME DEPARTMENT_NAME
---------- ------------------------------ ------------------------------
1000 Manzoor Software
1001 Ahamed Hardware
1002 Jameel Hardware
1003 KZPWKAVBCXRXCOEBQLLQBIJQWZPGDA HR
1004 VPZVDOTYLJWCMFYOUIWSCYAHGYEDOB Hardware
1005 WSWNBQHCMZHNDGGXLFWIJCBOBODYSP Wealth
1006 VDFJDDNBBTQKVJFYSMVNMQDQYHALTG Hardware
1007 YQEQRGTHYFVGESHAHOKRLITMULFUWK Health
1008 ZRYGBCCMXXHCRFYDYNRRWCAPSBKLOQ Admin
9 rows selected.
Lets see what is the execution plan which is used now.
SQL> select sql_id, child_number from V$sql where sql_Text like '%a.emp_no, a.e_name, b.department_%';
SQL_ID CHILD_NUMBER
------------- ------------
5q10ntkvp78qh 0
SQL> select * from table (dbms_xplan.display_cursor('5q10ntkvp78qh',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5q10ntkvp78qh, child number 0
-------------------------------------
select a.emp_no, a.e_name, b.department_name from emp a , department b
where a.depart_id = b.depart_id and rownum < 10
Plan hash value: 3200020666
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
|* 1 | COUNT STOPKEY | | | | | |
|* 2 | HASH JOIN | | 82 | 5986 | 5 (20)| 00:00:01 |
| 3 | TABLE ACCESS FULL| DEPARTMENT | 82 | 2460 | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMP | 82 | 3526 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<10 p="p"> 2 - access("A"."DEPART_ID"="B"."DEPART_ID")
Note
-----
- outline "EMP_DEPT" used for this statement
27 rows selected.
## We could see that even though the index is created the index is not created
and still it used the stored outline for the explain plan. Now we d
SQL> conn manzoor/manzoor
Connected
SQL> select a.emp_no, a.e_name, b.department_name from emp a , department b where a.depart_id = b.depart_id and rownum < 10;
EMP_NO E_NAME DEPARTMENT_NAME
---------- ------------------------------ ------------------------------
1000 Manzoor Software
1019 OPMQIQAMXYJHBTVRVFIWZJLZAOYTCM Software
1032 KZTBKZKSZKGHHOMXUVANIIWVQQYBOE Software
1048 GDBQSACOZRCONFFPMMAGHXZDSGAATC Software
1072 UCYVYCALRSNXMLGPYAAQOJGEPQYKKD Software
1073 YVCATTWMYUNRYUHPKIPHZHCJKSXFJF Software
1083 DQRRSUSNHHYUCAJGFBDAVXOBDNDSGE Software
1085 ZHXULTHXKGKTWOINPSCZXOGJTCLGQO Software
1089 JFJGRUXLLRKASMVVPSNYVJYRJYKHFL Software
9 rows selected.
Let see the explain plan now.
SQL> select sql_id, child_number from V$sql where sql_Text like '%a.emp_no, a.e_name, b.department_%';
SQL_ID CHILD_NUMBER
------------- ------------
5q10ntkvp78qh 0
5q10ntkvp78qh 1
SQL> select * from table (dbms_xplan.display_cursor('5q10ntkvp78qh',1));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5q10ntkvp78qh, child number 1
-------------------------------------
select a.emp_no, a.e_name, b.department_name from emp a , department b where
a.depart_id = b.depart_id and rownum < 10
Plan hash value: 2690663879
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 32090 | 1347K| 3 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 288K| 20M| 6 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | DEPARTMENT | 9 | 270 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | DEPT_IDX | 10 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<10 p="p"> 5 - access("A"."DEPART_ID"="B"."DEPART_ID")
Note
-----
- dynamic sampling used for this statement
Now the outline is not used.
No comments:
Post a Comment