Re: dbms_stats and number of buckets

From: Stefan Koehler <contact_at_soocs.de>
Date: Thu, 19 Oct 2017 10:09:59 +0200 (CEST)
Message-ID: <401500577.5120.1508400599626_at_ox.hosteurope.de>


Hello Cheng,
welcome to the "instability problem" with histograms and its 11g implementation.

Here is more information about it:

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher Website: http://www.soocs.de
Twitter: _at_OracleSK

> Ls Cheng <exriscer_at_gmail.com> hat am 19. Oktober 2017 um 10:02 geschrieben:
>
> 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

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Oct 19 2017 - 10:09:59 CEST

Original text of this message