Gather_schema_stats with GATHER AUTO don't create histograms for bulk loaded tables

Background :

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

Oracle 12 creates automatically statistics for a table in case of a bulk load. We use gather_schema_stats to gather statistics for specific tables in a schema. gather_schema_stats with GATHER AUTO don't create histograms for bulk loaded tables. When we use gather_table_stats with GATHER AUTO histograms are created. Is it a bug that gather_schema_stats don't create histograms?

In this document (12c New option value of parameter OPTIONS of DBMS_STATS.GATHER_TABLE_STATS (Doc ID 2295873.1)) stands that it should work gather_table_stats, but from my point of view gather_schema_stats should work similar to gather_table_stats.

 

Test Case:

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

-- create table and a mat view

SQL> create table test_master

(

   a number,

   b number,

   c number

);

 

SQL> Table Created

 

SQL> insert into test_master values (1,2,3);

SQL> insert into test_master values (3,4,5);

SQL> insert into test_master values (3,4,5);

SQL> insert into test_master values (3,4,5);

SQL> insert into test_master values (3,4,5);

SQL> insert into test_master values (3,4,5);

SQL> insert into test_master values (3,4,5);

SQL> insert into test_master values (3,4,5);

SQL> insert into test_master values (3,4,5);

SQL> insert into test_master values (3,4,5);

commit;

 

SQL> create materialized view test_mv refresh on demand as select * from test_master;

 

-- complete refresh

SQL>

begin

  dbms_mview.refresh (list    => 'TEST_MV'

                    , method  => 'C'

                    , atomic_refresh => false); 

end;

/

 

-- access mat view so that gather statistics create histogram  

SQL> select * from test_mv where c=2;

 

-- gather statistics

SQL> begin

  dbms_stats.gather_table_stats(null, 'TEST_MV', options=>'GATHER AUTO');

end;

/

 

-- check whether histogram is there

SQL> SELECT histogram, to_char(last_analyzed, 'DD.MM.YYYY HH24:MI:SS') FROM user_tab_col_statistics where table_name='TEST_MV' and column_name='C';

-- Result: FREQUENCY -> Histogram was created

 

 

-- mat view complete refresh

-- -> Oracle 12 is used so bulk load creates new statistics

SQL> begin

  dbms_mview.refresh (list    => 'TEST_MV'

                    , method  => 'C'

                    , atomic_refresh => false); 

end;

/

 

SQL> SELECT histogram, to_char(last_analyzed, 'DD.MM.YYYY HH24:MI:SS') FROM user_tab_col_statistics where table_name='TEST_MV' and column_name='C';

-- NONE               05.06.2018 11:26:05

-- correct as online statistic gathering don't create histograms 

 

-- Use gather_schema_stats with GATHER AUTO

-- Should create histograms

declare

  out_list dbms_stats.objecttab; 

  i_filter_lst DBMS_STATS.OBJECTTAB := DBMS_STATS.OBJECTTAB();

  l_user varchar2(100);

begin

  select user into l_user from dual;

  i_filter_lst.extend(1);

  i_filter_lst(1).ownname := l_user;

  i_filter_lst(1).objname := 'TEST_MV';

  dbms_stats.gather_schema_stats(ownname          => l_user

                                   , estimate_percent => dbms_stats.auto_sample_size

                                   , degree           => null

                                   , granularity      => 'AUTO'

                                   , cascade          => dbms_stats.auto_cascade

                                   , options          => 'GATHER AUTO'

                                   , obj_filter_list  => i_filter_lst

                                   , objlist          => out_list

                                   ); 

end;

/

 

SQL> SELECT histogram, to_char(last_analyzed, 'DD.MM.YYYY HH24:MI:SS') FROM user_tab_col_statistics where table_name='TEST_MV' and column_name='C';

-- NONE               05.06.2018 11:26:05

-- Still no histogram is there --> Error

 

-- Using normal gather_table_stats

SQL> begin

  dbms_stats.gather_table_stats(null, 'TEST_MV', options=>'GATHER AUTO');

end;

/

SQL> SELECT histogram, to_char(last_analyzed, 'DD.MM.YYYY HH24:MI:SS') FROM user_tab_col_statistics where table_name='TEST_MV' and column_name='C';

-- FREQUENCY   05.06.2018 11:29:51

-- Histogram is created

 

 

-- drop materialized view test_mv;

-- drop table test_master purge;

 

 

Solution :

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

SQL> alter session set "_optimizer_gather_stats_on_load" =FALSE;

 

SQL> <<<<execute the testcase which is not creating histogram>>>>>>>>>>>