Date: Thu, 18 Aug 2011 09:59:10 +0300
>Wonder how the optimizer works in peoplesoft system with no foreign keys.
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-lReceived on Thu Aug 18 2011 - 01:59:10 CDT