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;

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

转载于:http://blog.itpub.net/14012827/viewspace-1796331/