Re: Re: explain plan conundrum
Histograms are only used to refine the selectivity of a predicate. This in turn determines the cardinality estimate and various costs such as index access cost and then of course join costs (NL, sort-merge, and hash) and join cardinality. This ultimately will drive the decision whether a particular index access looks more promising (i.e. has a cheaper estimated cost than an FTS) and which join order together with which join method looks most promising - has the cheapest overall cost. It is all driven by the estimated costs, which are driven by the estimated cardinalities, which are driven by the estimated selectivities. BTW. Histograms on non-indexed columns also affect the cardinality estimate when they are used in the where clause, which is why it is not enough to collect histograms "for all indexed columns". Conversely, most likely not all indexed (much less ALL) columns require a histogram. Histograms, and the number of their buckets, need to be chosen on a column by column basis, not with a broad brush such as "for all columns" or "for all indexed columns". In the best case it is a waste of resources to gather them, but it easily also can be detrimental to the performance. At 10:04 AM 11/5/2003, you wrote: are histograms only used to determine whether to use an index or join type, not join order? Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.co -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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).
Re: Re: explain plan conundrum
The join order of an access plan - in the absence of any leading or ordered hints - is determined strictly like everything else by the CBO: the join order with the lowest estimated cost wins. And the selectivity and cardinality estimates play a big role in determining the cardinality and thus cost estimates. To answer your question "does oracle use histograms and distinctness in determining join order?" outright: Yes, but only indirectly: histograms and distinctness determine the cardinality -> therefore the cost estimates -> therefore the join order. And lastly, you can not compare the results, i.e. plans, of two different parses. Each is in its own world. At 10:04 AM 11/5/2003, you wrote: im not concerned about the type of join. Im strictly concerned about the join order. does oracle use histograms and distinctness in determining join order? The odd thing is that it chose a different join order on these tables earlier and on 'similiar' joins(ie large number of records and only 4 distinct values on the join column) oracle chooses the proper join 'order' > Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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).
Re: Re: explain plan conundrum
Well, you have 10053, lev 8 guesses to make. On 11/05/2003 12:04:26 PM, [EMAIL PROTECTED] wrote: > im not concerned about the type of join. Im strictly concerned about the join order. > does oracle use histograms and distinctness in determining join order? The odd thing > is that it chose a different join order on these tables earlier and on 'similiar' > joins(ie large number of records and only 4 distinct values on the join column) > oracle chooses the proper join 'order' > > > > From: Wolfgang Breitling <[EMAIL PROTECTED]> > > Date: 2003/11/05 Wed AM 11:49:26 EST > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > > Subject: Re: Re: explain plan conundrum > > > > However, since it is a join predicate, the histogram data can not be used. > > The CBO uses the density values of the join column(s) to derive the join > > selectivity. The density value of a column changes (from 1/num_distinct) > > when you collect a histogram. If you create a frequency histogram (aka > > value based histograms or equi-width histogram), which you most likely did > > for a field with only four distinct values using the default size of 75, > > the calculated density will be much lower than 1/num_distinct (i.e. less > > than 1/4 = .25) and therefore the join selectivity and ultimately the join > > cardinality will be unrealistically low, increasing the likelihood that the > > CBO will choose an NL join. > > > > At 04:49 PM 11/4/2003, you wrote: > > >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. > > > > Wolfgang Breitling > > Oracle7, 8, 8i, 9i OCP DBA > > Centrex Consulting Corporation > > http://www.centrexcc.com > > > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Wolfgang Breitling > > 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). > Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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).
Re: Re: explain plan conundrum
are histograms only used to determine whether to use an index or join type, not join order? > > From: Wolfgang Breitling <[EMAIL PROTECTED]> > Date: 2003/11/05 Wed AM 11:49:26 EST > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: Re: Re: explain plan conundrum > > However, since it is a join predicate, the histogram data can not be used. > The CBO uses the density values of the join column(s) to derive the join > selectivity. The density value of a column changes (from 1/num_distinct) > when you collect a histogram. If you create a frequency histogram (aka > value based histograms or equi-width histogram), which you most likely did > for a field with only four distinct values using the default size of 75, > the calculated density will be much lower than 1/num_distinct (i.e. less > than 1/4 = .25) and therefore the join selectivity and ultimately the join > cardinality will be unrealistically low, increasing the likelihood that the > CBO will choose an NL join. > > At 04:49 PM 11/4/2003, you wrote: > >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. > > Wolfgang Breitling > Oracle7, 8, 8i, 9i OCP DBA > Centrex Consulting Corporation > http://www.centrexcc.com > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Wolfgang Breitling > 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).
Re: Re: explain plan conundrum
im not concerned about the type of join. Im strictly concerned about the join order. does oracle use histograms and distinctness in determining join order? The odd thing is that it chose a different join order on these tables earlier and on 'similiar' joins(ie large number of records and only 4 distinct values on the join column) oracle chooses the proper join 'order' > > From: Wolfgang Breitling <[EMAIL PROTECTED]> > Date: 2003/11/05 Wed AM 11:49:26 EST > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: Re: Re: explain plan conundrum > > However, since it is a join predicate, the histogram data can not be used. > The CBO uses the density values of the join column(s) to derive the join > selectivity. The density value of a column changes (from 1/num_distinct) > when you collect a histogram. If you create a frequency histogram (aka > value based histograms or equi-width histogram), which you most likely did > for a field with only four distinct values using the default size of 75, > the calculated density will be much lower than 1/num_distinct (i.e. less > than 1/4 = .25) and therefore the join selectivity and ultimately the join > cardinality will be unrealistically low, increasing the likelihood that the > CBO will choose an NL join. > > At 04:49 PM 11/4/2003, you wrote: > >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. > > Wolfgang Breitling > Oracle7, 8, 8i, 9i OCP DBA > Centrex Consulting Corporation > http://www.centrexcc.com > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Wolfgang Breitling > 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).
Re: Re: explain plan conundrum
However, since it is a join predicate, the histogram data can not be used. The CBO uses the density values of the join column(s) to derive the join selectivity. The density value of a column changes (from 1/num_distinct) when you collect a histogram. If you create a frequency histogram (aka value based histograms or equi-width histogram), which you most likely did for a field with only four distinct values using the default size of 75, the calculated density will be much lower than 1/num_distinct (i.e. less than 1/4 = .25) and therefore the join selectivity and ultimately the join cardinality will be unrealistically low, increasing the likelihood that the CBO will choose an NL join. At 04:49 PM 11/4/2003, you wrote: 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. Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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).
RE: Re: explain plan conundrum
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 STATEM
Re: Re: explain plan conundrum
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 cont
RE: Re: explain plan conundrum
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 RowsBytes CostObject Node In/Out PStart > > PStop > > > > SELECT STATEMENT Optimizer Mode=CHOOSE 1 G 237 > > HASH JOIN 1 G 20G 237 > > INDEX FAST FULL SCANPK1 5 K 11 K3 > > 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
Re: Re: explain plan conundrum
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 http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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).
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 <[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 RowsBytes CostObject Node In/Out PStart > > PStop > > > > SELECT STATEMENT Optimizer Mode=CHOOSE 1 G 237 > > > > HASH JOIN 1 G 20G 237 > > INDEX FAST FULL SCANPK1 5 K 11 K3 > > > > 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).
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 RowsBytes CostObject Node In/Out PStart > PStop > > SELECT STATEMENT Optimizer Mode=CHOOSE1 G 237 > > HASH JOIN 1 G 20G 237 > INDEX FAST FULL SCAN PK1 5 K 11 K3 > > 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).
Re: explain plan conundrum
i figured it out. I use the ordered hint. Its actually a 3 table join and I took that out for simplicity in my question. I drove off a more selective join. Got it to run in 1 minute. i almost never have to use ordered hint > > From: <[EMAIL PROTECTED]> > Date: 2003/11/04 Tue PM 01:34:26 EST > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: explain plan conundrum > > 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 RowsBytes CostObject Node In/Out PStart > PStop > > SELECT STATEMENT Optimizer Mode=CHOOSE1 G 237 > > HASH JOIN 1 G 20G 237 > INDEX FAST FULL SCAN PK1 5 K 11 K3 > > TABLE ACCESS FULL TABLE2 366 K 4 M 231 > > > > -- > 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: <[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).
Re: explain plan conundrum
It's not 20 billion rows but 20 billion bytes. It's "only" 1 billion rows. The cartesion product of 5K rows and 366K rows is 1830M rows or 1.8G. If the join predicate is not very selective, .5 or .33 for example, that would yield an estimated join cardinality of 1G (after rounding). At 11:34 AM 11/4/2003, you 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 RowsBytes CostObject Node In/Out PStart PStop SELECT STATEMENT Optimizer Mode=CHOOSE 1 G 237 HASH JOIN 1 G 20G 237 INDEX FAST FULL SCANPK1 5 K 11 K3 TABLE ACCESS FULL TABLE2 366 K 4 M 231 Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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).
Re: explain plan conundrum
some more info. I checked distinct values in those two tables. and there are 366,000 records in one table and 5,000 records in another however, the column that is joining to only has 4 distinct values. I added a bitmap index to the columns and its using it on the smaller table. Im willing to be that is the problem. any possible way to tune a join against such few distinct values > > From: <[EMAIL PROTECTED]> > Date: 2003/11/04 Tue PM 01:34:26 EST > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: explain plan conundrum > > 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 RowsBytes CostObject Node In/Out PStart > PStop > > SELECT STATEMENT Optimizer Mode=CHOOSE1 G 237 > > HASH JOIN 1 G 20G 237 > INDEX FAST FULL SCAN PK1 5 K 11 K3 > > TABLE ACCESS FULL TABLE2 366 K 4 M 231 > > > > -- > 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: <[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).
