How to check what automatic statistics collection is scheduled on 10g

Subject:  How to check what automatic statistics collection is scheduled on 10g
  Doc ID:  Note:377143.1 Type:  BULLETIN
  Last Revision Date:  16-FEB-2007 Status:  PUBLISHED
***
This article is being delivered in Draft form. and may contain errors.
Please use the MetaLink "Feedback" button to advise Oracle of any issues related to this article.
PURPOSE

This Note provides information on the How to check what automatic statistics collection is scheduled on 10g

SCOPE AND APPLICATION

Users collecting statistics on database objects for use by the Cost Based Optimizer (CBO)

How to check what automatic statistics collection is scheduled on 10g

With Oracle 10g, the gathering of statistics has become automated.
The GATHER_STATS_JOB that is built in the database creation process schedules automatic statistics collection.
The job initiates a 'program' of statistics gathering appropriate for the database in question.

The job details can be viewed by querying the DBA_SCHEDULER_JOBS view:
  select job_name, job_type, program_name, schedule_name, job_class
  from dba_scheduler_jobs
  where job_name = 'GATHER_STATS_JOB';

  JOB_NAME                       JOB_TYPE         PROGRAM_NAME        SCHEDULE_NAME             JOB_CLASS
  ------------------------------ ---------------- ------------------- ------------------------- ------------------------------
  GATHER_STATS_JOB                                GATHER_STATS_PROG   MAINTENANCE_WINDOW_GROUP  AUTO_TASKS_JOB_CLASS

The output shows that the 'GATHER_STATS_JOB' schedules a program 'GATHER_STATS_PROG' in the 'MAINTENANCE_WINDOW_GROUP' time schedule.
The PROGRAM_NAME 'GATHER_STATS_PROG' starts the DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC stored procedure:
  select  PROGRAM_ACTION
  from dba_scheduler_programs
  where PROGRAM_NAME = 'GATHER_STATS_PROG';

  PROGRAM_ACTION
  ----------------------------------------------
  dbms_stats.gather_database_stats_job_proc

The job is scheduled according to the value of the SCHEDULE_NAME field.
In this example, the schedule being used is: 'MAINTENANCE_WINDOW_GROUP'.
This schedule is defined in the DBA_SCHEDULER_WINGROUP_MEMBERS view:
  select *
  from DBA_SCHEDULER_WINGROUP_MEMBERS
  where WINDOW_GROUP_NAME = 'MAINTENANCE_WINDOW_GROUP';

  WINDOW_GROUP_NAME         WINDOW_NAME
  ------------------------- ------------------------------
  MAINTENANCE_WINDOW_GROUP  WEEKNIGHT_WINDOW
  MAINTENANCE_WINDOW_GROUP  WEEKEND_WINDOW

The meaning of these 'windows' can be found in 'DBA_SCHEDULER_WINDOWS':
  select window_name, repeat_interval, duration
  from dba_scheduler_windows
  where window_name in ('WEEKNIGHT_WINDOW', 'WEEKEND_WINDOW')

  WINDOW_NAME       REPEAT_INTERVAL                                                        DURATION
  ----------------- ---------------------------------------------------------------------- -------------
  WEEKNIGHT_WINDOW  freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0  +000 08:00:00
  WEEKEND_WINDOW    freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0                    +002 00:00:00

The meaning of these entries is as follows:

The WEEKNIGHT_WINDOW is scheduled each week day at 10PM. and should last a maximum of 8 hours.
The WEEKEND_WINDOW is scheduled each Saturday at 0AM and should last 2 days max

If the START_DATE and END_DATE columns (Not shown) are NULL, then this job will run continuously.

All these definitions can be found in the $ORACLE_HOME/rdbms/admin/catmwin.sql script.

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14270146/viewspace-677554/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14270146/viewspace-677554/