禁用自動分析

exec dbms_scheduler.disable('SYS.GATHER_STAT_JOB');

啟用自動分析

exec dbms_scheduler.enable('SYS.GATHER_STAT_JOB');

gather_schema_stats

exec dbms_stats.gather_schema_stats(ownname => 'DFMS',estimate_percent => 30,method_opt => 'for all columns size auto',cascade=>TRUE,degree => 8 ) ;

gather_table_stats(table & index)

exec dbms_stats.gather_table_stats(ownname=>'USER_NAME',tabname=>'TAB_NAME',method_opt=>'for all indexed columns size auto',cascade=>true);

gather_table_stats(table)

exec dbms_stats.gather_table_stats(ownname=>'USER_NAME',tabname=>'TAB_NAME',method_opt=>'for all indexed columns size auto',cascade=>false);

gather_index_stats
exec dbms_stats.gather_index_stats(ownname =>'USER_NAME',indname =>'INDEX_NAME',estimate_percent => '30',degree => '4') ;

/* Formatted on 2011-7-30 10:54:27 (QP5 v5.115.810.9015) */
SELECT      'exec dbms_stats.gather_schema_stats(ownname =>'
         || ''''
         || username
         || ''''
         || ',estimate_percent => 30,method_opt =>'
         || ''''
         || 'for all columns size auto'
         || ''''
         || ',cascade=>TRUE,degree => 8 ) ;'
  FROM   dba_users

/* Formatted on 2011-7-30 11:46:26 (QP5 v5.115.810.9015) */
SELECT      'exec dbms_stats.gather_table_stats(ownname=>'
         || ''''
         || owner
         || ''''
         || ',tabname=>'
         || ''''
         || table_name
         || ''''
         || ',method_opt=>'
         || ''''
         || 'for all indexed columns size auto'
         || ''''
         || ',cascade=>true) ;'
  FROM   dba_tables
 WHERE   wner = :owner

/* Formatted on 2011-7-30 11:52:53 (QP5 v5.115.810.9015) */
SELECT      'exec dbms_stats.gather_table_stats(ownname=>'
         || ''''
         || owner
         || ''''
         || ',tabname=>'
         || ''''
         || table_name
         || ''''
         || ',method_opt=>'
         || ''''
         || 'for all indexed columns size auto'
         || ''''
         || ',cascade=>false) ;'
  FROM   dba_tables
 WHERE   wner = :owner

/* Formatted on 2011-7-30 11:59:35 (QP5 v5.115.810.9015) */
SELECT      'exec dbms_stats.gather_index_stats(ownname =>'
         || ''''
         || owner
         || ''''
         || ',indname =>'
         || ''''
         || index_name
         || ''''
         || ',estimate_percent =>30,degree => 4) ;'
  FROM   dba_indexes
 WHERE   wner = :owner

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25198367/viewspace-703884/，如需转载，请注明出处，否则将追究法律责任。