禁用自動分析
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/,如需转载,请注明出处,否则将追究法律责任。