Script to gather stats the customs schemas

The below script is to gather stats the customs schemas.

SET SERVEROUTPUT ON SIZE 1000000
set feedb off
set trimspool on

spool tmp_gather_schstats_${ORACLE_SID}.sql

DECLARE
CURSOR c_tm IS
select DISTINCT OWNER
from dba_tables t
where t.OWNER not in (‘APPQOSSYS’,’CTXSYS’,’DBSNMP’,’EXFSYS’,’MDSYS’,’OLAPSYS’,’ORDDATA’,’ORDSYS’,
‘OUTLN’,’PERFSTAT’,’SYS’,’SYSTEM’,’WMSYS’,’XDB’)
order by 1;

BEGIN
DBMS_OUTPUT.PUT_LINE(‘set echo on feedback on ‘);
DBMS_OUTPUT.PUT_LINE(‘set serveroutput on ‘);
DBMS_OUTPUT.PUT_LINE(‘ ‘);

DBMS_OUTPUT.PUT_LINE(‘BEGIN ‘);
DBMS_OUTPUT.PUT_LINE(‘ DBMS_STATS.gather_system_stats(‘||””||’Start’||””||’);’);
DBMS_OUTPUT.PUT_LINE(‘ DBMS_STATS.GATHER_FIXED_OBJECTS_STATS();’);
DBMS_OUTPUT.PUT_LINE(‘ DBMS_STATS.GATHER_DICTIONARY_STATS();’);
DBMS_OUTPUT.PUT_LINE(‘ DBMS_STATS.gather_system_stats(‘||””||’Stop’||””||’);’);
DBMS_OUTPUT.PUT_LINE(‘END; ‘);
DBMS_OUTPUT.PUT_LINE(‘/ ‘);
DBMS_OUTPUT.PUT_LINE(‘ ‘);

FOR c_tm_rec IN c_tm LOOP
DBMS_OUTPUT.PUT_LINE(‘BEGIN ‘);
DBMS_OUTPUT.PUT_LINE(‘ DBMS_STATS.GATHER_SCHEMA_STATS ( ‘);
DBMS_OUTPUT.PUT_LINE(‘ OWNNAME => ‘||””||c_tm_rec.OWNER||””||’,’);
— DBMS_OUTPUT.PUT_LINE(‘ OPTIONS => ‘||””||’GATHER AUTO’||””||’, ‘); — gather stale and gather empty
DBMS_OUTPUT.PUT_LINE(‘ ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE,’);
DBMS_OUTPUT.PUT_LINE(‘ DEGREE => CEIL(DBMS_STATS.AUTO_DEGREE/2),’);
DBMS_OUTPUT.PUT_LINE(‘ METHOD_OPT => ‘||””||’FOR ALL INDEXED COLUMNS SIZE SKEWONLY’||””||’,’);
DBMS_OUTPUT.PUT_LINE(‘ GRANULARITY => ‘||””||’ALL’||””||’,’);
DBMS_OUTPUT.PUT_LINE(‘ NO_INVALIDATE => FALSE’||’,’);
DBMS_OUTPUT.PUT_LINE(‘ CASCADE => TRUE); ‘);
DBMS_OUTPUT.PUT_LINE(‘END; ‘);
DBMS_OUTPUT.PUT_LINE(‘/ ‘);

END LOOP;
END;
/

spool off

set feedb on

@tmp_gather_schstats_${ORACLE_SID}.sql