关于11g+的统计信息收集

1.a),check the task name(in database 11g the name is :autooptimizer stats collection ) and the windowgroup: 
>>select client_name,status, attributes,window_group,service_name from dba_autotask_client whereclient_name='auto optimizer statscollection'; 

CLIENT_NAME STATUS WINDOW_GROUP 
---------------------------------------- -------------------------------------- 
auto optimizer stats collection ENABLEDORA$AT_WGRP_OS 


1.b),check the window name of the windowgroup: 
>>select window_name from DBA_SCHEDULER_WINGROUP_MEMBERSwhere window_group_name ='ORA$AT_WGRP_OS'; 

WINDOW_NAME 
------------------------------ 
MONDAY_WINDOW 
TUESDAY_WINDOW 
WEDNESDAY_WINDOW 
THURSDAY_WINDOW 
FRIDAY_WINDOW 
SATURDAY_WINDOW 
SUNDAY_WINDOW 

1.c),check the start time and the duration of everywindow: 
>>select window_name, REPEAT_INTERVAL, DURATION fromDBA_SCHEDULER_WINDOWS wherewindow_name='MONDAY_WINDOW'; 
WINDOW_NAME REPEAT_INTERVALDURATION 
--------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
MONDAY_WINDOW freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0+000 04:00:00 

11g+ Automatic Maintenance Tasks

• What is the name of the default stats gathering job on 11g?

  The automatic statistics gathering job on 11g is called "autooptimizer stats collection".

• What are the default windows for the automated maintenance task torun?

  In 11g daily maintenance windows are provided. by default these aredefined as :
  
  
  • Weeknights: Starts at 10 p.m. and ends at 2 a.m.
  • Weekends: Starts at 6 a.m. is 20 hours long.
  
  See:
  
  
  Oracle® Database Administrator's Guide
  11g Release 2 (11.2)
  Part Number E17120-05
  Automated Maintenance Tasks Reference
  Table 26-1 Predefined Maintenance Windows
  http://docs.oracle.com/cd/E18283_01/server.112/e17120/tasks006.htm#CIHJHGCA 
  
  Document 743507.1 How to Benefit fromAutomatic Maintenance Tasks Following the Removal of theGATHER_STATS_JOB in 11g?

• How do you change the default windows for the automated maintenancetask to run?

  Maintenance windows can be modified using the DBMS_SCHEDULER PL/SQLpackage. For details see:
  
  Oracle® Database Administrator's Guide
  11g Release 2 (11.2)
  Part Number E17120-05
  Configuring Maintenance Windows
  http://docs.oracle.com/cd/E18283_01/server.112/e17120/tasks004.htm
    
  Here is an example to change the default time for the dailymaintenance window for example to 2:00 AM instead of 10:00 PM usingthe following commands :
  
  BEGIN
    dbms_scheduler.disable(
     name  => 'SATURDAY_WINDOW');
    dbms_scheduler.set_attribute(
     name     => 'SATURDAY_WINDOW',
      attribute=> 'repeat_interval',
     value    =>'freq=daily;byday=SAT;byhour=02;byminute=0;bysecond=0');
    dbms_scheduler.enable(
      name =>'SATURDAY_WINDOW');
  END;
  /
  
  BEGIN
    dbms_scheduler.disable(
     name  => 'SUNDAY_WINDOW');
    dbms_scheduler.set_attribute(
     name     => 'SUNDAY_WINDOW',
      attribute=> 'repeat_interval',
     value    =>'freq=daily;byday=SUN;byhour=02;byminute=0;bysecond=0');
    dbms_scheduler.enable(
      name =>'SUNDAY_WINDOW');
  END;
  /
  
  BEGIN
    dbms_scheduler.disable(
     name  => 'MONDAY_WINDOW');
    dbms_scheduler.set_attribute(
     name     => 'MONDAY_WINDOW',
      attribute=> 'repeat_interval',
     value    =>'freq=daily;byday=MON;byhour=02;byminute=0;bysecond=0');
    dbms_scheduler.enable(
      name =>'MONDAY_WINDOW');
  END;
  /
  
  BEGIN
    dbms_scheduler.disable(
     name  => 'TUESDAY_WINDOW');
    dbms_scheduler.set_attribute(
     name     => 'TUESDAY_WINDOW',
      attribute=> 'repeat_interval',
     value    =>'freq=daily;byday=TUE;byhour=02;byminute=0;bysecond=0');
    dbms_scheduler.enable(
      name =>'TUESDAY_WINDOW');
  END;
  /
  
  BEGIN
    dbms_scheduler.disable(
     name  => 'WEDNESDAY_WINDOW');
    dbms_scheduler.set_attribute(
     name     => 'WEDNESDAY_WINDOW',
      attribute=> 'repeat_interval',
     value    =>'freq=daily;byday=WED;byhour=02;byminute=0;bysecond=0');
    dbms_scheduler.enable(
      name =>'WEDNESDAY_WINDOW');
  END;
  /
  
  BEGIN
    dbms_scheduler.disable(
     name  => 'THURSDAY_WINDOW');
    dbms_scheduler.set_attribute(
     name     => 'THURSDAY_WINDOW',
      attribute=> 'repeat_interval',
     value    =>'freq=daily;byday=THU;byhour=02;byminute=0;bysecond=0');
    dbms_scheduler.enable(
      name =>'THURSDAY_WINDOW');
  END;
  /
  
  BEGIN
    dbms_scheduler.disable(
     name  => 'FRIDAY_WINDOW');
    dbms_scheduler.set_attribute(
     name     => 'FRIDAY_WINDOW',
      attribute=> 'repeat_interval',
     value    =>'freq=daily;byday=FRI;byhour=02;byminute=0;bysecond=0');
    dbms_scheduler.enable(
      name =>'FRIDAY_WINDOW');
  END;
  /
• 
  
• 
  
• 
  
• 参考：FAQ: Automatic Statistics Collection (文档 ID1233203.1)