12c - Histogram-Buckets Increased in

Oracle significantly increased the number of histogram buckets in 12c to 2048, that is an increase by 8 folds.

In this blog, let's just prove that - 

I will use max_histogram table and insert 2048 rows into it

SQL> create table max_histograms (id number, v_number number);

Table created.

Let's now insert 2048 rows into it 

SQL>  begin
  for i in 1..2048
  loop
  insert into max_histograms values (1,i);
  end loop;
  commit;
  end;
/  

PL/SQL procedure successfully completed.

now let's gather stats with 2048 as the bucket size 

SQL> exec DBMS_STATS.GATHER_TABLE_STATS('SCOTT','MAX_HISTOGRAMS', method_opt => 'FOR COLUMNS v_number size 2048');

PL/SQL procedure successfully completed.

Wow!, it captured with bucket size 2048 (try it in 11g)

Now let's prove it

SQL> select count(*), max(endpoint_value), min(endpoint_value) 

from dba_histograms

where owner = 'SCOTT' and table_name = 'MAX_HISTOGRAMS';  

  COUNT(*) MAX(ENDPOINT_VALUE) MIN(ENDPOINT_VALUE)

---------- ------------------- -------------------

      2048  2048 1

if you want to see all the buckets individually and their values you can use the below query

select owner, table_name, column_name, column_name, endpoint_value, endpoint_repeat_count

from dba_histograms

where owner = 'SCOTT' and table_name = 'MAX_HISTOGRAMS'

order by 1,5;

Note - I have removed sql prompts at places so that it is easy in case you want to just copy/paste to see this in your machine.