Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Re: explain plan conundrum

Re: Re: explain plan conundrum

From: Wolfgang Breitling <>
Date: Tue, 04 Nov 2003 12:49:33 -0800
Message-ID: <>

Could you please post the entire sql and plan and statistics of the tables and indexes so that we can comment on it rather than letting us guess on selective bits of the entire problem.

At 01:29 PM 11/4/2003, you wrote:
>everything is analyzed. For all indexes, for all indexed columns.
>I used analyze. its the same as dbms_stats, just not as robust. I use it
>when I dont feel like typing out dbms_stats.
>Are there optimizer parameters that help the optimizer determine join
>order? Ive never had to use the 'ordered' hint on the CBO before when
>everything is analyzed. The difference was huge. Ran for 2 hours and still
>going, with the hint ran in 45 seconds.
>im assuming there are some init.ora parameters that I should check out?
>Does oracle take into account 'distinctness' of the columns being joined?
>I have 1 table with 366,000 rows and another with 5,000 rows. the columns
>being joined have 4 distinct values each. However, the table with 366,000
>rows joins on its primary key to another table and that filters out enough
>rows that that join should go first. The optimizer made a bad decision.
>how do i analyze why it made a bad join order decision? hints like this
>are a stop gap fix.

Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation

Please see the official ORACLE-L FAQ:
Author: Wolfgang Breitling

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 Tue Nov 04 2003 - 14:49:33 CST

Original text of this message