automated statistics gathering failed in Oracle

Sometimes automated statistics job failed on large table or table partition segments. In that case , you have to find out the automatic stats job scheduling , its running time and failed times and how to resolve them .

By default , automatic stats job runs every weekday night between 22:00 hours and 06:00 hours.You can find out these information  by below queries :-

SELECT * FROM dba_autotask_client WHERE client_name = 'auto optimizer stats collection';

SELECT window_group_name, window_name FROM dba_scheduler_wingroup_members;

SELECT window_name, start_time, duration FROM dba_autotask_schedule;

select * from dba_autotask_client;

SELECT client_name, job_status, job_start_time, job_duration, job_info

FROM dba_autotask_job_history

WHERE client_name like '%stats%'

ORDER BY job_start_time;

select Owner, job_name, program_name, schedule_name, schedule_type,

failure_count, last_start_date , comments

from dba_scheduler_jobs;

select window_name, repeat_interval,duration, window_priority,

next_start_date, last_start_date, Comments

from dba_scheduler_windows;

select operation||decode(target,null,null,'-'||target) operation

      ,to_char(start_time,'YY-MM-DD HH24:MI:SS.FF4') start_time

      ,to_char(end_time,'YY-MM-DD HH24:MI:SS.FF4') end_time

from dba_optstat_operations

order by start_time desc

If Automated Statistics Gathering working , then check :-

SELECT owner, table_name, last_analyzed FROM all_tables ORDER BY last_analyzed DESC NULLS LAST; --Tables.

SELECT owner, index_name, last_analyzed FROM all_indexes ORDER BY last_analyzed DESC NULLS LAST; -- Indexes.

Then check :-

select * from sys.dba_tab_modifications

where table_owner not in ('SYS','SYSTEM')

order by timestamp;

After that check the DBA_TAB_STATS_HIST table to see the history of stats stored  :-

select owner,table_name, partition_name,stats_update_time

from dba_tab_stats_history

where table_name='<tb_name>'  and owner=<user_name> ORDER BY stats_update_time DESC;

To resolve this issue , please  follow below instructions :-

sql> grant analyze any to <username> ;

sql> SELECT TABLE_NAME, PARTITION_NAME, INSERTS, UPDATES, DELETES, TIMESTAMP, TRUNCATED FROM user_tab_modifications ;

sql> EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO ;

sql> SELECT TABLE_NAME, PARTITION_NAME, INSERTS, UPDATES, DELETES, TIMESTAMP, TRUNCATED FROM user_tab_modifications ;

sql> EXEC DBMS_STATS.GATHER_TABLE_STATS(OwnName=>'<username>', TabName=>'<tb_name>', Degree=>DBMS_STATS.AUTO_DEGREE, Granularity=>'AUTO') ;

sql> SELECT table_name object_name, partition_name, num_rows, last_analyzed, stale_stats FROM user_tab_statistics WHERE table_name = '<tb_name>';

Note :- Run the above steps before automated statistics gathering job .