eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« SUN + Oracle推出Exadata 2 终止与HP的合作 | Blog首页 | 换一种思路 - 通过导出导入进行数据整理 »

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

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-


历史上的今天...
    >> 2019-09-17文章:
    >> 2012-09-17文章:
    >> 2010-09-17文章:
    >> 2008-09-17文章:
    >> 2007-09-17文章:
    >> 2006-09-17文章:

By eygle on 2009-09-17 09:36 | Comments (0) | FAQ | HowTo | 2395 |


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com