| plan stability:outlines [message #158134] |
Wed, 08 February 2006 11:01  |
rkl1
Messages: 97
Registered: June 2005
|
Member |
|
|
Dear Members and Experts: Here is my worry. Our application uses some third party stuffs and we fished out some bad sql which we love to tune.We are wondering if we could use the stored outlines as added to Oracle in 8i(I presume !).I did a little outline mad scientist experiment but the hypothesis is not working out. My assumption is that, if we have a stored outline, and when optimizer parses the sql statements identical to it, then it would use the stored execution plan.
1.create table x5 as select rownum as num1, object_name from dba_objects;
2.create index ind1 on x5(num1) compute statistics;
3.create or replace outline trial1 for category nfdba on
select /*+full(x5)*/ * from x5 where num1=1001;
4.alter system set create_stored_outlines=false;
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
pause:Dear all: I have used the full hint and run an explain plan and it showed that it uses full tablescan and not the index.
so my hypothesis is : if I run the statement:
select * from x5 where num1=1001; it would use the full tablescan rather than using the index.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
5.alter system set use_stored_outlines=nfdba;
6.select * from x5 where num1=1001;
Explain plan shows it uses the index not the full tablescan as intended. I have put cursor_sharing=similar.
Since we could not change the hard coded sql from third party, we intended to put the hints etc to optimize it. What we could do, optimizer would use the stored outlines all the times as expected.
Any suggestion, advice would be highly appreciated.
thanks.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
