script to find stale objects and run the stats

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......