Sunday, 2 February 2014

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 .





Unable to recompile invalid package bodies after data pump import

Sometimes after IMPORT , the user is unable to recompile invalid package bodies after data pump import. This issue arises when sys objects has been also imported .

To solve this issue , Please see the below view :-

select * from user_errors ;

You may  found some privileges are missing . Then grant them :- 

grant execute on SYS.DBMS_LOCK to <usr_name>;

grant execute on SYS.DBMS_FLASHBACK to <usr_name>;

grant execute on SYS.DBMS_SYS_SQL to <usr_name>;