Re: Hints

they are ... in the data model :) Actually, in the many different forms of it: physical, logical, conceptual (the list ends about here)

What I wanted to point out with "data model driven hint system" is that most of a time a developer constructs SQL to do nothing else than to fetch data according to relationships defined in the the data model. Almost allways the natural order defined in the data model is good enough as SQL execution plan. Almost allways developer battles the optimizer to follow exactly this order :)

And now if optimizer chooses the wrong plan even if statistics are accurate enough then hinting to follow the "natural" order defined in the data model does not seem a bad idea to me.
Better and sounder than tweaking with [hidden][global] parameters, statistics, patching, etc, etc.

Actually this whole discussion brings forward the property of relational database to have ad-hoc queries: one can join any table to any table using whatever conditions. Even not forseen in the original data model. This whole mess goes to optimizer. One can not expect optimizer to be perfect in such conditions.

For cases when predictable performance is expected (like OLTP) some discipline must exist: a data model designed in advance must be present. Foreign keys, etc might or might not exist physically in the database but the model must be there in any form.

p.s. As for optimizer and foreign keys then it considers only uniqueness. F-keys have no effect on CBO whatsoever. Correct me if I am wrong.

Please consider the environment before printing this e-mail

                                                                                                                                                   
  From:       Ram Raman <veeeraman_at_gmail.com>                                                                                                      
                                                                                                                                                   
  To:         Laimutis.Nedzinskas_at_seb.lt                                                                                                           
                                                                                                                                                   
  Cc:         oracle-l_at_freelists.org                                                                                                               
                                                                                                                                                   
  Date:       2011.08.17 20:41                                                                                                                     
                                                                                                                                                   
  Subject:    Re: Hints                                                                                                                            
                                                                                                                                                   

"then I vote to use hints to tell the optimizer to follow the relationships defined in the data model"
Wonder how the optimizer works in peoplesoft system with no foreign keys.

On Wed, Aug 17, 2011 at 1:24 AM, <Laimutis.Nedzinskas_at_seb.lt> wrote:.

      Agree with that. OLTP and DSS are different and need different
      handling.

      My guess (someone younger can launch a research) that absolute
      majority of
      hints is used

      a) to solve issues "why [a particular] index is not used" and

      b) to tune OLTP type of queries (e.g. queries which response time
      must be
      way under a second)

      I doubt very much that many can tune DSS queries with HASH, bitmap,
      transformation hints appropriately.

      Now as far as OLTP is concerned then fortunately we have a system - a
      data
      model.

      A data model provides one with a network of entities, interconnected
      with
      relationships. Normally those relationships are implemented as a)
      foreign
      keys b) with indexes.

      Most of OLTP queries does nothing else than "traces" those
      relationships.
      BTW, it reminds very much of network data model with relationships
      inherent
      part of it.

      For example:

      to select from Employees, Departments by employee_id = :b1 one
      expects
      optimizer to exactly "trace" this relationship: first select the
      employee
      then find ones department.
      However a simple condition like "WHERE dept_id = 101" can drive the
      optimizer crazy.

      In this situation if optimizer is in doubt (if machine has
      doubts :) ) then
      I vote to use hints to tell the optimizer to follow the relationships
      defined in the data model. Use a "data model driven hint system"

      10g (at least 10g) has a very good ability to hint not exactly the
      index
      name but columns. Those who know Tapio Lahdenmaki's work will
      understand
      why: optimizer can be hinted to use an indexed join on particular
      columns
      but if a better fat("covering") index becomes available later the
      optimizer
      will be able to choose that one instead and avoid unnecessary table
      "touches".

      my 2cents,
      Laimis N





      ---------------------------------------------------------------------------------


      Please consider the environment before printing this e-mail



       From:       Kellyn Pot'vin <kellyn.potvin_at_ymail.com>

       To:         "jeremy.schneider_at_ardentperf.com" <
      jeremy.schneider_at_ardentperf.com>

       Cc:         "cicciuxdba_at_gmail.com" <cicciuxdba_at_gmail.com>, "
      oralrnr_at_gmail.com" <oralrnr_at_gmail.com>, "oracle-l_at_freelists.org"
                   <oracle-l_at_freelists.org>

       Date:       2011.08.16 20:24

       Subject:    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_at_gmail.com>; "oracle-l_at_freelists.org" <oracle-l_at_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

--
http://www.freelists.org/webpage/oracle-l