Oracle FAQ Your Portal to the Oracle Knowledge Grid

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

RE: About an old hint question

From: Jesse, Rich <>
Date: Thu, 15 May 2003 06:22:21 -0800
Message-ID: <>

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 Administrator           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 controversy?
> 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 hints.
> 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: Jesse, Rich

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 - 09:22:21 CDT

Original text of this message