First I would take a look at the papers posted at Wolfgang Breitling's site http://www.centrexcc.com/ Next, try and compare the number of rows the optimizer expects to bring back at each step (cardinality as seen in explain plan), to the actual number returned (rows as seen in sql_trace=true -- tkprof; or manually do each part of the query, but be careful because of the filtering). Focus in on a discrepency between these two methods. That is where the optimizer is being fooled. It might be because of bad statistics, skewed data, init.ora settings, ...
Henry -----Original Message----- [EMAIL PROTECTED] Sent: Tuesday, November 04, 2003 3:29 PM To: Multiple recipients of list ORACLE-L 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 <[EMAIL PROTECTED]> > Date: 2003/11/04 Tue PM 02:09:30 EST > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > 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 > > --- [EMAIL PROTECTED] 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: <[EMAIL PROTECTED] INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: Henry Poras INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
