Re: Re: explain plan conundrum

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.
>
> From: Yong Huang <yong321_at_yahoo.com>
> Date: 2003/11/04 Tue PM 02:09:30 EST
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Subject: Re: explain plan conundrum
>
> Hi, Ryan,
>
> Where's the 20 billion rows? There's 1 G rows and 20 G bytes.
>
> What are the values of NUM_ROWS in xxx_INDEXES for PK1 and xxx_TABLES for
> TABLE2? Did you analyze using ANALYZE command or DBMS_STATS?
>
> Yong Huang
>
> --- ryan_oracle_at_cox.net wrote:
> > I cant sql trace it now. I hae run statspack. this query is running now and I
> > dont want to run another copy with a trace on until this finishes, since I
> > dont want to suck up resources. Im at a loss as to where the 20 billion rows
> > comes from in this explain plan? Everything including the indexes are
> > analyzed.
> >
> > when the two tables involved have 36k and 5k rows involved.
> > looks like some form of cartesian join, but its not showing up in the plan.
> > The two tables are joined by a column.
> >
> > any place to look on this? I know I need the 10046 trace, but I cant get that
> > yet and it make take 12 hours to get it after this runs.
> >
> > select col1,
> > col2,
> > col3
> > from tab1
> > tab2
> > where tab1.col1 = tab2.col2;
> >
> >
> > Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
> >
> > SELECT STATEMENT Optimizer Mode=CHOOSE 1 G 237
> > HASH JOIN 1 G 20G 237
> > INDEX FAST FULL SCAN PK1 5 K 11 K 3
> > TABLE ACCESS FULL TABLE2 366 K 4 M 231
>
> __________________________________
> Do you Yahoo!?
> Protect your identity with Yahoo! Mail AddressGuard
> http://antispam.yahoo.com/whatsnewfree
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Yong Huang
> INET: yong321_at_yahoo.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: <ryan_oracle_at_cox.net
  INET: ryan_oracle_at_cox.net

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