dbms_stats.gather_dictionary_stats
and
dbms_stats.gather_fixed_objects_stats
Examples:
BEGIN DBMS_STATS.GATHER_DICTIONARY_STATS ( estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE ,method_opt => 'FOR ALL COLUMNS SIZE AUTO' ,degree => NULL ,cascade => DBMS_STATS.AUTO_CASCADE ,granularity => 'AUTO' ,no_Invalidate => DBMS_STATS.AUTO_INVALIDATE); END; / BEGIN DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; END; /
Used in a simple shell script:
#!/usr/bin/ksh # Gather dictionary and fixed objects stats as recommended by oracle cat << EoF > ${SCRIPT_BASE}/sql/gather_dict_stats.sql alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS'; spool ${SCRIPT_BASE}/log/gather_dict_stats.log append set timing on select 'Start time: ' || sysdate as "startime" from dual; execute dbms_stats.gather_dictionary_stats(estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'FOR ALL COLUMNS SIZE AUTO',degree=>DBMS_STATS.DEFAULT_DEGREE,granularity=>'ALL',cascade=>DBMS_STATS.AUTO_CASCADE,options=>'GATHER AUTO',no_invalidate=>DBMS_STATS.AUTO_INVALIDATE); execute dbms_stats.gather_fixed_objects_stats(NULL); select 'End time: ' || sysdate as "endtime" from dual; exit EoF sqlplus / as sysdba @${SCRIPT_BASE}/sql/gather_dict_stats.sql rm -r ${SCRIPT_BASE}/sql/gather_dict_stats.sql exit
Good sources for further reading: Maria Colgan's blog
No comments:
Post a Comment