Re: dbms_stats and number of buckets
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:
- https://blogs.oracle.com/optimizer/i-thought-the-new-autosamplesize-in-oracle-database-11g-looked-at-all-the-rows-in-a-table-so-why-do-i-see-a-very-small-sample-size-on-some-tables
- https://blogs.oracle.com/optimizer/how-does-autosamplesize-work-in-oracle-database-11g
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-lReceived on Thu Oct 19 2017 - 10:09:59 CEST