Re: Hints in Practice

"DA Morgan" <damorgan_at_psoug.org> wrote in message news:1156354006.563938_at_bubbleator.drizzle.com...
> Bob Jones wrote:
>
>>>> That is correct. Oracle is about the only database I know uses explicit
>>>> hints. That is not really a vote of confidence in their optimizer.
>
>>> <RANT WARNING>
>>> By your logic the DBMS_LOCK package would be a vote of no-confidence in
>>> the provided locks, global temporary, iot, and partitioning a vote of
>>> no-confidence in their table structures, and RAC a vote of no-confidence
>>> in the stability of the product: An interesting way to approach a vendor
>>> that provides a robust product and gives the developer and/or DBA the
>>> CHOICE as to whether they wish to grind out slightly better scalability
>>> and performance.
>>>
>>
>> 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.

>>>> 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?

>>> I can not subscribe to gutting those features that make a product best
>>> of breed just because they are not also present in the worst of breed.
>>> </RANT WARNING>
>>
>> Well, if injecting hints into SQL is considered the best of the breed
>> feature.
>
> 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.

>>>> It is almost always possible to build good performing queries without
>>>> hints.
>>> With this I agree. I hardly ever use hints but then that is the advice
>>> given by the experts that know Oracle far better than I ever will.
>>> --
>>
>> Then why all the rants?
>
> 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?

> 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. Received on Wed Aug 23 2006 - 20:47:29 CDT