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.
|