Re: dbms_stats and number of buckets

Hi

After estimating with 100% I get 4 buckets instead of 5

So this is expected behaviour?

On Thu, Oct 19, 2017 at 10:31 AM, Jonathan Lewis < jonathan_at_jlcomp.demon.co.uk> wrote:

>
> I'd guess the "bad cardinality" is about 20,000 for PENDIENTE and
> PENDIENTE2.
>
> You either have to gather stats on just that column at estimate_percent =>
> 100 at the right time of day, or you can fake a histogram ( sample here for
> a numeric column - just use the chararray for your requirment:
> https://jonathanlewis.wordpress.com/2009/05/28/frequency-histograms/ ).
>
> 12c could handle this properly with its auto_sample_size.
>
> Regards
> Jonathan Lewis
>
> ________________________________________
> From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on
> behalf of Ls Cheng <exriscer_at_gmail.com>
> Sent: 19 October 2017 09:02:49
> To: Oracle Mailinglist
> Subject: dbms_stats and number of buckets
>
> Hi
>
> I am trying to generate histogram in one of columns in a message table,
> there are 5 differente values in the column as following
>
> select estado, count(*) from hl7.hl7_pending group by estado;
>
> ESTADO COUNT(*)
> -------------------------------- ----------
> OMITIDO 40155
> PENDIENTE 145
> ENVIADO 8801353
> PROCESADO 363466
> PENDIENTE2 227
>
>
> When I run dbms_stats as follows ( I tried different combination for
> mthod_opt)
>
> exec dbms_stats.gather_table_stats('HL7', 'HL7_PENDING', method_opt =>
> 'FOR ALL COLUMNS SIZE 1 FOR COLUMNS SIZE 10 ESTADO')
>
> exec dbms_stats.gather_table_stats('HL7', 'HL7_PENDING', method_opt =>
> 'FOR ALL COLUMNS SIZE skewonly')
>
>
> The number of buckets shows 3 only instead of 5
>
>
> select table_name, column_name, histogram, num_buckets
> from dba_tab_columns
> where table_name = 'HL7_PENDING'
> and column_name = 'ESTADO';
>
> TABLE_NAME COLUMN_NAME HISTOGRAM
> NUM_BUCKETS
> ------------------------------ ------------------------------
> --------------- -----------
> HL7_PENDING ESTADO FREQUENCY
> 3
>
> the column is varchar32 and NOT NULL
>
> And the query shows bad cardinality of course. This is RAC 11.2.0.4 with
> July 217 PSU. Any idea?
>
> Thanks
>
>
>
>
> [https://ipmcdn.avast.com/images/icons/icon-envelope-
> tick-round-orange-animated-no-repeat-v1.gif]<https://www.
> avast.com/sig-email?utm_medium=email&utm_source=link&
> utm_campaign=sig-email&utm_content=webmail> Virus-free. www.avast.com<
> https://www.avast.com/sig-email?utm_medium=email&utm_source=link&
> utm_campaign=sig-email&utm_content=webmail>
>

--
http://www.freelists.org/webpage/oracle-l