Does GATHER_STATS_JOB gather all objects’ stats every time?

周五在一家客户的调优会议中讨论了一个由于统计信息陈旧导致SQL执行计划偏差的问题,这是一个10g的库并且禁用了自动收集统计信息的定时作业GATHER_STATS_JOB;当问及应用程序开发商为何要禁用自动统计信息收集时,开发商的一位工程师说因为该库的数据量较大,考虑到该JOB每天都会将所有大表统计一遍可能要花费大量时间所以予以停用。

这里就存在一个问题,GATHER_STATS_JOB自动统计作业是每次打开都会将数据库中所有的对象的统计信息都收集一遍吗?细心的朋友一定会发觉实际上该JOB的运行时间是时长时短的,同时绝对不是如这位开发工程师所说的会每天都重复统计所有表。

10g的官方文档中对该GATHER_STATS_JOB描述为”The Scheduler runs this job when the maintenance window is opened. By default, the maintenance window opens every night from 10 P.M. to 6 A.M. and all day on weekends.The stop_on_window_close attribute controls whether the GATHER_STATS_JOB continues when the maintenance window closes. The default setting for the stop_on_window_close attribute is TRUE, causing Scheduler to terminate GATHER_STATS_JOB when the maintenance window closes. The remaining objects are then processed in the next maintenance window.The GATHER_DATABASE_STATS_JOB_PROC procedure collects statistics on database objects when the object has no previously gathered statistics or the existing statistics are stale because the underlying object has been modified significantly (more than 10% of the rows).”

以上这段描述还是比较清晰的,MAINTENANCE_WINDOW_GROUP维护窗口组中的工作日窗口(WEEKNIGHT_WINDOW,周一到周五)会在每个工作日的22:00启动并于第二天的6:00结束,在周末该维护窗口组中的周末窗口(WEEKEND_WINDOW)从周六Sat的0点开始并持续48小时(你不难发现这2个窗口在周六0点到6点之间存在overlay,实际的情况是WEEKEND_WINDOW窗口是从周六的0点整到周一的0点,具体可以观察dba_scheduler_windows视图的NEXT_START_DATE列,这里不再赘述)。在数据库一直打开的情况下,GATHER_STATS_JOB会伴随维护窗口一起被启动,默认情况下如果到维护窗口关闭该JOB仍未结束则将被终止(这取决于该JOB的属性stop_on_window_close),剩下的有待收集信息的对象将在下一个维护窗口中得到处理;如果数据库一直处于关闭的状态,并在某维护窗口的时间范围内该DB被打开,那么相应的维护窗口会被立即激活(ACTIVE),同时GATHER_STATS_JOB自动作业也会被启动,但该自动作业仅会在一个窗口中自动运行一次(因REASON="ORA-01014: ORACLE shutdown in progress"等原因失败的不算做一次)。

以上介绍了GATHER_STATS_JOB的运行周期,和我们要介绍的问题没有直接的联系。我们这里要谈的是,GATHER_STATS_JOB自动统计信息收集作业每次启动时是由针对性地收集统计信息的而非对数据库中所有schema下的对象都分析一遍;以上引用的文字中介绍了该JOB挑选分析对象的条件,即:

  1. 对象之前从未收集过统计信息,或由于某些原因没有统计信息
  2. 对象的统计信息相对陈旧(stale),是否陈旧的评判标准是由上次收集信息到此次收集期间被修改过的行数超过10%

条件1显得理所当然,剔除一些复杂的情况,一个对象没有统计信息的原因往往是这个对象刚刚被创建或者加载到数据库中,并且用户没有手动地去收集过统计信息,那么Oracle有充分的理由去分析这些对象。而后者则体现了查询优化器对统计信息陈旧度的容忍在超过10%的情况下导致执行计划偏差的可能性将大幅上升,为了遏制这种势头有必要再次统计这些对象。

让我们来看看GATHER_STATS_JOB针对”陈旧”(stale)统计信息的实际表现:

SQL> select * from global_name;

GLOBAL_NAME
------------------------------------
www.oracledatabase12g.com

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE	10.2.0.1.0	Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> conn maclean/maclean
Connected.

