查询统计信息的收集所对应的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 ---------------------------------------------------------------- ----------------------------------------------------------------- --------------------------------------------------