rule hint. Any ideas

[clinton]

I just received a dump file from an export
taken from an 8.1.7 database from one of my company's
ofshore application.
I'm importing this data into a 9.2 database because
we have another application here that needs those
data to work.
During the import I realised that a lot of views
are being imported and these views have been
created using a rule hint (/*+ RULE */). I tried to get in touch
with the contractor that worked on this application
to find out why he used the rule hint,
but he is no longer with the company.

Since 9.2 uses the CBO, I'm thinking of recreating
the views without the rule hint. But I would test it
first.

What would you do if you were in this situation?

[abhaysk]

test the in line queries with CBO ( i.e analyze underlying tables and remove off that stupid RULE hint )..

Ofcourse on TEST/ACP box first.

Abhay.

[apunhiran]

Hi,
I personally feel that the /*+RULE*/ hint may be really required. At time when we use the CBO, the response time is not good. Try taking the explain plan with and without RULE hint and you will see the difference. At time RULE works much better than CBO. So be very sure before to recompile the views removing the hints.

Personally see the different in response time by selecting from the views and then take the decision.
We have certain reporting environments which completly work in RULE based optimizer mode and work good. Initially they work in CBO and the performance was slow.

Thanks
Regards

[slimdave]

Situations where RBO performs better than CBO are generally ones where every access is done by index. If CBO fails to perform in these circumstances it may be that it is rating the cost of index access too highly (of course, out of date segment statistics may also be an issue).

The init parameter that most strongly determines how aggressive the CBO is in choosing index access over table access is optimizer_index_cost_adj. The default value of 100 is way too high -- better results generally occur with values down around 20, or even less.