Re: Hints in Practice

From: Bob Jones <email_at_me.not>
Date: Wed, 23 Aug 2006 15:22:03 GMT
Message-ID: <vo_Gg.14317$9T3.2514@newssvr25.news.prodigy.net>

"DA Morgan" <damorgan_at_psoug.org> wrote in message news:1156309585.128739_at_bubbleator.drizzle.com...
> Bob Jones wrote:

>> "Robert Klemme" <shortcutter_at_googlemail.com> wrote in message 
>> news:4l0scsFe4upiU1_at_individual.net...
>>> All,
>>>
>>> from what I read and from my limited personal experience, hints are 
>>> superfluous most of the time.  The general recommendation seems to be 
>>> "make sure your schema is properly done, create appropriate indexes, 
>>> make sure statistics are up to date and let the CBO do its work".  Hints 
>>> seem to be useful only in very rare cases (bugs?) where the CBO doesn't 
>>> come up with an appropriate plan.  Is this a correct summary of the 
>>> situation or did I miss something?
>>>
>>> Thanks
>>>
>>> robert
>>
>> 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.

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

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

>> 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? Received on Wed Aug 23 2006 - 10:22:03 CDT