Oracle 11G统计信息自动收集及调整

查询统计信息的收集所对应的task,以及当前状态

col CLIENT_NAME for a50
col TASK_NAME for a20
SELECT client_name, task_name, status
  FROM dba_autotask_task
 WHERE client_name = 'auto optimizer stats collection';
auto optimizer stats collection gather_stats_prog ENABLED;

CLIENT_NAME                           TASK_NAME        STATUS
--------------- ------------------  -----------------  -------
auto optimizer stats collection     gather_stats_prog  ENABLED

select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0    Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

下面查询dba_scheduler_programs
--在scheduler_programs中，程序GATHER_STATS_PROG调用
--dbms_stats.gather_database_stats_job_proc来实现统计信息的自动收集

col PROGRAM_ACTION for a50
SELECT program_action, number_of_arguments, enabled
FROM dba_scheduler_programs
WHERE owner = 'SYS'
AND program_name = 'GATHER_STATS_PROG';

PROGRAM_ACTION                       NUMBER_OF_ARGUMENTS ENABL
-------------------------------------------------- ------------------- -----
dbms_stats.gather_database_stats_job_proc                 0 TRUE

2、查看自动收集统计信息是否开启

SELECT CLIENT_NAME, STATUS
  FROM DBA_AUTOTASK_CLIENT
 WHERE CLIENT_NAME = 'auto optimizer stats collection';

CLIENT_NAME                             STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection                  ENABLED

统计信息收集的窗口组，如下查询

SELECT window_group
FROM dba_autotask_client
WHERE client_name = 'auto optimizer stats collection';

WINDOW_GROUP
----------------------------------------------------------------
ORA$AT_WGRP_OS

查询统计信息收集的具体窗口
--统计信息收集的时间窗口
--如下查询周一至周五时间为22点，周六日为6点
--此外持续也不相同，周一至周五为4小时，周六日为20个小时
--enabled为true表明当前的这些作业处于激活状态

col REPEAT_INTERVAL for a60
col DURATION for a30
SELECT w.window_name, w.repeat_interval, w.duration, w.enabled
  FROM dba_autotask_window_clients c, dba_scheduler_windows w
 WHERE c.window_name = w.window_name
   AND c.optimizer_stats = 'ENABLED';

WINDOW_NAME               REPEAT_INTERVAL                            DURATION               ENABL
------------------------------ ------------------------------------------------------------ ------------------------------ -----
MONDAY_WINDOW               freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0        +000 04:00:00           TRUE
TUESDAY_WINDOW               freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0        +000 04:00:00           TRUE
WEDNESDAY_WINDOW           freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0        +000 04:00:00           TRUE
THURSDAY_WINDOW            freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0        +000 04:00:00           TRUE
FRIDAY_WINDOW               freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0        +000 04:00:00           TRUE
SATURDAY_WINDOW            freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0        +000 20:00:00           TRUE
SUNDAY_WINDOW               freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0        +000 20:00:00           TRUE

查看自动收集统计信息历史执行情况

SELECT * FROM dba_autotask_client_history WHERE client_name LIKE '%stats%';

CLIENT_NAME                                                      WINDOW_NAME                                                       WINDOW_START_TIME                                                                WINDOW_DURATION                                                                 JOBS_CREATED JOBS_STARTED JOBS_COMPLETED WINDOW_END_TIME
---------------------------------------------------------------- ----------------------------------------------------------------- ----------------------------------------------