DBA_SCHEDULER_JOBS——gather_stats_job

GATHER_STATS_JOB

今天检查alert日志的时候发现以下报错：

ORA-1652:unable to extend temp segment by 128 in tablespace TEMP

MonNov 25 22:57:07 2013

GATHER_STATS_JOB encountered errors.  Check the trace file.

MonNov 25 22:57:07 2013

Errorsin file /u01/oracle/admin/epqdb/bdump/epqdb2_ora_26041.trc:

ORA-01652: unable to extend temp segment by 128 intablespace TEMP

很明显是temp表空间不够。

但让我关注的不是该ORA-1652错误，而是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> SELECTowner,job_name,state,last_start_date,last_run_duration,failure_count

FROM dba_scheduler_jobs WHERE job_name ='GATHER_STATS_JOB';

 

OWNERJOB_NAME             STATE      LAST_START_DATE                      LAST_RUN_DURATION              FAILURE_COUNT

------------------------- ---------- ------------------------------------------------------------------ -------------

SYS   GATHER_STATS_JOB     SCHEDULED 25-NOV-13 10.00.02.884222 PM +08:00 +000000000 00:04:45.756662                 0

 

--查看GATHER_STATS_JOB运行的具体过程：

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

 

PROGRAM_ACTION

---------------------------------------------------------------------------------------------------------------------------------

dbms_stats.gather_database_stats_job_proc

 

--查看GATHER_STATS_JOB的运行日志：

SQL> SELECT log_id, job_name, status,

  2       TO_CHAR (log_date, 'DD-MON-YYYYHH24:MI') log_date

  3   FROM dba_scheduler_job_run_details

  4  WHERE job_name = 'GATHER_STATS_JOB'  order by 1 desc;

 

LOG_IDJOB_NAME             STATUS                         LOG_DATE

------------------------------ ------------------------------ -----------------------

26286GATHER_STATS_JOB     SUCCEEDED                      25-NOV-2013 22:04

26254GATHER_STATS_JOB     SUCCEEDED                      23-NOV-2013 06:14

26246GATHER_STATS_JOB     SUCCEEDED                      22-NOV-2013 22:03

26196GATHER_STATS_JOB     SUCCEEDED                      21-NOV-2013 22:02

26189GATHER_STATS_JOB     SUCCEEDED                      20-NOV-2013 22:02

26165GATHER_STATS_JOB     SUCCEEDED                      19-NOV-2013 22:02

26145GATHER_STATS_JOB     SUCCEEDED                      18-NOV-2013 22:02

26114GATHER_STATS_JOB     SUCCEEDED                      16-NOV-2013 06:01

26108GATHER_STATS_JOB     SUCCEEDED                      15-NOV-2013 22:02

26085GATHER_STATS_JOB     SUCCEEDED                      14-NOV-2013 22:02

26045GATHER_STATS_JOB     SUCCEEDED                      12-NOV-2013 22:01

……

 

 

参考eygle的blog：http://www.eygle.com/archives/2009/09/gather_stats_job.html