You can use below script to automate running stats when stale objects are found in the database...
here we are ignoring all the objects were their stats are locked.
In order to get updated stale objects... it is better you flush the monitoring info... as this updates the changes occurred recently....and tells whether any objects got staled or not. we are doing this manually because oracle has it own way/time of updating... if you have hard hitting tables then they might be stale but the monitoring info will not capturing it... by doing so we can have better stats for the tables needed.
you can easily automate the below code with you environment set.
exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO;
Script:-
CHANNELS=`cat /proc/cpuinfo | grep processor | wc -l`
===================
sqlplus -S sys /change@ABCD_SID as sysdba <<EOF >> $LOG
set linesize 200
set serveroutput on size 1000000
set feedback on echo off verify off
Prompt Running stats for Stale Table objects
declare
sql_stmt varchar2(256);
va_tab dba_tab_statistics.table_name%type;
cursor c_f is (select owner,table_name,global_stats,stale_stats from dba_tab_statistics
where stale_stats = 'YES' and table_name not like 'BIN$%' and STATTYPE_LOCKED is null);
begin
for i in c_f
loop
begin
dbms_stats.gather_table_stats(''||i.owner||'',''||i.table_name||'',
estimate_percent => 100,
degree => $CHANNELS,
method_opt => 'for all columns size auto', cascade => true);
dbms_output.put_line(sysdate || ': stats done for ' || ''||i.owner||'' ||'.' || ''||i.table_name||'');
exception
when others then
continue;
dbms_output.put_line(sysdate || ': failed to gather stats for ' || ''||i.owner||'' ||'.' || ''||i.table_name||'' || chr(10) || sqlerrm);
end;
end loop;
dbms_output.put_line(sysdate || ': #######################' || ': Stats done for Above Found Stale objects ' || ': #######################');
END;
/
Prompt Running stats for Stale index objects
declare
sql_stmt varchar2(256);
va_tab dba_ind_statistics.table_name%type;
cursor c_f is (select owner,index_name,global_stats,stale_stats from dba_ind_statistics
where stale_stats = 'YES' and table_name not like 'BIN$%' and STATTYPE_LOCKED is null);
begin
for i in c_f
loop
begin
dbms_stats.gather_index_stats(''||i.owner||'',''||i.index_name||'',
estimate_percent => 100,
degree => $CHANNELS,
no_invalidate => false);
dbms_output.put_line(sysdate || ': stats done for ' || ''||i.owner||'' ||'.' || ''||i.index_name||'');
exception
when others then
continue;
dbms_output.put_line(sysdate || ': failed to gather stats for ' || ''||i.owner||'' ||'.' || ''||i.index_name||'' || chr(10) || sqlerrm);
end;
end loop;
dbms_output.put_line(sysdate || ': #######################' || ': Stats done for Above Found Stale objects ' || ': #######################');
END;
/
EOF
echo "Done" >> $LOG
Hope this Helps......
