outline test

create table test1(id int,name varchar2(20));
create table test2(id int,age number(3));

show parameter USE_STORED_OUTLINES
show parameter query_rewrite_enabled;

select * from sys.V$SES_OPTIMIZER_ENV
SELECT NAME,VALUE,ISSES_MODIFIABLE,ISSYS_MODIFIABLE  FROM V$SYSTEM_PARAMETER
SELECT NAME,VALUE,ISSES_MODIFIABLE,ISSYS_MODIFIABLE  FROM V$PARAMETER
Select sys.dbms_metadata.get_ddl('TABLESPACE','TABLESPACE_NAME') from dual

 

update outln.ol$hints set hint_text=replace(hint_text,'USE_HASH','USE_NL');

select *  from   OUTLN.Ol$ ;
select *  from  OUTLN.OL$HINTS ;
select *  from  OUTLN.OL$NODES;

create or replace outline ol_test on select t1.id,t1.name,t2.age from shyt.test1 t1,shyt.test2 t2 where t1.id=t2.id;

create or replace outline ol_test2 on select * from shyt.test1,shyt.test2 where test1.id=test2.id;

 

explain plan for select * from shyt.test1,shyt.test2 where test1.id=test2.id;
select name,used from user_outlines;

explain plan for select t1.id,t1.name,t2.age from shyt.test1 t1,shyt.test2 t2 where t1.id=t2.id;
@?/rdbms/admin/utlxplp

ALTER SESSION SET USE_STORED_OUTLINES =OL_TEST;
alter session set use_stored_outlines = false;