I am new to oracle database developer. currently i am leraning the performance tuning.
I have a question that is it a good practice to use hint as having using oracle database version 10g.
some where i have read and also some Senior members from suggest me that before 9i the optimizer was rule based , hence there was nesecarry to use hint.
after 9i onwards the optimeser is cost based hence there is no need to provide the hints.
but i have seen some queries from the expert where they have used hints.( even in db version 10g).
so can you please tell me that if we wants to use hints then when to use them>\
thanks in advance
> Senior members from suggest me that before 9i the optimizer was rule based
No, the CBO was introduced in Oracle 7.
> so can you please tell me that if we wants to use hints then when to use them>
Hints can be handy during development for tuning and tweaking (but not twerking).
In production code, hints can be poison. Some of the worst performance problems I've seen were *caused* by inappropriate hints.
Keep in mind I'm talking about "tuning hints". There are some other hints (like insert /*+ append */) that fall into a totally different category.
Hints are a path of last resort.
I suggest you do some searches on http://asktom.oracle.com and you'll get tons of examples and explanations regarding performance tuning, as well as hints.
Also, you should read the Oracle Performance Tuning Guide, it comes in 2 flavors:
2-day Guide: Contents
Full Guide: Contents