Home » RDBMS Server » Performance Tuning » plan stability:outlines
plan stability:outlines [message #158134] Wed, 08 February 2006 11:01 Go to next message
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.
Re: plan stability:outlines [message #158176 is a reply to message #158134] Wed, 08 February 2006 16:57 Go to previous message
chetwyn
Messages: 73
Registered: December 2005
Member
If you only select from one table there are only two outcomes.
FTS(Ftull table scan) OR Index FULL Range Scan.
Previous Topic: Comparing data from two databases – Exception reports
Next Topic: In Vs Like
Goto Forum:
  


Current Time: Thu Mar 28 10:14:21 CDT 2024