SQL> create table need_analyze tablespace users as select rownum t1 from dba_objects where rownum<10001;
Table created. 

SQL> select count(*)  from need_analyze;

  COUNT(*)
----------
     10000

SQL> select num_rows,blocks from dba_tables where table_name='NEED_ANALYZE';

  NUM_ROWS     BLOCKS
---------- ----------

/* 以上创建了一张具有10000行记录的测试用表,因为是新建的所以没有num_rows和blocks等等统计信息 */

/* 手动调用GATHER_STATS_JOB自动作业 */

SQL> begin
dbms_scheduler.run_job(job_name=>'SYS.GATHER_STATS_JOB',use_current_session=>true);
end;
/

SQL> select num_rows,blocks from dba_tables where table_name='NEED_ANALYZE';

  NUM_ROWS     BLOCKS
---------- ----------
     10000	   20

/* 删除999条记录,即不到10%的数据 */

SQL> delete need_analyze where rownum<1000;
999 rows deleted. 

SQL> commit;
Commit complete.

/* 再次调用GATHER_STATS_JOB */

begin
dbms_scheduler.run_job(job_name=>'SYS.GATHER_STATS_JOB',use_current_session=>true);
end;
/

/* 可以看到统计信息并未被更新 */

SQL> select num_rows,blocks from dba_tables where table_name='NEED_ANALYZE';

  NUM_ROWS     BLOCKS
---------- ----------
     10000	   20

SQL> delete need_analyze where rownum<2;
1 row deleted. 

SQL> commit;
Commit complete.

SQL> begin
dbms_scheduler.run_job(job_name=>'SYS.GATHER_STATS_JOB',use_current_session=>true);
end;
/  2    3    4  

PL/SQL procedure successfully completed.

SQL> select num_rows,blocks from dba_tables where table_name='NEED_ANALYZE';

  NUM_ROWS     BLOCKS
---------- ----------
     10000	   20

SQL>  delete need_analyze where rownum<2;
1 row deleted. 

SQL> commit;
Commit complete.

SQL> begin
dbms_scheduler.run_job(job_name=>'SYS.GATHER_STATS_JOB',use_current_session=>true);
end;
/  2    3    4  

PL/SQL procedure successfully completed.

SQL> select num_rows,blocks from dba_tables where table_name='NEED_ANALYZE';

  NUM_ROWS     BLOCKS
---------- ----------
      8999	   20

/* 可以看到修改的行数必须超过10%后才会被收集 */

有的朋友肯定要问Oracle是怎么知道某张表是否有过DML操作,而DML操作又涉及到了多少行数据呢?这都是通过表监控特性(a table monitoring facility)来实现的,当初始化参数STATISTICS_LEVEL设置为TYPICAL或ALL时默认启用这种特性。Oracle会默认监控表上的INSERT,UPDATE,DELETE以及表是否被TRUNCATE截断,并记录这些操作数量的近似值到数据字典。我们可以通过访问user_tab_modifications视图来了解这些信息:

SQL> delete need_analyze;
8999 rows deleted.

SQL> commit;
Commit complete.

SQL> select * from user_tab_modifications where table_name='NEED_ANALYZE';
no rows selected

/* 从实际的DML操作完成到*_tab_modifications视图到更新可能存在几分钟的延迟 */

/* 通过dbms_stats包中的FLUSH_DATABASE_MONITORING_INFO存储过程可以
   将这些监控数据刷新到字典中  */

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
PL/SQL procedure successfully completed

SQL> col table_name for a20

SQL> select table_name,inserts,updates,deletes,timestamp from user_tab_modifications where table_name='NEED_ANALYZE';

TABLE_NAME		INSERTS    UPDATES    DELETES TIMESTAMP
-------------------- ---------- ---------- ---------- ---------
NEED_ANALYZE		      0 	 0	 8999 26-MAR-11

/* 可以看到*_tab_modifications视图中记录了上次收集统计信息以来
   NEED_ANALYZE表上删除过8999条记录,因为测试时仅用少量的串行DML,所以这里十分精确 */

SQL> set autotrace on;

