Oracle 10g/11g 統計資訊相關

10g:
確認統計資訊收集是否開啟
SQL> select * from Dba_Scheduler_Jobs where JOB_NAME ='GATHER_STATS_JOB';
關閉、啟動這個自動統計資訊收集功能：  
SQL> exec DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
SQL> exec DBMS_SCHEDULER.ENABLE('GATHER_STATS_JOB');
手動收集統計資訊
DBMS_STATS.GATHER_DATABASE_STATS or DBMS_STATS.GATHER_SCHEMA_STATS OR DBMS_STATS.GATHER_TABLE_STATS




11g：
確認統計資訊收集是否開啟
select client_name,status from DBA_AUTOTASK_CLIENT where client_name='auto optimizer stats collection';
關閉、啟動這個自動統計資訊收集功能：
exec dbms_auto_task_admin.disable(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);


BEGIN
  dbms_auto_task_admin.enable(
  client_name => 'auto optimizer stats collection',
  operation => NULL,
  window_name => NULL);
END;
/
手動收集統計資訊，先備份現有統計資訊
exec DBMS_STATS.CREATE_STAT_TABLE (ownname=>'SYSTEM', stattab=>'ees_statistics_OLD', tblspace=>'SYSTEM');
exec DBMS_STATS.EXPORT_DATABASE_STATS (statown=>'SYSTEM', stattab=>'ees_statistics_OLD');
exec DBMS_STATS.GATHER_DATABASE_STATS(degree=>16);
exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'S8PROD',degree=>12,force => TRUE);


exec DBMS_STATS.CREATE_STAT_TABLE (ownname=>'SYSTEM', stattab=>'ees_statistics_NEW', tblspace=>'SYSTEM');
exec DBMS_STATS.EXPORT_DATABASE_STATS (statown=>'SYSTEM', stattab=>'ees_statistics_NEW');
exec DBMS_STATS.IMPORT_DATABASE_STATS (statown=>'SYSTEM', stattab=>'statistics_NEW');
exec DBMS_STATS.IMPORT_SCHEMA_STATS (ownname=>'S8PROD',statown=>'SYSTEM', stattab=>'statistics_NEW', FORCE=>TRUE );
更新資料字典
exec dbms_stats.gather_system_stats(GATHERING_MODE=>'INTERVAL', INTERVAL=>10);
exec dbms_stats.gather_fixed_objects_stats;
exec dbms_stats.gather_dictionary_stats;