RE: RE: About an old hint question

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.

SF

>----- ------- Original Message ------- -----
>From: "Mark Leith" <mark_at_cool-tools.co.uk>
>To: Multiple recipients of list ORACLE-L
><ORACLE-L_at_fatcity.com>
>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.
>
>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: Stephane Faroult
  INET: sfaroult_at_oriolecorp.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).