/* 通过以上执行计划可以猜测,monitoring监控数据来源于MON_MODS_ALL$基表上 */

SQL> desc sys.MON_MODS_ALL$;
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJ#						    NUMBER
 INSERTS					    NUMBER
 UPDATES					    NUMBER
 DELETES					    NUMBER
 TIMESTAMP					    DATE
 FLAGS						    NUMBER
 DROP_SEGMENTS					    NUMBER

SQL> select * from mon_mods_all$ where obj#=(select object_id from dba_objects where object_name='NEED_ANALYZE');

      OBJ#    INSERTS	 UPDATES    DELETES TIMESTAMP	   FLAGS DROP_SEGMENTS
---------- ---------- ---------- ---------- --------- ---------- -------------
     52565	    0	       0       8999 26-MAR-11	       0	     0

/* 需要注意的该mon_mods_all$修改监控基表仅记录上次该对象统计信息以来的修改(modify)情况,
   并不能做为某张表的实际修改历史记录来利用 */

虽然我们现在对GATHER_STATS_JOB在如何选择分析对象的条件上更清晰了,但是不少朋友可能还是会疑惑难道Oracle不对那些长久以来没有显著修改的表一直不予以收集信息吗?这似乎有悖于我们的常识,试看下例子:

/ * NEED_ANALYZE现在扮演一张静态表,它上次被分析是在2011年3月26日 */

SQL> select last_analyzed from dba_tables where table_name='NEED_ANALYZE';

LAST_ANAL
---------
26-MAR-11

SQL> select sysdate from dual;
SYSDATE
---------
26-MAR-11

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> host
[maclean@rh8 ~]$ su - root
Password: 

/* 我们把时钟调快到2012年的12月30日,希望我们能安全度过2012! */

[root@rh8 ~]# date -s "2012-12-30 00:00:00"
Sun Dec 30 00:00:00 CST 2012
[root@rh8 ~]# date
Sun Dec 30 00:00:01 CST 2012

[maclean@rh8 ~]$ exit
exit

SQL> startup;
ORACLE instance started.
Total System Global Area  167772160 bytes
Fixed Size		    1218292 bytes
Variable Size		   75499788 bytes
Database Buffers	   83886080 bytes
Redo Buffers		    7168000 bytes
Database mounted.
Database opened.

SQL> select sysdate from dual;

SYSDATE
---------
30-DEC-12

/* 再次手动调用GATHER_STATS_JOB自动作业 */

SQL> set timing on;
SQL> begin
dbms_scheduler.run_job(job_name=>'SYS.GATHER_STATS_JOB',use_current_session=>true);
end;
/
  2    3    4
PL/SQL procedure successfully completed.

Elapsed: 00:00:00.33

/* :-),运行结束不到1s */

SQL>  select last_analyzed from dba_tables where table_name='NEED_ANALYZE';

LAST_ANAL
---------
26-MAR-11

是的,默认情况下GATHER_STATS_JOB不会反复去分析那些静态表,无论过去”多久”。

好了,我们需要对GATHER_STATS_JOB和DBMS_STATS包下属的统计信息收集存储过程(gather_*_stats)有一个饱满的认识,他们远没有我们想象的那么2,实际上这个GATHER_STATS_JOB调用的PROGRAM存储过程是DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC,虽然这是一个内部存储过程(interal procedure);但实际上它和我们经常手动调用的DBMS_STATS.GATHER_DATABASE_STATS收集整库统计信息的存储过程在以GATHER AUTO选项运行时的行为方式上几乎一致,主要的区别是GATHER_DATABASE_STATS_JOB_PROC总是优先收集那些急需收集统计信息的对象,这保证了在维护窗口关闭之前那些最需要收集的统计信息总是能得到满足。而在手动调用GATHER_DATABASE_STATS等存储过程时因为不需要考虑窗口时间线的限制,而不论优先级。

