RE: About an old hint question

Just out of curiosity, does anyone think that stored outlines could be used in cases like this?

Just a thought.

Mark

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

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mark Leith
  INET: mark_at_cool-tools.co.uk

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