如何检查GATHER_STATS_JOB任务的执行情况

GATHER_STATS_JOB是10g开始引入的自动统计数据收集功能的重要组成部分，但是这个定时任务带来的问题也是较多的，应当根据应用的具体情况进行定制，通过DBA_SCHEDULER_JOBS可以查询JOB的执行情况：

SQL> col job_name for a20
SQL> col owner for a5
SQL> col last_start_date for a36
SQL> col last_run_duration for a30
SQL> col state for a10
SQL> SELECT owner,job_name,state,last_start_date,last_run_duration,failure_count
2 FROM dba_scheduler_jobs WHERE job_name = 'GATHER_STATS_JOB';

OWNER JOB_NAME STATE LAST_START_DATE LAST_RUN_DURATION FAILURE_COUNT
----- -------------------- ---------- ------------------------------------ ------------------------------ -------------
SYS GATHER_STATS_JOB SCHEDULED 09-SEP-09 10.00.01.091140 PM +08:00 +000000000 00:10:02.536139 0

进一步的，通过dba_scheduler_job_run_details表可以获得JOB的执行情况细节，一下显示JOB都执行成功：

SQL> col job_name for a20
SQL> SELECT log_id, job_name, status,
2 TO_CHAR (log_date, 'DD-MON-YYYY HH24:MI') log_date
3 FROM dba_scheduler_job_run_details
4 WHERE job_name = 'GATHER_STATS_JOB';

LOG_ID JOB_NAME STATUS LOG_DATE
---------- -------------------- ------------------------------ --------------------
52135 GATHER_STATS_JOB SUCCEEDED 12-AUG-2009 22:04
53615 GATHER_STATS_JOB SUCCEEDED 25-AUG-2009 22:02
52755 GATHER_STATS_JOB SUCCEEDED 18-AUG-2009 22:03
54075 GATHER_STATS_JOB SUCCEEDED 29-AUG-2009 06:03
54735 GATHER_STATS_JOB SUCCEEDED 05-SEP-2009 06:02
52415 GATHER_STATS_JOB SUCCEEDED 15-AUG-2009 06:03
53995 GATHER_STATS_JOB SUCCEEDED 28-AUG-2009 22:03
52055 GATHER_STATS_JOB SUCCEEDED 11-AUG-2009 22:03
53895 GATHER_STATS_JOB SUCCEEDED 27-AUG-2009 22:02
52655 GATHER_STATS_JOB SUCCEEDED 17-AUG-2009 22:04
54296 GATHER_STATS_JOB SUCCEEDED 31-AUG-2009 22:03

LOG_ID JOB_NAME STATUS LOG_DATE
---------- -------------------- ------------------------------ --------------------
54395 GATHER_STATS_JOB SUCCEEDED 01-SEP-2009 22:03
54495 GATHER_STATS_JOB SUCCEEDED 02-SEP-2009 22:02
54595 GATHER_STATS_JOB SUCCEEDED 03-SEP-2009 22:02
52235 GATHER_STATS_JOB SUCCEEDED 13-AUG-2009 22:03
52355 GATHER_STATS_JOB SUCCEEDED 14-AUG-2009 22:03
54675 GATHER_STATS_JOB SUCCEEDED 04-SEP-2009 22:02
54995 GATHER_STATS_JOB SUCCEEDED 07-SEP-2009 22:04
55115 GATHER_STATS_JOB SUCCEEDED 08-SEP-2009 22:03
55256 GATHER_STATS_JOB SUCCEEDED 09-SEP-2009 22:10
52916 GATHER_STATS_JOB SUCCEEDED 19-AUG-2009 22:10
53775 GATHER_STATS_JOB SUCCEEDED 26-AUG-2009 22:03

LOG_ID JOB_NAME STATUS LOG_DATE
---------- -------------------- ------------------------------ --------------------
53455 GATHER_STATS_JOB SUCCEEDED 24-AUG-2009 22:04
53235 GATHER_STATS_JOB SUCCEEDED 22-AUG-2009 06:02
53055 GATHER_STATS_JOB SUCCEEDED 20-AUG-2009 22:02
53155 GATHER_STATS_JOB SUCCEEDED 21-AUG-2009 22:04

