| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Stored outlines = plan stabilization (long)
John
Below is a sample sql script which demonstrates using stored outlines. It's based on one used at a course I was on, but it should work with the sample SCOTT/TIGER emp table (it does on 9.2). Before the outline is created, Oracle will use the index to lookup the row. The outline (using hints) forces a full table scan. Subsequent sessions using will use the stored outline.
Ray
-------------out.sql----------------
set long 24
col hint_text for a30
col stage# for 99999
col t_id for 9999
col table_name for a12
col object_name for a20
col node# for 99999
col ol_name for a8
col category for a8
col version for a10
col creator for a4
col hintcount for 99999
col hint# for 999
col options for a20
alter session set use_stored_outlines = true;
alter session set use_private_outlines = false;
exec dbms_outln_edit.drop_edit_tables;
analyze table emp compute statistics;
set autot traceonly
select count(*) from emp;
set autot off
truncate table plan_table;
explain plan for
select count(*)
from emp E;
select operation,options,object_name
from plan_table;
rem Index Scan
pause Now run the query
select count(*) from emp E;
pause Now set up a public outline
create or replace public outline OL1
for category C1 on
select count(*) from emp E;
rem now look at the outline in the OUTLN schema
pause
select ol_name,category,version,creator,
timestamp,hintcount,sql_text
from outln.ol$
where ol_name = 'OL1';
pause Now the hints
select h.hint#,h.stage#,h.node#,h.hint_text,
h.table_name, table_pos
from outln.ol$ ol, outln.ol$hints h
where ol.ol_name = h.ol_name
and ol.ol_name = 'OL1'
order by h.stage#, h.hint#;
pause Now lets set up a private outline editing area
exec dbms_outln_edit.create_edit_tables;
pause Now show them
select table_name, temporary,duration
from user_tables
where table_name like 'OL%';
rem these are sessional temporary tables
pause Now clone the public outline for editing
create private outline POL1
from OL1
for category C1;
pause Look at it
select ol_name,category,version,creator,
timestamp,hintcount,sql_text
from scott.ol$
where ol_name = 'POL1';
rem now the hints
pause
select h.hint#,h.stage#,h.node#,h.hint_text,
h.table_name, table_pos
from scott.ol$ ol, scott.ol$hints h
where ol.ol_name = h.ol_name
and ol.ol_name = 'POL1'
order by h.stage#, h.hint#;
rem they are the same at the moment
pause
truncate table plan_table;
pause Force index scan pretending this is the best method
explain plan for
select /*+ full(E) */ count(*)
from emp E;
pause Now see the plan
select operation,options,object_name
from plan_table;
pause Set up an outline to match this plan
create or replace private outline POL2
for category C1 on
select /*+ full(E) */ count(*)
from emp E;
pause Now see the outline
select h.hint#,h.stage#,h.node#,h.hint_text,
h.table_name, table_pos
from scott.ol$ ol, scott.ol$hints h
where ol.ol_name = h.ol_name
and ol.ol_name = 'POL2'
order by h.stage#, h.hint#;
pause Now we will swap the outlines by updating the private tables
update scott.ol$hints
set ol_name=decode(ol_name,'POL2','POL1','POL1','POL2')
where ol_name in ('POL2','POL1');
commit;
pause Invalidate and refresh shared pool content for POL1
exec dbms_outln_edit.refresh_private_outline('POL1');
pause Alter session to use private outline
alter session set use_private_outlines = C1;
pause
truncate table plan_table;
pause see if the plans have been changed
explain plan for
select count(*)
from emp E;
pause
select operation,options,object_name
from plan_table;
rem Index is used now without hint as private outline was found
rem now run the query
pause
select count(*)
from emp E;
pause Now the private outline can be moved to the PUBLIC area
create or replace outline OL1
from private POL1 for category C1;
pause Now stop using private outlines for the session
alter session set use_private_outlines = false;
pause Start using public outlines
alter session set use_stored_outlines = C1;
pause Now explain again
truncate table plan_table;
explain plan for
select count(*)
from emp E;
pause
select operation,options,object_name
from plan_table;
pause
select count(*) from emp E;
---------------end out.sql--------------
-----Original Message-----
From: Fedock, John (KAM.RHQ) [mailto:John.Fedock_at_us.kline.com]
Sent: Friday, January 30, 2004 7:26 PM
To: oracledba_at_lazydba.com; oracle-l_at_freelists.org
Subject: Stored outlines = plan stabilization
Resending this to the new group ...
I have been trying to get stored outlines to work. It seems simple enough, but it is working the exact opposite as I think it should. Anyone else use these? I am on 8.1.7.4 on HP-UX 11.00
In a nutshell, I verify my query is using the correct explain plan. I grant 'create any outline' to the users.
Then, I do:
alter system set use_stored_outlines = true
ALTER SESSION SET CREATE_STORED_OUTLINES=true;
select * from edi_monitor_vw;
ALTER SESSION SET CREATE_STORED_OUTLINES=false;
I can see the outline in DBA_OUTLINES and also in outln.ol$
If I open another session and run the query, it is NOT using the correct explain plan.
If I 'alter system set use_stored_outlines = false;' then the query uses the correct plan.
I also noticed that outline_category in v$sql is NULL. Does this mean that they outline is not being used? I assume not.
Thanks all,
John
John Fedock
"K" Line America, Inc.
www.kline.com
* john.fedock_at_us.kline.com
John Fedock
"K" Line America, Inc.
www.kline.com
* 804.327.4444
* john.fedock_at_us.kline.com
This email and any attached to it are confidential and intended only for the individual or entity to which it is addressed. If you are not the intended recipient, please let us know by telephoning or emailing the sender. You should also delete the email and any attachment from your systems and should not copy the email or any attachment or disclose their content to any other person or entity. The views expressed here are not necessarily those of Churchill Insurance Group plc or its affiliates or subsidiaries. Thank you.
Churchill Insurance Group plc. Company Registration Number - 2280426. England.
Registered Office: Churchill Court, Westmoreland Road, Bromley, Kent BR1 1DP.
 |
 |