Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Hints in Practice

Re: Hints in Practice

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 23 Aug 2006 21:33:45 -0700
Message-ID: <1156394025.514256@bubbleator.drizzle.com>


Bob Jones wrote:

>>> CHOICE? Do we have other options for those features? With hints, we
>>> certainly do.
>> We certainly do. There is no situation where using any of these is
>> required nor is it required that you use hints. The analogy holds.
>>

> 
> Like partitioning and table structures? I don't see any relevancy here. Are 
> there any other Oracle features do what RAC does? No. Your analogy holds, 
> only with a major stretch.

IBM and others do not have RAC yet they cobble together something that serves as a (in my opinion inadequate) substitute. You could do the same in Oracle if you had a need to do so. Data Guard for example has TAF. And one could fail over, however clumsily, to a standby maintained using Advanced Replication (what an oxymoron) or Streams/Change Data Capture.

I'll grant you it was a stretch but I do think a reasonable one.

>>>>> I would do anything to avoid using those hints. They are not portable
>>>>> and do not conform to any standard.
>>>> Thus you don't use array processing, don't use exception handling, don't
>>>> use PL/SQL packages, and heaven-forbid don't use any type of index other
>>>> than B*Tree and Bitmap.
>>> Again, do we have other choices? With PL/SQL, maybe.
>> Many other choices.
>>
>> DBMS_STATS.SET procedures, stored outlines, write better statements,
>> tune initialization parameters more often, the list is long.

> 
> Now you are just going 10 different directions. What standard does any of 
> these follow?

That's not the point. The point was there are alternatives to using hints if you want to force Oracle's CBO to use a specific plan. I wasn't going any further than that.

>> In some cases it does what is required. In that case yes. If injecting
>> hints is done as SOP (Standard Operating Procedure) then the usage is
>> no different then throwing 10% PCTFREE at every table.
>
> Those cases would be when the optimizer is not doing its job.

In the eyes of a good DBA or senior developer no optimizer known in computing today does a job so good that we wouldn't want to do a bit better.

I remember when I was happy with Lotus 1,2,3 recalcs that took minutes and we were thrilled with them. Today if a calculation on a GB database takes more than a fraction of a second I'm be typing autotrace.

That's a lie ... I type it all the time even when they are that fast.

>> Because you specifically equated Oracle making hints available to a lack
>> of confidence in, or ability of, the CBO. And I think that is just plain
>> wrong.

>
> If they are confident about the optimizer, why are hints necessary?

You are missing the point. SQL is a language that defines the result not the plan. We don't instruct the optimizer on what to do. We rely upon it to do the right thing.

But all to often the optimizer MUST make assumptions because there is no way it can possibly know everything about our data. It can peek at bind variables, it can look at cardinality, etc. if we have done a good job of collecting statistics. But you get into some situations where there is just no way to provide information to the CBO in the normal course of business except via a hint. And in those cases I'd rather have the tool available for use than sit wishing I could rewrite the SLA.
>> The CBO is certainly not perfect but it is, with equal certainty,
>> getting smarter with every release (notably excluding 8.1.6). Were it
>> not Oracle would not be producing the results we see in the real
>> world.

>
> Exactly, that's why we also need HBO - Hint Based Optimization.

I thought HBO was what you were complaining about just a paragraph ago.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Wed Aug 23 2006 - 23:33:45 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US