Home » RDBMS Server » Performance Tuning » Why RULE hints is used? (Oracle 9i)
Why RULE hints is used? [message #340300] Tue, 12 August 2008 04:37 Go to next message
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 Go to previous messageGo to next message
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 #340319 is a reply to message #340304] Tue, 12 August 2008 05:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I concur.

Regards
Michel
Re: Why RULE hints is used? [message #340332 is a reply to message #340300] Tue, 12 August 2008 06:34 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks for the update...

Regards,
Oli
Re: Why RULE hints is used? [message #340827 is a reply to message #340300] Thu, 14 August 2008 09:12 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #340853 is a reply to message #340835] Thu, 14 August 2008 11:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The alternative is to get statistics up to date and to work on each query.

Regards
Michel
Re: Why RULE hints is used? [message #340918 is a reply to message #340853] Thu, 14 August 2008 22:27 Go to previous message
rleishman
Messages: 3724
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
Previous Topic: tracing, connection pools, java programs
Next Topic: HOW TO implement INDEXES ON NEW SCHEMA
Goto Forum:
  


Current Time: Tue Dec 06 08:15:13 CST 2016

Total time taken to generate the page: 0.15345 seconds