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

PHPABC Oracle 779 次浏览 , 没有评论

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-

发表评论

电子邮件地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据

Go