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 10:26:46 -0700
Message-ID: <1156354006.563938@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.

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

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

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

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.

-- 
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 - 12:26:46 CDT

Original text of this message

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