Re: Hints
- From: Jeremy Schneider <jeremy.schneider@xxxxxxxxxxxxxx>
- To: kellyn.potvin@xxxxxxxxx
- Date: Tue, 16 Aug 2011 09:09:57 -0500
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@xxxxxxxxx>
> *To:* oralrnr@xxxxxxxxx
> *Cc:* oracle-l@xxxxxxxxxxxxx
> *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@xxxxxxxxx
> <mailto:oralrnr@xxxxxxxxx>> wrote:
>
> Hello all,
>
> Are hints in queries considered bad, if so why.
>
> Orlando.
>
>
>
>
>
>
--
http://www.ardentperf.com
+1 312-725-9249
Jeremy Schneider
Chicago
Other related posts:
