Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: RE: About an old hint question

RE: RE: About an old hint question

From: Stephane Faroult <>
Date: Thu, 15 May 2003 08:37:32 -0800
Message-ID: <>

Stored outline being a collection of very precise and specific hints, I would dread them more than hints ... I fully agree with Mladen's 'broadsword' approach, and I am fully comfortable with ALL_ROWS or FIRST_ROWS (which I'd use preferably to USE_HASH or USE_NL to reach the same goal). I feel reasonably comfortable with ORDERED and NO_MERGE, and uneasy with anything naming an index specifically. Which of course doesn't mean 'NEVER ever ....' Broad directions and status of intent are I believe acceptable. But Rich is right to underscore the 'optimize NOW' danger. Most applications developed today are likely to outlive 3 or 4 major Oracle versions and generations of hardware.


>----- ------- Original Message ------- -----
>From: "Mark Leith" <>
>To: Multiple recipients of list ORACLE-L
>Sent: Thu, 15 May 2003 07:51:02
>Just out of curiosity, does anyone think that
>stored outlines could be used
>in cases like this?
>Just a thought.
>-----Original Message-----
>Sent: 15 May 2003 15:22
>To: Multiple recipients of list ORACLE-L
>Because the hint can force a query's explain plan
>to be "correct" now, but
>horrible as the stats on the objects change with
>time. We've been burned a
>few times by the hint "patch". Unfortunately,
>trying to justify the time it
>would take for the whole department to stop what
>they're doing for a few
>weeks and retest all apps after a change in stats
>collection versus adding a
>hint here and a hint there is tough.
>Rich Jesse System/Database
> Quad/Tech
>International, Sussex, WI USA
>> -----Original Message-----
>> From: Mladen Gogala []
>> Sent: Wednesday, May 14, 2003 10:27 PM
>> To: Multiple recipients of list ORACLE-L
>> Subject: Re: About an old hint question
>> Thanks, Wolfgang! K. Gopalakrishnan also pointed
>that to me.
>> I must say
>> that I prefer parameters to a very broad sword
>and that I'm perfectly
>> content with hints. Why do hints cause such a
>> They're only
>> a fine tuning tool which are needed only when
>fine tuning is needed.
>> Another instance of need for hints is when one of
>the tables involved
>> in a join is in a hotspot and even if a full
>table scan would normally
>> be the fastest way to read it, you want to use
>fast full index scan,
>> just
>> to avoid it. Optimizer has no way of knowing
>about misteries of disks,
>> universe and everything. For such cases, we need
>> On 2003.05.14 22:06 Wolfgang Breitling wrote:
>> > If you have many of those, try setting
>> > TRUE (can be done with alter session) and
>potentially increase the
>> > selectivity of the column by reducing density
>by an order of
>> > magnitude or two.
>> >
>> > At 04:11 PM 5/14/2003 -0800, you wrote:
>> >> [...]
>> >
>> >> The contention issue are the bind variables
>from PRO*C
>> programs where
>> >> things "LIKE :S" are usually resolved by using
>a full table scan,
>> >> even if :S is of the form 'ABC%' in which case
>the query should, as
>> >> we all know, use an index. The only solution
>that I have for the
>> >> problem
>> >> is to put a dreaded hint in the select.
>> >
>> > [...]
>> > Wolfgang Breitling

Please see the official ORACLE-L FAQ:
Author: Stephane Faroult

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Thu May 15 2003 - 11:37:32 CDT

Original text of this message