The default SIZE of the histograms is different for ANALYZE and DBMS_STATS. Be sure you got what you wanted.
Also, as Wolfgang Breitling discusses in his papers, histograms don't deal with all kinds of skew. For example, if two fields in a table are dependent (they both show similar/identical skew), and if both are in your WHERE clause, the optimizer will assume they are independent and its cardinality guesstimate will be a lot lower than the actual number of rows returned (i.e. a table includes fields A & B. 90% of the data values in field A is the number 1, 90% of the data values in field B is number 1. A WHERE clause of 'WHERE A=1' will do just about the same amount of filtering as 'WHERE A=1 AND B=1' but the optimizer thinks the second clause is more selective). This problem can also happen with joins between tables. Henry -----Original Message----- Ryan Sent: Tuesday, November 04, 2003 6:49 PM To: Multiple recipients of list ORACLE-L the data is very skewed, but i included 'for all indexes' and for all indexed columns. doesnt that create histograms? or do i have the syntax wrong. what i really needed was histograms, Ill bet. ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, November 04, 2003 5:04 PM > 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ryan 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).
