Re: Hints
Date: Wed, 17 Aug 2011 09:24:07 +0300
Message-ID: <OF54E509C5.D1788035-ONC22578EF.001F8765-C22578EF.00232B41_at_seb.lt>
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
- to solve issues "why [a particular] index is not used" and
- 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
Received on Wed Aug 17 2011 - 01:24:07 CDT