Hi Charudatta, I don't think this needs fixing -- I like to consider it as the price to pay for enhanced statistics gathering performance. and as you found out, there is an easy workaround ;-) just to explain this a little further: suppose you are somewhere in the middle of gathering histogram statistics, and you started with an height-based approach in mind. in that situation, you cannot simply switch to a frequency histogram approach; you already lost some precision, so you would have to start all over... I agree that frequency histograms give the ultimate information about a column population, compared with height-based histograms, but on the other hand the performance should be "comparable" -- that is, the height-based histogram with a reasonably high number of buckets shouldn't give bad execution plans. The reason this is not documented (I guess) is that it is rather unpredictable indeed, especially if you choose a size pretty close to the number of distinct values. Kind regards, Lex. --------------------------------------------- visit my website at http://www.naturaljoin.nl --------------------------------------------- -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Charudatta Joshi Sent: Monday, August 02, 2004 16:27 To: oracle-l@xxxxxxxxxxxxx Subject: RE: Histograms - SIZE clause & num_buckets anomaly Thanks for the info, Lex. BTW, how come SKEWONLY option created requisite number of buckets? Having experienced it's (adverse) effect on execution plan, I AM surprised that this isn't a well known and documented fact. I had searched Web, Metalink, AskTom, FMs, and could not pick any mention of this. It may be that: 1. I hadn't searched carefully enough. 2. Not many people pay attention to histogram creation. 3. Not too many shops have D/W environments (which would make DBAs consider histograms). 4. I am overestimating the impact of the issue. Must be one or more of the above. I still think this needs to be fixed (has it been, in 10G?) i.e. instead of choosing some number more than 50%, there should be a better way. Anyways, it was nice to get the doubt solved. Thanks & regards, Charu. -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Lex de Haan Sent: Monday, August 02, 2004 8:05 PM To: oracle-l@xxxxxxxxxxxxx Subject: RE: Histograms - SIZE clause & num_buckets anomaly the reason of this behavior is a consequence of dbms_stats optimization. in the old (Oracle8) days I believe the ANALYZE command scanned the segment twice -- so it knew in advance the precise number of distinct values. in order to enhance optimizer statistics collection, dbms_stats scans the segment only once. that is, the exact number of distinct column values is not known at scanning time. hope this helps, kind regards, Lex. --------------------------------------------- visit my website at http://www.naturaljoin.nl --------------------------------------------- -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Charudatta Joshi Sent: Monday, August 02, 2004 14:41 To: oracle-l@xxxxxxxxxxxxx Subject: RE: Histograms - SIZE clause & num_buckets anomaly Thanks Wolfgang, Yes, doubling the size parameter created frequency based histograms. Seems very weird though. BTW, I am seeing exactly the same behaviour in 8.1.7.4 environment. And unless one is aware of this quirk, this creation of height-based histograms instead of f.b. histograms will affect the query execution plans adversely, isn't it? Hasn't any bug been raised on this? Or is some kind of 'feature'? Good point about using analyze to delete related index statistics. Thanks & Regards, Charu. -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Wolfgang Breitling Sent: Monday, August 02, 2004 6:35 PM To: oracle-l@xxxxxxxxxxxxx Subject: Re: Histograms - SIZE clause & num_buckets anomaly I have noticed that too that Oracle 9 collects a height balanced histogram when you gather statistics with size=<num_distinct> instead of a frequency histogram as you'd expect (and as Oracle 8 did). At least sometimes. I haven't done enough testing to have recognized a pattern or rule. In my case, for example, I needed to crank up size to 38 or 39 (forgot exactly which one) (~ 50% more) in order to get the frequency histogram. Btw, if you use "analyze ... for columns owner size 11" you'll get your frequency histogram. Analyze hasn't changed from 8 to 9, but gather_table_stats has. Also, in order to delete statistics I still use analyze because delete_table_stats doesn't delete index stats. It doesn't matter in your case, I just thought I mention it. At 05:32 AM 8/2/2004, you wrote: >Hi all, > >Version:- 9.2.0.1 Enterprise Edition >OS: Win2K > > >** Check the number of buckets. >SQL> select num_distinct, num_buckets > 2 from user_tab_col_statistics > 3 where table_name = 'T4' and column_name = 'OWNER'; > >NUM_DISTINCT NUM_BUCKETS >------------ ----------- > 11 4 > >How come there are only four buckets created even though I specified size as >11? Could it be histogram compression? Regards Wolfgang Breitling Centrex Consulting Corporation www.centrexcc.com ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ********************************************************* Disclaimer: This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. ********************************************************* Visit us at http://www.mahindrabt.com ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- -- Binary/unsupported file stripped by Ecartis -- -- Type: text/x-vcard -- File: Lex de Haan.vcf ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ********************************************************* Disclaimer: This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited. ********************************************************* Visit us at http://www.mahindrabt.com ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- -- Binary/unsupported file stripped by Ecartis -- -- Type: text/x-vcard -- File: Lex de Haan.vcf ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------