FreeLists / oracle-l / RE: Histograms - SIZE clause & num_buckets anomaly

["Lex de Haan" <lex.de.haan@xxxxxxxxxxxxxx>]

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 http://www.freelists.org/archives/oracle-l/
FAQ is at http://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 http://www.freelists.org/archives/oracle-l/
FAQ is at http://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 http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------