Re: Hints
Date: Wed, 17 Aug 2011 10:53:46 -0500
Message-ID: <CAHSa0M2bBdQ5f4ofa4UHQTAN0QTvZQBvpt-PQFD_qN7nLC4oeg_at_mail.gmail.com>
"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-lReceived on Wed Aug 17 2011 - 10:53:46 CDT