BSLN_MAINTAIN_STATS_JOB不起作用

26856649

库版本为 11.1.0.6.0 ，我清空了一schema 的 stats信息，连续很多天查看，发现未能自动收集stats，谁能给讲讲怎么回事儿

以下是 BSLN_MAINTAIN_STATS_JOB 最近两天运行日志
LOG_ID LOG_ID LOG_DATE OWNER JOB_SUBNAME STATUS ERROR# LOG_ID REQ_START_DATE LOG_ID ACTUAL_START_DATE RUN_DURATION INSTANCE_ID SESSION_ID SLAVE_PID CPU_USED DESTINATION ADDITIONAL_INFO
263471 263471 2012-02-18 00:00:00 SYS  SUCCEEDED 0 263471 2012-02-18 00:00:00 263471 2012-02-18 00:00:00 +000 00:00:00 1 74,3258 3512 +000 00:00:00.03  
263451 263451 2012-02-17 00:00:00 SYS  SUCCEEDED 0 263451 2012-02-17 00:00:00 263451 2012-02-17 00:00:00 +000 00:00:00 1 75,2368 4092 +000 00:00:00.01  
263430 263430 2012-02-16 00:00:00 SYS  SUCCEEDED 0 263430 2012-02-16 00:00:00 263430 2012-02-16 00:00:00 +000 00:00:00 1 87,1279 1704 +000 00:00:00.03  

WINDOW信息
SELECT W.WINDOW_NAME, W.REPEAT_INTERVAL, W.DURATION, W.ENABLED
  FROM DBA_SCHEDULER_WINGROUP_MEMBERS M, DBA_SCHEDULER_WINDOWS W
WHERE M.WINDOW_NAME = W.WINDOW_NAME
   AND M.WINDOW_GROUP_NAME = 'MAINTENANCE_WINDOW_GROUP'

WINDOW_NAME REPEAT_INTERVAL DURATION ENABLED
------------------------------ --------------------------------------------------------------------------------
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

26856649

SELECT program_action,number_of_arguments,ENABLED
from dba_scheduler_programs
WHERE owner = 'SYS'
AND program_name = UPPER('gather_stats_prog')
--
dbms_stats.gather_database_stats_job_proc 0 TRUE

Maclean Liu(刘相兵

action plan:


set linesize 140 pagesize 1400
select client_name,status from Dba_Autotask_Client;

select job_name,window_start_time,job_status,job_error,job_info from DBA_AUTOTASK_JOB_HISTORY where client_name='auto optimizer stats collection';

26856649

SQL> select client_name,status from Dba_Autotask_Client;
CLIENT_NAME                                                      STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection                                  ENABLED
auto space advisor                                               ENABLED
sql tuning advisor                                               ENABLED
SQL>
SQL> select job_name,window_start_time,job_status,job_error,job_info from DBA_AU
TOTASK_JOB_HISTORY where client_name='auto optimizer stats collection';
未选定行
SQL> select job_name,window_start_time,job_status,job_error,job_info from DBA_AU
TOTASK_JOB_HISTORY;
未选定行
SQL>

另：测试库，未开EM,我刚刚才打开。

[ 本帖最后由 26856649 于 2012-2-20 09:42 编辑 ]

Maclean Liu(刘相兵

这个JOB 是每天22点以后才运行的， 如果你不是一直开着 那么 没统计到也属正常。

26856649

就是机子一直开着，下班后不关的。