RE: SQL statement with hints or without hints

From: John Kanagaraj <john.kanagaraj_at_hds.com>
Date: Fri, 12 Apr 2002 11:08:32 -0800
Message-ID: <F001.0044379A.20020412110832@fatcity.com>

Raj,

Keep in mind that the CBO will be defaulted whenever the following is present:

· Partitioned tables and indexes
· Index-organized tables
· Reverse key indexes
· Function-based indexes
· SAMPLE clauses in a SELECT statement
· Parallel execution and parallel DML (i.e. presence of DEGREE on

Tables/Indexes)

· Star transformations
· Star joins
· Extensible optimizer
· Query rewrite (materialized views)
· Progress meter
· Hash joins
· Bitmap indexes and bitmap join indexes
· Partition Views
· Index skip scans

*And*, when this now-invoked CBO detects objects without statistics, it 'guesstimeates' them based on some ridiculous defaults, leading to absolutely horrible paths. Methinks that is what you are observing....

Looking at the subject lines for papers at IOUG, this subject is going to be dealt with severely :)

John Kanagaraj
Oracle Applications DBA
DBSoft Inc
(W): 408-970-7002

Grace - Getting something we don't deserve Mercy - NOT getting something we deserve

Click on 'http://www.needhim.org' for Grace and Mercy that is freely available!

• The opinions and statements above are entirely my own and not those of my employer or clients **

> -----Original Message-----
> From: Jamadagni, Rajendra [mailto:Rajendra.Jamadagni_at_espn.com]
> Sent: Friday, April 12, 2002 8:38 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: SQL statement with hints or without hints
>
>
> I am not sure, outlines do require CBO (me thinks) and we are
> not there yet.
> Also there is a general resistance around here for that. Now
> that you have
> asked me, I will probably take it up to my manager once we move to CBO
> completely (sometime before Universe stops expanding judging
> by current
> support from within our group to migrate to CBO).
>
> Raj
> ______________________________________________________
> Rajendra Jamadagni MIS, ESPN Inc.
> Rajendra dot Jamadagni at ESPN dot com
> Any opinion expressed here is personal and doesn't reflect
> that of ESPN Inc.
>
> QOTD: Any clod can have facts, but having an opinion is an art!
>
>
> -----Original Message-----
> Sent: Friday, April 12, 2002 10:18 AM
> To: 'Jamadagni, Rajendra'; 'ORACLE-L_at_fatcity.com'
>
>
> Raj, out of curiosity: Why are outlines a no-no? Please
> elaborate. I
> haven't used them and would be interested in your opinion.
>
> LK
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: John Kanagaraj
  INET: john.kanagaraj_at_hds.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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 Fri Apr 12 2002 - 14:08:32 CDT