to be continued ………….

  1. Applies to:
    Oracle Server – Enterprise Edition – Version: 10.2.0.1 to 10.2.0.4 – Release: 10.2 to 10.2
    Information in this document applies to any platform.
    ***Checked for relevance on 19-Nov-2010***
    Goal
    How to diagnose the issues where GATHER_STATS_JOB job is not running automatically in the maintenance windows. (WEEKEND_WINDOW, WEEKNIGHT_WINDOW)
    Solution
    Data Collection:
    ===========
    > Download and run the Data Collection script from here
    > Output file : job_diag.html.

    This script will provide formatted output from the queries required to collect information for diagnosing
    GATHER_STATS_JOB issues. Please upload the output file to the SR.

    You can copy paste the Data Collection Script from here as well.
    set markup html on spool on
    spool job_diag.html
    SET echo on
    SELECT object_id, object_name FROM DBA_OBJECTS WHERE object_type = ‘JOB';
    SELECT owner,job_name,job_creator,schedule_owner,schedule_name,schedule_type,start_date,end_date,job_class,enabled,auto_drop,restartable,state,run_count,retry_count,last_start_date,last_run_duration,last_run_duration,next_run_date,logging_level FROM DBA_SCHEDULER_JOBS WHERE job_name =’GATHER_STATS_JOB';
    SELECT * FROM DBA_SCHEDULER_JOB_LOG WHERE job_name =’GATHER_STATS_JOB’ ORDER BY log_date DESC;
    SELECT * FROM DBA_SCHEDULER_JOB_RUN_DETAILS WHERE job_name =’GATHER_STATS_JOB’ ORDER BY log_date DESC;
    SELECT * FROM DBA_SCHEDULER_WINDOWS;
    SELECT * FROM DBA_SCHEDULER_WINGROUP_MEMBERS;
    SELECT SYSTIMESTAMP FROM dual;
    SELECT dbms_scheduler.stime FROM dual ;
    show parameter STATISTICS_LEVEL;
    spool off
    set markup html off spool on

    Please note that since it collects output in html format , the on screen output may not be readable. So please execute the complete script and review the job_diag.html file.

    Step by step checklist:
    ================
    1. Check if the GATHER_STATS_JOB exists in the database:
    SELECT object_id, object_name FROM dba_objects WHERE object_type = ‘JOB';

    The output should look like:
    OBJECT_ID OBJECT_NAME
    ….. …………….
    54901 GATHER_STATS_JOB
    ….. …………….

    If it is not appearing in the output of the query, then it does not exist and can be recreated
    by running following scripts:
    conn as sysdba
    exec dbms_scheduler.add_window_group_member(‘MAINTENANCE_WINDOW_GROUP’, ‘WEEKNIGHT_WINDOW’);
    exec dbms_scheduler.add_window_group_member(‘MAINTENANCE_WINDOW_GROUP’,’WEEKEND_WINDOW’);

    (Though above two statements are included in catmwin script but they are needed because of the reason given in check 6.)

  2. Applies to:
    Oracle Server – Enterprise Edition – Version: 10.1.0.2 to 10.2.0.4 – Release: 10.1 to 10.2
    Information in this document applies to any platform.
    Checked for relevance on 03-Nov-2010
    Goal
    Often users want to know if the GATHER_STATS_JOB completed successfully?
    This article outlines how to find out answer for the GATHER _STATS_JOB, but is equally applicable to any scheduled job.
    Solution

    The STATE column from DBA_SCHEDULER_JOBS provides information on the status of a particular job. It can be queried as follows:

    SELECT job_name, state
    FROM dba_scheduler_jobs
    WHERE job_name=’GATHER_STATS_JOB';

    There are four types of jobs that are not running:

    * FAILED
    * BROKEN
    * DISABLED
    * COMPLETED

    Note that if a job has recently completed successfully, but is scheduled to run again, the job state is set to ‘SCHEDULED’.
    A job is marked as ‘COMPLETED’ if ‘end_date’ or ‘max_runs’ (in dba_scheduler_jobs) is reached.

    To further analyze details of each job run, for example:

    * “Why it failed?”
    * “What the actual start time was?”
    * “How long the job ran?”

    and so on , query the DBA_SCHEDULER_JOB_RUN_DETAILS view.

    As an example, the following statement illustrates the status of job “GSJ”:

    SELECT log_id, job_name, status, to_char(log_date,’DD-MON-YYYY HH24:MI’) log_date
    FROM dba_scheduler_job_run_details
    WHERE job_name = ‘GSJ';

    LOG_ID JOB_NAME STATUS LOG_DATE
    ———- ———————- ———— —————–
    69 GSJ SUCCEEDED 02-JUN-2006 03:14
    124 GSJ SUCCEEDED 03-JUN-2006 03:15
    133 GSJ FAILURE 04-JUN-2006 03:00
    146 GSJ FAILURE 05-JUN-2006 03:01

  3. Applies to:
    Oracle Server – Enterprise Edition – Version: 11.1.0.6 and later [Release: 11.1 and later ]
    Information in this document applies to any platform.
    ***Checked for relevance on 13-Oct-2010***
    Goal
    Question: Why Has the GATHER_STATS_JOB been removed in 11g?
    Solution
    Answer:

    Oracle Database 10g introduced the concept automated maintenance task execution during maintenance windows implemented via a WEEKNIGHT_WINDOW and WEEKEND_WINDOW schedule. This included statistics collection by means of the GATHER_STATS_JOB.

    In Oracle Database 11g, the WEEKNIGHT_WINDOW and WEEKEND_WINDOW schedules (defined in Oracle Database 10g) are replaced with daily maintenance windows (such as SUNDAY_WINDOW, MONDAY_WINDOW etc). These were replaced in order to add increased flexibility and manageability. One can query dba_scheduler_windows to check the window definitions.

    Automatic Maintenance Tasks (including the automated Optimizer Statistics Gathering task) are defined to execute within these daily windows. Using Enterprise Manager is the preferred way to control Automatic Maintenance Tasks, although the DBMS_AUTO_TASK_ADMIN package can also be used.

    ‘Automatic Maintenance Tasks Management ‘ is an 11g new feature and was implemented to increase the flexibility of statistics collection and to avoid potential resourcing issue when maintenance jobs run alongside user operations. Maintenance operations can potentially use a lot of resource which may, in extreme cases, affect other jobs. To address this, in 11g, maintenance operations are closely linked to resource manager to manage the resources that are used and share them more efficiently.

    From the

    Oracle� Database New Features Guide
    11g Release 1 (11.1)
    Part Number B28279-03

    “This feature ensures that work during maintenance operations is not affected and that user activity gets the necessary resources to complete.”

    By spreading the workload over multiple weeknights and managing the resource usage, this feature provides the flexibility to allow maintenance jobs to complete in a timely fashion and for user operations to be unaffected.

    Users are still completely free to define other maintenance windows and change start times and durations for the daily maintenance windows.

  4. Applies to:
    Oracle Server – Enterprise Edition – Version: 10.2.0.4 and later [Release: 10.2 and later ]
    Information in this document applies to any platform.
    Goal

    How to change GATHER_STATS_JOB or any job to run on a different schedule?
    Solution

    1. Changing the Gather_Stats_Job, you will need to change the Schedule that it is assigned to.

    At the moment it is set to the Weeknight_window which is 7 days a week.

    You will need to assign it to another schedule.

    2. Create the new schedule.

    BEGIN
    DBMS_SCHEDULER.create_schedule (
    schedule_name => ‘‘,
    start_date => SYSTIMESTAMP,
    repeat_interval => ‘freq=daily;byday=MON, TUE, WED, THU, FRI;byhour=0;byminute=0;bysecond=0′,
    end_date => NULL,
    comments => ‘Repeats daily for ever’);
    END;

    3. Then assign the job to the schedule:

    SQL> exec sys.dbms_scheduler.disable( ‘”SYS”.”GATHER_STATS_JOB”‘ );
    SQL> exec sys.dbms_scheduler.set_attribute( name => ‘”SYS”.”GATHER_STATS_JOB”‘, attribute =>
    ‘schedule_name’, value => ‘‘);
    SQL> exec sys.dbms_scheduler.enable( ‘”SYS”.”GATHER_STATS_JOB”‘ );

    This should reschedule the job run_time to only M to F.

    Note: Repeat_Interval parameter (byday) can be assigned any order of days.

    4. To check the schedule details:

    SELECT schedule_name,start_date FROM user_scheduler_schedules;

    SCHEDULE_NAME START_DATE
    ————————– ————————–
    TEST_SCHEDULE 27-APR-04 11.32.33.604343 AM -05:00

    5. To check job details:

    SELECT job_name, enabled FROM user_scheduler_jobs;

    JOB_NAME ENABL
    —————————— —–
    TEST_PROGRAM_SCHEDULE_JOB TRUE
    TEST_SELF_CONTAINED_JOB TRUE

    6. Other useful views:

    Data Dictionary Views
    ================
    [DBA | ALL | USER ] _Scheduler_jobs
    [DBA | ALL | USER ] _scheduler_jobs_args
    [DBA | ALL | USER ] _scheduler_running_jobs
    [DBA | ALL ] _scheduler_job_classes
    [DBA | ALL | USER ] _scheduler_job_log
    [DBA | ALL | USER ] _scheduler_job_run_details
    [DBA | ALL | USER ] _scheduler _programs
    [DBA | ALL | USER ] _scheduler_program_args

  5. Applies to:
    Oracle Server – Enterprise Edition – Version: 10.1.0.2 to 10.2.0.3 – Release: 10.1 to 10.2
    Information in this document applies to any platform.
    Symptoms

    GATHER_STATS_JOB scheduler job is not running in your database.
    It could be that you recently applied a DST patch or upgraded the database by applying 10.2.0.3 patch set ,etc

    SQL> select OWNER,JOB_NAME,STATE,SCHEDULE_NAME from dba_scheduler_jobs where job_name=
    2 ‘GATHER_STATS_JOB';

    OWNER JOB_NAME STATE SCHEDULE_NAME
    —– ——————– ————— ———————

    SYS GATHER_STATS_JOB SCHEDULED MAINTENANCE_WINDOW_GROUP

    To check the members under this group , use

    SQL> SELECT * FROM DBA_SCHEDULER_WINGROUP_MEMBERS;

    WINDOW_GROUP_NAME WINDOW_NAME
    —————————— ——————–
    MAINTENANCE_WINDOW_GROUP WEEKNIGHT_WINDOW
    MAINTENANCE_WINDOW_GROUP WEEKEND_WINDOW

    clear col
    set lines 150 pages 99 trimspool on echo on
    col window_name for a16
    col resource_plan for a13
    col repeat_interval for a43
    col duration for a15
    col enabled for a10
    col active for a10

    select window_name, resource_plan,
    repeat_interval, duration, enabled, active
    from dba_scheduler_windows;

    1 select WINDOW_NAME,RESOURCE_PLAN, START_DATE,REPEAT_INTERVAL,END_DATE,DURATION,ENABLED,ACTIVE
    2* from dba_scheduler_windows
    SQL> /

    WINDOW_NAME RESOURCE_PLAN START_DATE REPEAT_INTERVAL END_DATE
    ——————– ——————– ——————– —————————— ——————–
    DURATION ENABL ACTIV
    ——————– —– —–
    WEEKNIGHT_WINDOW freq=daily;byday=MON,TUE,WED,T
    HU,FRI;byhour=22;byminute=0; b
    ysecond=0
    +000 08:00:00 TRUE TRUE

    WEEKEND_WINDOW freq=daily;byday=SAT;byhour=0;
    byminute=0;bysecond=0
    +002 00:00:00 TRUE FALSE

    The job status is shown as scheduled and the windows under the maintenance window group are also enabled

    In case the job is disabled (STATE under dba_scheduler_jobs) , then use

    EXEC DBMS_SCHEDULER.ENABLE (‘GATHER_STATS_JOB’);

    In case the window is disabled (ENABLED under dba_scheduler_windows) , then use

    EXEC DBMS_SCHEDULER.ENABLE (‘SYS.WINDOWNAME’);

    Here the WINDOWNAME has to be replaced with the window which is disabled

    Also, remember that for GATHER_STATS_JOB to properly work, you would have to set the STATISTICS_LEVEL initialization parameter to at least TYPICAL.

    Cause
    Bug 5688806 has been filed for one of such cases, but the bug was suspended as required information was not available.
    Solution
    As a workaround for this issue,run the following scripts which would recreate the windows and stats job

    10.2
    =====
    SQL> exec dbms_scheduler.drop_job(‘auto_space_advisor_job’);

    Windows
    SQL> @%ORACLE_HOME%/rdbms/admin/catnomwn.sql

    OR

    Unix
    SQL>@$ORACLE_HOME/rdbms/admin/catnomwn.sql
    Above script will drop the window group, windows, job and job class

    Windows
    SQL> @%ORACLE_HOME%/rdbms/admin/catmwin.sql

    OR

    Unix
    SQL>@$ORACLE_HOME/rdbms/admin/catmwin.sql

    Above script will create all the required objects including auto_space_advisor_job

    10.1
    =====

    Windows
    SQL> @%ORACLE_HOME%/rdbms/admin/catnomwn.sql

    OR

    Unix
    SQL>@$ORACLE_HOME/rdbms/admin/catnomwn.sql
    Above script will drop the window group, windows, job and job class

    Windows
    SQL> @%ORACLE_HOME%/rdbms/admin/catmwin.sql

    OR

    Unix
    SQL>@$ORACLE_HOME/rdbms/admin/catmwin.sql

    Note: – AUTO_SPACE_ADVISOR_JOB runs the Automatic Segment Advisor, which identifies segments that have space available for reclamation, and then makes recommendations that you can view with Enterprise Manager or a set of PL/SQL package procedures. This job was introduced in 10gR2 and is not present in 10gR1

    In case the job still does not run, then please enable the cjq tracing by setting the following event. This needs to be set before the scheduled run i.e 10 P.M so maybe you can start it at 9:45 PM

    connect / as sysdba
    oradebug setospid < --This is OS process id for Cjq process (You can also use SETORAPID ) where orapid is Oracle process id for Cjq process)
    oradebug tracefile_name
    oradebug Event 27402 trace name context forever, level 37

    Wait for 1 hour. Then turn it off

    oradebug Event 27402 trace name context off
    oradebug tracefile_name

    Upload the trace file.

    Also upload following query results to the SR.

    Rem Pls run as sys i.e “connect / as sysdba” on each instance
    set pagesize 2000
    set echo on
    column OBJECT_NAME format a30
    spool job

    select object_id, OBJECT_NAME from dba_objects
    where OBJECT_TYPE = ‘JOB';
    select * from dba_scheduler_jobs where job_name =’GATHER_STATS_JOB';
    select * from DBA_SCHEDULER_JOB_LOG where job_name =’GATHER_STATS_JOB';
    select * from DBA_SCHEDULER_JOB_RUN_DETAILS where job_name =’GATHER_STATS_JOB';
    spool off

    Rem Internal Job Queues Summary report.
    Rem Pls run as sys i.e “connect / as sysdba” on each instance

    column systimestamp format a35
    column ATTR_INTV format a37
    column ATTR_TSTAMP format a37
    column start_time format a35
    column obj_name format a20
    column name format a30
    column value format a30
    column window_name format a10
    column job_queue_name format a18
    column job_type format 9999
    column flag format 9999
    column status format a6
    column “SEQUENCE#” format 9999
    column id format 99999

    set pagesize 100
    set echo on
    spool scheduler_qs

    — Report Internal Queues
    –select job_queue_name, JOB_TYPE,
    — (select object_name from dba_objects where object_id = job_oid union select name from
    — scheduler$_lwjob_obj where obj# = job_oid) OBJ_NAME, FLAG, start_time from x$jskjobq;
    — scheduler$_lwjob_obj is not available

    select * from x$jskjobq;
    — Report current time in scheduler format
    select dbms_scheduler.stime from dual;

    — Report all global attributes
    SELECT o.name, a.*
    FROM sys.obj$ o, sys.scheduler$_global_attribute a
    WHERE o.obj# = a.obj# AND BITAND(a.flags,1) != 1;

    — Report resource manager info
    select * from v$rsrc_plan;
    select * from v$rsrc_plan_history;
    select id,name, active_sessions, execution_waiters from v$rsrc_consumer_group;
    select * from v$rsrc_consumer_group;
    select state from v$rsrc_session_info where state != ‘NOT MANAGED’ ;
    select name, CPU_WAITS, CONSUMED_CPU_TIME, YIELDS,
    CPU_WAIT_TIME from V$RSRC_CONSUMER_GROUP;

    Rem Scheduler job and window logs
    column additional_info format a40
    set pagesize 1000
    set linesize 150
    column REQ_START_DATE format a37
    column ACTUAL_START_DATE format a37
    column log_date format a37
    column window_name format a10
    column operation format a9
    column error# format 99999
    column status format a9
    column CPU_USED format a16
    column job_name format a18
    column run_duration format a15
    connect / as sysdba
    select log_date, owner, job_name, operation, status from dba_scheduler_job_log
    union
    select log_date, ‘SYS’, window_name, operation, status from
    dba_scheduler_window_log
    order by 1;

    Rem select log_date, actual_start_date-req_start_date, job_name, error#, status, run_duration,
    Rem cpu_used, additional_info
    Rem from dba_scheduler_job_run_details order by log_id;

    select job_name, CAST (actual_start_date-req_start_date AS INTERVAL DAY(2) TO SECOND(2)) DELAY
    from dba_scheduler_job_run_details order by log_id;

    Rem select additional_info from dba_scheduler_job_run_details where additional_info is not null;

    spool off;

    Rem Internal Job Queues Summary report for 10.2.
    Rem Pls run as sys i.e “connect / as sysdba” on each instance
    set long 400
    column log_date format a37
    column operation format a8
    column status format a6
    column additional_info format a400

    set pagesize 1000
    set linesize 120
    column systimestamp format a37
    column next_start_date format a40
    column start_date format a40
    column manual_open_time format a40
    column manual_duration format a40
    column duration format a40
    column end_date format a40
    column last_start_date format a40
    column window_name format a26
    column enabled format a6
    column active format a6
    column systimestamp format a35
    column ATTR_INTV format a37
    column ATTR_TSTAMP format a37
    column start_time format a35
    column obj_name format a20
    column name format a30
    column value format a30
    column job_queue_name format a18
    column job_type format 9999
    column flag format 9999
    column status format a6
    column “SEQUENCE#” format 9999
    column id format 99999

    spool wintest
    select * from dba_scheduler_windows ;

    select log_date, window_name, operation, status, substr(additional_info,1,350) from
    dba_scheduler_window_log
    where operation != ‘CREATE’
    order by 1;
    select log_date, window_name, operation,substr(additional_info,1,350) from
    dba_scheduler_window_log order by 1 ;
    select window_name, substr(additional_info,1,350) x from
    dba_scheduler_window_log where additional_info is not null;

    — Report Internal Queues v10.2
    select job_queue_name, JOB_TYPE,
    (select object_name from dba_objects where object_id = job_oid
    ) OBJ_NAME, FLAG, start_time from x$jskjobq;

    — Report current time in scheduler format
    select dbms_scheduler.stime from dual;

    — Report all global attributes
    SELECT o.name, a.*
    FROM sys.obj$ o, sys.scheduler$_global_attribute a
    WHERE o.obj# = a.obj# AND BITAND(a.flags,1) != 1;

    — Report resource manager info
    select * from v$rsrc_plan;
    select * from v$rsrc_plan_history;
    select id,name, active_sessions, execution_waiters from v$rsrc_consumer_group;
    select * from v$rsrc_consumer_group;
    select state from v$rsrc_session_info where state != ‘NOT MANAGED’ ;
    select name, CPU_WAITS, CONSUMED_CPU_TIME, YIELDS,
    CPU_WAIT_TIME from V$RSRC_CONSUMER_GROUP;
    spool off;

  6. 非常好的帖子
    曾经有个非常牛的公司面试DBA,你们数据库如何收集统计信息?自动收集?那说一下自动收集的算法?不知道怎么敢用自动收集? 有点小尴尬

  7. Pingback: GATHER_STATS_JOB: Stopped by Scheduler. Consider increasing the maintenance window duration if this happens frequently. – ORACLE数据库数据恢复、性能优化、故障诊断来问问MACLEAN