Re: Hints

From: Kellyn Pot'vin <kellyn.potvin_at_ymail.com>
Date: Mon, 15 Aug 2011 08:35:15 -0700 (PDT)
Message-ID: <1313422515.88745.YahooMailNeo_at_web121020.mail.ne1.yahoo.com>


I'm in agreement with Alan, this question is so vague considering the subject matter-  there is no "one-word answer".

I've been guilty of giving a development team a difficult time and telling them I was going to send them to rehab to get past their "USE HASH" habit in a data warehouse I had worked long hours on to correct neglected statistics and design.  

I've also been guilty of promoting hints in another environment where the design and code combination to do what needed to be done, left the optimizer no way to make a solid choices, victimized by well-intentioned dynamic sampling and it required hints to ensure consistent, solid performance.

Hints have the positive gains of being statement focused, optimizer influencing when dynamic sampling is not your friend.
Hints have the negative issue of having to be hunted down and removed when the CBO is functioning well and not influencing if misunderstood or implemented incorrectly.

Just my 2c on hints... :)

Kellyn Pot'Vin
Sr Database Administrator and Developer
dbakevlar.com




________________________________
From: Guillermo Alan Bort <cicciuxdba_at_gmail.com>
To: oralrnr_at_gmail.com
Cc: oracle-l_at_freelists.org
Sent: Monday, August 15, 2011 7:52 AM
Subject: Re: Hints


The answer to that question, stated like that is a definite NO. Not *ALL* hints are considered bad.

This looks kind of like a flamebait to me... anyway I'll stake a stab at it and try to answer that I think you wanted to ask (or should've asked).

While not *ALL* hints are bad, with 10g the optimizer has become rather good at finding good execution plans. Sometimes the optimizer will choose the "wrong" plan (a plan that is not the best possible choice) but it will do so based on the data it has available, so most of the times it's just a matter to playing around with said data until the optimizer chooses a good plan. There are some rare instances where no matter how you change the statistics the plan will not be the one you expect, and that's when hints (in my opinion) come in.

Did a DBA give a scolding for hint overuse? or are you looking for ammo to scold your developers who are used to "the old ways"

cheers
Alan.-



On Mon, Aug 15, 2011 at 2:43 AM, Orlando L <oralrnr_at_gmail.com> wrote:

Hello all,
>  
>Are hints in queries considered bad, if so why. 
>
>Orlando. 
>  
>  
>
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Aug 15 2011 - 10:35:15 CDT

Original text of this message