收集统计信息检查

alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';

SELECT CLIENT_NAME,TASK_NAME,OPERATION_NAME,STATUS FROM dba_autotask_task; -- status 应该是 ENABLED

select client_name,status from dba_autotask_client order by 1;

SELECT PROGRAM_NAME,PROGRAM_TYPE,PROGRAM_ACTION FROM dba_scheduler_programs WHERE PROGRAM_NAME = 'GATHER_STATS_PROG'; --action应该是dbms_stats.gather_database_stats_job_proc

SELECT WINDOW_NAME,AUTOTASK_STATUS FROM dba_autotask_window_clients ; --status应该是 ENABLED

SELECT a.WINDOW_NAME,a.REPEAT_INTERVAL,a.duration FROM dba_scheduler_windows a WHERE ENABLED = 'TRUE'; --DURATION 应该是4小时 周末20小时

select * from ( SELECT a.JOB_NAME,a.ACTUAL_START_DATE,a.RUN_DURATION,a.STATUS
 FROM dba_scheduler_job_run_details a WHERE a.JOB_NAME LIKE 'ORA$AT_OS_OPT%' order by ACTUAL_START_DATE desc) where rownum<11 ; --近期 status应该是SUCCEEDED

SELECT * FROM (select OWNER,t.TABLE_NAME,t.NUM_ROWS,t.BLOCKS,t.LAST_ANALYZED from dba_tables t where owner not in ('SYS','SYSTEM','DBSNMP','MDSYS','CTXSYS','XDB','DVSYS') and LAST_ANALYZED is not null ORDER BY LAST_ANALYZED DESC ) WHERE ROWNUM<11;   --验证

  sho parameter job
设置 STATISTICS_LEVEL = BASIC 禁用自动优化器统计信息收集。

  还不够？

DBA_AUTOTASK_CLIENT
DBA_AUTOTASK_CLIENT_HISTORY
DBA_AUTOTASK_CLIENT_JOB
DBA_AUTOTASK_JOB_HISTORY
DBA_AUTOTASK_OPERATION
DBA_AUTOTASK_SCHEDULE
DBA_AUTOTASK_TASK
DBA_AUTOTASK_WINDOW_CLIENTS
DBA_AUTOTASK_WINDOW_HISTORY

手工执行收集统计信息

exec DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS;

 select DBMS_STATS.GET_PREFS('STALE_PERCENT') from dual; 
可以看以下属性
AUTOSTATS_TARGET
CASCADE
DEGREE
ESTIMATE_PERCENT
METHOD_OPT
NO_INVALIDATE
GRANULARITY
PUBLISH
INCREMENTAL
STALE_PERCENT

改一个

exec DBMS_STATS.SET_GLOBAL_PREFS('STALE_PERCENT','5');

参考：
  常见问题解答：自动统计信息收集（文档 ID 1233203.1）