26 rows selected.

这个JOB任务运行的具体过程如下：

SQL> select PROGRAM_ACTION from dba_scheduler_programs where PROGRAM_NAME = 'GATHER_STATS_PROG';

PROGRAM_ACTION
------------------------------------------------------------------------------------------------------------------------
dbms_stats.gather_database_stats_job_proc

以下是一个TB级海量数据库的统计数据收集情况，可以看到这个任务在很多时候于凌晨6：00被强制停止，因为数据量太大，分析不完了，而在周末全天运行时，有时候可以完成任务：

SQL> SELECT log_id, job_name, status,
2 TO_CHAR (log_date, 'DD-MON-YYYY HH24:MI') log_date
3 FROM dba_scheduler_job_run_details
4 WHERE job_name = 'GATHER_STATS_JOB' order by 1;

LOG_ID JOB_NAME STATUS LOG_DATE
---------- -------------------- ------------------------------ ------------------------------
23749 GATHER_STATS_JOB STOPPED 18-AUG-2009 06:00
23803 GATHER_STATS_JOB STOPPED 19-AUG-2009 06:00
23857 GATHER_STATS_JOB STOPPED 20-AUG-2009 06:00
23911 GATHER_STATS_JOB STOPPED 21-AUG-2009 06:00
23965 GATHER_STATS_JOB STOPPED 22-AUG-2009 06:00
23978 GATHER_STATS_JOB SUCCEEDED 22-AUG-2009 10:15
24109 GATHER_STATS_JOB STOPPED 24-AUG-2009 23:53
24129 GATHER_STATS_JOB STOPPED 25-AUG-2009 00:27
24133 GATHER_STATS_JOB SUCCEEDED 29-AUG-2009 19:43
24162 GATHER_STATS_JOB STOPPED 25-AUG-2009 06:00
24216 GATHER_STATS_JOB STOPPED 26-AUG-2009 06:00

LOG_ID JOB_NAME STATUS LOG_DATE
---------- -------------------- ------------------------------ ------------------------------
24270 GATHER_STATS_JOB STOPPED 27-AUG-2009 06:00
24324 GATHER_STATS_JOB STOPPED 28-AUG-2009 06:00
24378 GATHER_STATS_JOB STOPPED 29-AUG-2009 06:00
24533 GATHER_STATS_JOB STOPPED 01-SEP-2009 06:00
24587 GATHER_STATS_JOB STOPPED 02-SEP-2009 06:00
24641 GATHER_STATS_JOB STOPPED 03-SEP-2009 06:00
24695 GATHER_STATS_JOB STOPPED 04-SEP-2009 06:00
24749 GATHER_STATS_JOB STOPPED 05-SEP-2009 06:00
24759 GATHER_STATS_JOB SUCCEEDED 05-SEP-2009 09:27
24906 GATHER_STATS_JOB STOPPED 08-SEP-2009 06:00
24946 GATHER_STATS_JOB STOPPED 08-SEP-2009 23:54

LOG_ID JOB_NAME STATUS LOG_DATE
---------- -------------------- ------------------------------ ------------------------------
24966 GATHER_STATS_JOB STOPPED 09-SEP-2009 00:06
24970 GATHER_STATS_JOB STOPPED 09-SEP-2009 05:58
25123 GATHER_STATS_JOB STOPPED 10-SEP-2009 06:00
25177 GATHER_STATS_JOB STOPPED 11-SEP-2009 06:00
25231 GATHER_STATS_JOB STOPPED 12-SEP-2009 06:00
25257 GATHER_STATS_JOB SUCCEEDED 12-SEP-2009 16:31
25379 GATHER_STATS_JOB SUCCEEDED 15-SEP-2009 01:10
25429 GATHER_STATS_JOB SUCCEEDED 15-SEP-2009 23:28

30 rows selected.

-The End-