Re: Hints

I'm fully aware of a similar situation to Jeremy's where complex/unique design and business/revenue demands, only allow for so much time to ensure stability for the CBO, but it MUST be stable and consistent. Due to the unique life span of these environments, the best solution was to lock stats on most of the tables after their initial build, (no more data would be added or removed from the tables), disabling many CBO dynamic features for views/sampling and have complex hints that help the database take the best path eliminating the choices that might seem to be the best by the cost, but end up "costing" in the end in heavy IO.  This arena pushes IO to the limits and avoiding it is my main goal when performance tuning in this environment.

These steps I have taken in this environment ONLY, where the OLTP's and other DSS systems involved, I take a much more traditional approach and performance challenges are in a complete different direction.  It all depends on the environment and what you are using Oracle for.  If you are stuck with a front end application that may not utilize Oracle as effectively as you would like, you may find yourself in the same place I was, a DBA shaking her head and doing the exact things she said "I would never do that in a database environment!"  Oracle offers us the options with hints, SQL outlines, SQL Profiles, statistics features, etc. so when we find ourselves up a creek without a paddle, we can build our own paddle...

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


________________________________
From: Jeremy Schneider <jeremy.schneider_at_ardentperf.com>
To: kellyn.potvin_at_ymail.com
Cc: "cicciuxdba_at_gmail.com" <cicciuxdba_at_gmail.com>; "oralrnr_at_gmail.com" <oralrnr@gmail.com>; "oracle-l@freelists.org" <oracle-l@freelists.org>
Sent: Tuesday, August 16, 2011 8:09 AM
Subject: Re: Hints


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> 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