[zt]Oracle10g自動優化統計-檢查GATHER_STATS_JOB任務的執行情況

tolywang發表於2010-02-08
Oracle優化
作者：eygle | English Version 【轉載時請以超連結形式標明文章和作者資訊及本宣告】
連結：
 
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.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/35489/viewspace-630262/，如需轉載，請註明出處，否則將追究法律責任。

相關文章