| Why RULE hints is used? [message #340300] |
Tue, 12 August 2008 04:37  |
Olivia
Messages: 519
Registered: June 2008
|
Senior Member |
|
|
Why a programmer uses RULE hints.What are the things that compells the programmer to use RULE hints and use Rule based optimizer other than going for other hints (which uses CBO).Thats a question that comes in my mind.May be its a silly kind of question ..but I admit that I have lack of knowledge in this arena.
I am going through the Oracle Performance Tuning guide.Can anyone please provide the reasons behind using Rule hints..
Regards,
Oli
|
|
|
|
| Re: Why RULE hints is used? [message #340304 is a reply to message #340300] |
Tue, 12 August 2008 04:48  |
gintsp
Messages: 118
Registered: February 2007
|
Senior Member |
|
|
I've mostly seen following reasons:
1) Lack of knowledge about RBO and CBO at all.
2) Lack of desire to learn something new. Lack of understanding that new features simply require to use CBO.
3) Used just a quick fix, if tried for a specific query and works faster than with CBO. Tweaking stats and learning to use new hints requires more time sometimes. However I've succesfully managed these cases just calculating stats and if this doesn't help then mostly using leading and/or cardinality hints.
Gints Plivna
[Updated on: Tue, 12 August 2008 05:29] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
| Re: Why RULE hints is used? [message #340827 is a reply to message #340300] |
Thu, 14 August 2008 09:12 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I've seen a lot of RULE hints in code that dates back to Oracle 7.3.* or 8.0. Back then, the CBO was much, much ropier than it is now, and frequenty the RBO gave better results.
In many places, there is a 'if it isn't broken, don't fix it' approach - if the production code is working adequately, then it doesn't need to be interfeered with. Thus the RULE hints stay.
|
|
|
|
| Re: Why RULE hints is used? [message #340835 is a reply to message #340827] |
Thu, 14 August 2008 09:38 |
Olivia
Messages: 519
Registered: June 2008
|
Senior Member |
|
|
Here in our databse I can see so many sql's that using Rule hint and giving better response time than using other hint or without hint. The margin is to less comparison to using other hints/without hint.
Problem is that we are going to migrate our databse to 10g. As Rule hints are no longer officially supported there must be some alternative way so that we get the same performance without rule hint.
We are not going to use Rule hint further.
Regards,
Oli
|
|
|
|
|
|
| Re: Why RULE hints is used? [message #340918 is a reply to message #340853] |
Thu, 14 August 2008 22:27 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
The thing about RULE is that it prioritises index use quite highly.
I agree that PROPER statistics should out-perform RULE on average, but if you wanted to keep prioritising index usage, you could try replacing the RULE hints with FIRST_ROWS.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
