I am using version- 11.2.0.4.0 of oracle. In our database there are few big tables which are range partitioned on a truncated(no time component) date column. We gather daily stats as below(granularity=partition) we are not using incremental feature, and as per documentation , it should rolled up the global stats automatically once all of the partitioned are analyzed. But in our database i see the global table level stats are showing as updated in DBA_TABLES but the column level stats are not getting updated, i verified from DBA_TAB_COL_STATISTICS the stats are old also the last_analyzed column showing old one. So i want to know, what must be causing this issue, is there any such setting which must be restricting the global column level stats to be rolled up?
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'USER1', TABNAME =>'T1', PARTNAME =>'part2', ESTIMATE_PERCENT => 25, GRANULARITY => 'PARTITION' ,CASCADE => TRUE, method_opt => 'FOR ALL COLUMNS SIZE 1', DEGREE => 12 );
To test and verify the scenario i created a sample table with three partition and collected the stats on partition level and i see that after all the partition are analyzed the global table and column level stats gets rolled up. so its working as expected, so why in my production database column level global stats are not getting updated? how can i debug this issue?
i have another question, as we are not using the incremental stats feature, so in current way of gathering partition level stats daily, do i really need to gather the global stats separately? as because i see currently we are not having histograms on any columns so my understanding was we wont need to gather the global stats separately and the automatic rolled up feature should work fine here which works well to calculate num_distinct,density etc correctly, want experts view on this?
SQL> CREATE TABLE t1
2 (
3 dt date,
4 x int,
5 y varchar2(25),
6 Z varchar2(25)
7 )
8 PARTITION BY RANGE (dt)
9 (
10 PARTITION part1 VALUES LESS THAN (to_date('13-mar-2017','dd-mon-yyyy')) ,
11 PARTITION part2 VALUES LESS THAN (to_date('14-mar-2017','dd-mon-yyyy')) ,
12 PARTITION part3 VALUES LESS THAN (to_date('15-mar-2017','dd-mon-yyyy')) ,
13 PARTITION OTHER VALUES LESS THAN (MAXVALUE)
14 )
15 /
Table created.
SQL> insert /*+ APPEND */ into t1
2 select to_date('12-mar-2017','dd-mon-yyyy'), rownum, rpad('*',25,'*'),'A'
3 from dba_objects;
74280 rows created.
Elapsed: 00:00:00.74
SQL> COMMIT;
Commit complete.
Elapsed: 00:00:00.22
SQL>
SQL> insert /*+ APPEND */ into t1
2 select to_date('13-mar-2017','dd-mon-yyyy'), rownum, rpad('*',25,'*'),'B'
3 from dba_objects;
74280 rows created.
Elapsed: 00:00:00.79
SQL> COMMIT;
Commit complete.
Elapsed: 00:00:00.33
SQL>
SQL> insert /*+ APPEND */ into t1
2 select to_date('14-mar-2017','dd-mon-yyyy'), rownum, rpad('*',25,'*'),'C'
3 from dba_objects;
74280 rows created.
Elapsed: 00:00:00.70
SQL> COMMIT;
Commit complete.
Elapsed: 00:00:00.20
SQL>
SQL> insert /*+ APPEND */ into t1
2 select to_date('15-mar-2017','dd-mon-yyyy'), rownum, rpad('*',25,'*'),'D'
3 from dba_objects;
74280 rows created.
Elapsed: 00:00:00.62
SQL> COMMIT;
Commit complete.
Elapsed: 00:00:00.12
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'USER1', TABNAME =>'T1', PARTNAME =>'part1', ESTIMATE_PERCENT => 25, GRANULARITY => 'PARTITION' ,CASCADE => TRUE, method_opt => 'FOR ALL COLUMNS SIZE 1', DEGREE => 12 );
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.48
SQL>
SQL>
SQL>
SQL> select table_name,num_rows,blocks,last_analyzed, global_stats from dba_tables where owner='USER1' and table_name='T1';
T1 NO
SQL> select table_name, column_name,num_distinct,low_value, high_value, density,num_nulls, histogram,global_stats
2 from DBA_tab_col_statistics where table_name like 'T1' and owner='USER1' ;
no rows selected
Elapsed: 00:00:00.25
SQL>
select partition_name,column_name,num_distinct,density, num_nulls ,low_value, high_value,global_stats
from dba_PART_COL_STATISTICS
where table_name like 'T1' and owner='USER1' ;
partition_name,column_name,num_distinct,density, num_nulls ,low_value, high_value,global_stats
OTHER DT NO
OTHER X NO
OTHER Y NO
OTHER Z NO
PART1 DT 1 1 0 7875030C010101 7875030C010101 YES
PART1 X 74368 1.34466437177281E-5 0 C102 C3082B4F YES
PART1 Y 1 1 0 2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A 2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A YES
PART1 Z 1 1 0 41 41 YES
PART2 DT NO
PART2 X NO
PART2 Y NO
PART2 Z NO
PART3 DT NO
PART3 X NO
PART3 Y NO
PART3 Z NO
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'USER1', TABNAME =>'T1', PARTNAME =>'part2', ESTIMATE_PERCENT => 25, GRANULARITY => 'PARTITION' ,CASCADE => TRUE, method_opt => 'FOR ALL COLUMNS SI
ZE 1', DEGREE => 12 );
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.39
SQL>
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'USER1', TABNAME =>'T1', PARTNAME =>'part3', ESTIMATE_PERCENT => 25, GRANULARITY => 'PARTITION' ,CASCADE => TRUE, method_opt => 'FOR ALL COLUMNS SI
ZE 1', DEGREE => 12 );
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.42
SQL>
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'USER1', TABNAME =>'T1', PARTNAME =>'OTHER', ESTIMATE_PERCENT => 25, GRANULARITY => 'PARTITION' ,CASCADE => TRUE, method_opt => 'FOR ALL COLUMNS SIZ
E 1', DEGREE => 12 );
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.39
SQL>
SQL>
SQL> select table_name,num_rows,blocks,last_analyzed, global_stats from dba_tables where owner='USER1' and table_name='T1'
2 ;
T1 298256 1888 5/7/2017 2:25:18 PM NO
select table_name, column_name,num_distinct,low_value, high_value, density,num_nulls, histogram,global_stats
from DBA_tab_col_statistics where table_name like 'T1' and owner='USER1' ;
T1 DT 4 7875030C010101 7875030F010101 0.25 0 NONE NO
T1 X 74724 C102 C3082B4F 1.33825812322681E-5 0 NONE NO
T1 Y 1 2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A 2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A 1 0 NONE NO
T1 Z 4 41 44 0.25 0 NONE NO
select partition_name,column_name,num_distinct,density, num_nulls ,low_value, high_value,global_stats
from dba_PART_COL_STATISTICS
where table_name like 'T1' and owner='USER1' ;
OTHER DT 1 1 0 7875030F010101 7875030F010101 YES
OTHER X 74720 1.33832976445396E-5 0 C106 C3082B4F YES
OTHER Y 1 1 0 2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A 2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A YES
OTHER Z 1 1 0 44 44 YES
PART1 DT 1 1 0 7875030C010101 7875030C010101 YES
PART1 X 74368 1.34466437177281E-5 0 C102 C3082B4F YES
PART1 Y 1 1 0 2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A 2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A YES
PART1 Z 1 1 0 41 41 YES
PART2 DT 1 1 0 7875030D010101 7875030D010101 YES
PART2 X 74500 1.34228187919463E-5 0 C102 C3082B47 YES
PART2 Y 1 1 0 2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A 2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A YES
PART2 Z 1 1 0 42 42 YES
PART3 DT 1 1 0 7875030E010101 7875030E010101 YES
PART3 X 74668 1.33926179889645E-5 0 C106 C3082B4E YES
PART3 Y 1 1 0 2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A 2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A2A YES
PART3 Z 1 1 0 43 43 YES