高手们都来看看GATHER_STATS_JOB表搜集任务问题吧

Question

问题如下：每天都会从一个几亿条数据的表中取数据，表中数据是变化的，每天增加大约两三千万条，每到周一凌晨跑周末数据的时候，都不走索引，其他时间都走。
我查了下scheduler情况，如下：
select * from  dba_scheduler_jobs WHERE job_name = 'GATHER_STATS_JOB';结果如下：
1 SYS GATHER_STATS_JOB SYS SYS GATHER_STATS_PROG SYS MAINTENANCE_WINDOW_GROUP WINDOW_GROUP AUTO_TASKS_JOB_CLASS TRUE FALSE TRUE SCHEDULED 3 870 1 0 13-10月-11 10.00.01.005471 下午 +08:00 +000000000 00:57:38.037224 RUNS TRUE TRUE TRUE 1 NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_CALENDAR='GREGORIAN' NLS_DATE_FORMAT='DD-MON-RR' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY' NLS_TIME_FORMAT='HH.MI.SSXFF AM' NLS_TIMESTAMP_FORMAT='DD-MON-RR HH.MI.SSXFF AM' NLS_TIME_TZ_FORMAT='HH.MI.SSXFF AM TZR' NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH.MI.SSXFF AM TZR' NLS_DUAL_CURRENCY='$' NLS_COMP='BINARY' NLS_LENGTH_SEMANTICS='BYTE' NLS_NCHAR_CONV_EXCP='FALSE' Oracle defined automatic optimizer statistics collection job 21074010
然后查看select * from dba_scheduler_windows
结果如下：
1 WEEKNIGHT_WINDOW CALENDAR freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0 +000 08:00:00 LOW 14-10月-11 10.00.00.100000 下午 +08:00 13-10月-11 10.00.00.105431 下午 +08:00 TRUE FALSE Weeknight window for maintenance task
2 WEEKEND_WINDOW CALENDAR freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0 +002 00:00:00 LOW 15-10月-11 12.00.00.000000 上午 +08:00 08-10月-11 06.00.02.766671 上午 +08:00 TRUE FALSE Weekend window for maintenance task
按理说这个job应该是在周一到周五的22点到早点6点，周末全天执行，但是我查看dba_scheduler_job_run_details情况如下：
select to_char(d.LOG_DATE,'yyyy-mm-dd hh24:mi:ss'),to_char(d.actual_start_date,'yyyy-mm-dd hh24:mi:ss'),
d.CPU_USED,d.RUN_DURATION
from dba_scheduler_job_run_details d
where d.job_name='GATHER_STATS_JOB'
order by d.LOG_DATE desc2011-10-13 22:57:39 2011-10-13 22:00:01 +000 00:22:35.09                                                            +000 00:57:38
2011-10-13 00:53:01 2011-10-12 22:00:03 +000 00:48:31.08                                                            +000 02:52:58
2011-10-11 22:47:10 2011-10-11 22:00:01 +000 00:19:14.86                                                            +000 00:47:08
2011-10-11 01:15:12 2011-10-10 22:00:03 +000 01:08:09.57                                                            +000 03:15:09
2011-10-08 06:36:27 2011-10-08 06:00:03 +000 00:34:42.38                                                            +000 00:36:24
2011-10-07 23:33:04 2011-10-07 22:00:02 +000 00:31:01.05                                                            +000 01:33:02
2011-10-06 23:11:00 2011-10-06 22:00:02 +000 00:25:52.66                                                            +000 01:10:57
2011-10-05 23:15:14 2011-10-05 22:00:02 +000 00:32:01.24                                                            +000 01:15:12
2011-10-04 22:50:26 2011-10-04 22:00:01 +000 00:22:08.28                                                            +000 00:50:25
2011-10-03 23:47:37 2011-10-03 22:00:02 +000 00:59:33.99                                                            +000 01:47:35
2011-10-01 07:37:40 2011-10-01 06:00:05 +000 01:19:49.39                                                            +000 01:37:36
2011-10-01 03:13:27 2011-09-30 22:00:03 +000 00:58:32.50                                                            +000 05:13:24
2011-09-29 23:35:43 2011-09-29 22:00:02 +000 00:33:00.47                                                            +000 01:35:41
2011-09-28 23:29:11 2011-09-28 22:00:00 +000 00:29:24.28                                                            +000 01:29:10
2011-09-27 23:03:35 2011-09-27 22:00:02 +000 00:23:01.45                                                            +000 01:03:33
2011-09-27 03:36:29 2011-09-26 22:00:02 +000 01:22:26.80                                                            +000 05:36:27
2011-09-24 06:47:49 2011-09-24 06:00:03 +000 00:31:17.36                                                            +000 00:47:47
2011-09-24 03:28:20 2011-09-23 22:00:02 +000 03:09:06.53                                                            +000 05:28:18
2011-09-23 01:51:26 2011-09-22 22:00:00 +000 00:36:20.89                                                            +000 03:51:26
2011-09-22 00:29:49 2011-09-21 22:00:03 +000 00:54:17.56                                                            +000 02:29:46
2011-09-20 23:15:45 2011-09-20 22:00:03 +000 00:27:29.89                                                            +000 01:15:42
2011-09-20 03:50:34 2011-09-19 22:00:03 +000 01:36:54.38                                                            +000 05:50:32
2011-09-17 06:49:29 2011-09-17 06:00:03 +000 00:22:50.97                                                            +000 00:49:26
2011-09-17 01:20:11 2011-09-16 22:00:02 +000 00:37:09.95                                                            +000 03:20:08
2011-09-15 22:56:00 2011-09-15 22:00:02 +000 00:20:34.76                                                            +000 00:55:58
2011-09-15 01:42:01 2011-09-14 22:00:02 +000 00:45:50.39                                                            +000 03:41:59每个周末都没有相关的记录，请问这正常吗？会不会是因为这个导致周一凌晨的程序不走索引了呢？

Answer 1

建议你上一下ITPUB..那里有个人比较厉害。如果他有时间的话，会帮你分析 。

Answer 2

DBA_OPTSTAT_OPERATIONS 里面有详细的信息。
你这统计信息是每周1-5的22点收集一次，周6的6点收集一次，周日没有。从周六早上6点直到下周一晚上22点才会再次收集，之间更新的记录可能影响了执行计划。
考虑到每天更新的数据都比较多，建议在周日也收集下统计信息，然后再看周一会不会走索引。