"DA Morgan" <damorgan_at_psoug.org> wrote in message
news:1156394025.514256_at_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 Thu Aug 24 2006 - 21:00:03 CDT
