Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: About an old hint question
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
Rich Jesse System/Database Administrator rich.jesse_at_qtiworld.com Quad/Tech International, Sussex, WI USA
> -----Original Message-----
> From: Mladen Gogala [mailto:mgogala_at_adelphia.net]
> 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
> _LIKE_WITH_BIND_AS_EQUALITY =
> > 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: http://www.orafaq.net -- Author: Jesse, Rich INET: Rich.Jesse_at_qtiworld.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (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