Re: Hints

From: Jeremy Schneider <jeremy.schneider_at_ardentperf.com>
Date: Tue, 16 Aug 2011 09:09:57 -0500
Message-ID: <4E4A7A35.5000403_at_ardentperf.com>



Hm, I have another angle on this question.

Most people here seem to be referencing a sparing use of hints... but what about the practice of locking a plan in with a "full set" of hints?

Honestly, statistics management is a bit of a bear in 10g. And I'm not sure that many 11g shops are really using the new statistics management features yet. For example: one large 10g warehouse environment where I recently worked gathers stats weekly. We have some very complex SQL statements (10 or more tables, functions, IN/EXISTS and repetitive/correlated filters) where the CBO often gets close costs for good plans and very bad plans, and a overnight slight stats change could possibly convert a 2 hour query to 12 hours.

At least with 11gR2 SQL Plan Management, we could tell oracle to stick with old plans until changes are verified... though I don't really know if this is being successfully used for major deployments in the field yet.

So it seems to me that in this particular environment, it might not be a bad idea to follow some guidelines like this: 1. Whenever working with a "problem" query, the first step is always to remove all hints.
2. While working, sparing use of appropriate hints is permissible - but only with a justification about why this is preferred over addressing the underlying cause.
3. After a strong plan is found for a "problem" query, get the full set of hints with dbms_xplan.display_cursor('<sql_id>', null, 'outline') to "lock in" the plan.

I'm curious about your thoughts on using hints heavily in this fashion.

-J

On 8/15/2011 10:35 AM, Kellyn Pot'vin wrote:
> 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
> <mailto:oralrnr_at_gmail.com>> wrote:
>
> Hello all,
>
> Are hints in queries considered bad, if so why.
>
> Orlando.
>
>
>
>
>
>

-- 
http://www.ardentperf.com
+1 312-725-9249

Jeremy Schneider
Chicago


--
http://www.freelists.org/webpage/oracle-l
Received on Tue Aug 16 2011 - 09:09:57 CDT

Original text of this message