Re: Oracle Hints

In article <391AC373.CD8179A3_at_Unforgettable.com>,   Kenneth C Stahl <BlueSax_at_Unforgettable.com> wrote:
> Sybrand Bakker wrote:
> >
> > Gertjan <gr_at_compexbv.nl> schreef in berichtnieuws
> > Gertjan

1) ... a situation like this. I'm about to start working with an application in which statistics have never been run. The developer who wrote the front end has embedded a lot of hints in his code to make the application work better. If I start running statistics will the execution plan for the sql which uses the hints change?

Yes, it is possible the plan may change depending on the hints used. In particular if Oracle chooses to use a hash join I have seen index hints ignored. You can eliminate the hash and use the index in two ways. Alter the session to disable hash joins or use the ORDERED hint to lock in the join order and add the USE_NL hint to get Oracle to use a nested loops join. The ORDERED hint requires that the tables be listed in the from clause from left to right (or top to bottom) in the order in which you want the join to be done.

2) Should I suggest to the developer that once I start running statistics that he drop the hints and see if he gets an execution plan that is better then what he was getting before with the hint?

Running explain plan on the hinted and unhinted SQL against the analyzed instance is a good idea. You should then compare what you believe is the best way to proceed against what the CBO wants to do. Remember also that as the data changes and the statistics are updated the plan used by the CBO may/will change so once you determine the best plan locking it in is not a bad idea. The proper use of hints can prevent a good query going bad when the statistics change or an upgrade takes place. It also may prevent new features, like hash join with 7.3, from being used so re-tuning/verification of the SQL should be done on a periodic basis.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.