[Oracle] 11 GB automatic collection of statistics

Source: Internet
Author: User

In 11g, statistics are automatically collected at PM (Monday to Friday, 4 hours) and am (Saturday, Sunday, 20 hours) by default ), as follows:

select a.window_name, a.repeat_interval,a.duration  from dba_scheduler_windows a, dba_scheduler_wingroup_members b  where a.window_name = b.window_name    and b.window_group_name = 'MAINTENANCE_WINDOW_GROUP';WINDOW_NAME                    REPEAT_INTERVAL                                              DURATION------------------------------ ------------------------------------------------------------ --------------------WEDNESDAY_WINDOW               freq=daily;byday=WED;byhour=22;byminute=0; bysecond=0        +000 04:00:00FRIDAY_WINDOW                  freq=daily;byday=FRI;byhour=22;byminute=0; bysecond=0        +000 04:00:00SATURDAY_WINDOW                freq=daily;byday=SAT;byhour=6;byminute=0; bysecond=0         +000 20:00:00THURSDAY_WINDOW                freq=daily;byday=THU;byhour=22;byminute=0; bysecond=0        +000 04:00:00TUESDAY_WINDOW                 freq=daily;byday=TUE;byhour=22;byminute=0; bysecond=0        +000 04:00:00SUNDAY_WINDOW                  freq=daily;byday=SUN;byhour=6;byminute=0; bysecond=0         +000 20:00:00MONDAY_WINDOW                  freq=daily;byday=MON;byhour=22;byminute=0; bysecond=0        +000 04:00:00

Since many systems are still in the peak business hours at, it is necessary to adjust the time. This should be determined based on their own business. In our system, the adjustment is as follows:

From Monday to Friday, it starts at a.m. and lasts for 5 hours. It starts at a.m. on Saturday and Sunday and lasts for 10 hours.

Run the following statement with the sys User:

begin  sys.dbms_scheduler.set_attribute(name => 'SYS.MONDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=MON;byhour=1;byminute=0; bysecond=0');  sys.dbms_scheduler.set_attribute(name => 'SYS.MONDAY_WINDOW', attribute => 'duration', value => '0 05:00:00');end;/begin  sys.dbms_scheduler.set_attribute(name => 'SYS.TUESDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=TUE;byhour=1;byminute=0; bysecond=0');  sys.dbms_scheduler.set_attribute(name => 'SYS.TUESDAY_WINDOW', attribute => 'duration', value => '0 05:00:00');end;/begin  sys.dbms_scheduler.set_attribute(name => 'SYS.WEDNESDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=WED;byhour=1;byminute=0; bysecond=0');  sys.dbms_scheduler.set_attribute(name => 'SYS.WEDNESDAY_WINDOW', attribute => 'duration', value => '0 05:00:00');end;/begin  sys.dbms_scheduler.set_attribute(name => 'SYS.THURSDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=THU;byhour=1;byminute=0; bysecond=0');  sys.dbms_scheduler.set_attribute(name => 'SYS.THURSDAY_WINDOW', attribute => 'duration', value => '0 05:00:00');end;/begin  sys.dbms_scheduler.set_attribute(name => 'SYS.FRIDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=FRI;byhour=1;byminute=0; bysecond=0');  sys.dbms_scheduler.set_attribute(name => 'SYS.FRIDAY_WINDOW', attribute => 'duration', value => '0 05:00:00');end;/begin  sys.dbms_scheduler.set_attribute(name => 'SYS.SATURDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=SAT;byhour=1;byminute=0; bysecond=0');  sys.dbms_scheduler.set_attribute(name => 'SYS.SATURDAY_WINDOW', attribute => 'duration', value => '0 10:00:00');end;/begin  sys.dbms_scheduler.set_attribute(name => 'SYS.SUNDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=SUN;byhour=1;byminute=0; bysecond=0');  sys.dbms_scheduler.set_attribute(name => 'SYS.SUNDAY_WINDOW', attribute => 'duration', value => '0 10:00:00');end;/
The result after the preceding statement is successfully executed is as follows:
WINDOW_NAME                    REPEAT_INTERVAL                                              DURATION------------------------------ ------------------------------------------------------------ --------------------WEDNESDAY_WINDOW               freq=daily;byday=WED;byhour=1;byminute=0; bysecond=0         +000 05:00:00FRIDAY_WINDOW                  freq=daily;byday=FRI;byhour=1;byminute=0; bysecond=0         +000 05:00:00SATURDAY_WINDOW                freq=daily;byday=SAT;byhour=1;byminute=0; bysecond=0         +000 10:00:00THURSDAY_WINDOW                freq=daily;byday=THU;byhour=1;byminute=0; bysecond=0         +000 05:00:00TUESDAY_WINDOW                 freq=daily;byday=TUE;byhour=1;byminute=0; bysecond=0         +000 05:00:00SUNDAY_WINDOW                  freq=daily;byday=SUN;byhour=1;byminute=0; bysecond=0         +000 10:00:00MONDAY_WINDOW                  freq=daily;byday=MON;byhour=1;byminute=0; bysecond=0         +000 05:00:00
After the time adjustment is successful, the next step is to enable the 11g automatic collection statistics job. First, confirm that the current is not enabled:
SYS@PROD> select client_name,status from DBA_AUTOTASK_CLIENT where client_name='auto optimizer stats collection';CLIENT_NAME                                                      STATUS---------------------------------------------------------------- --------auto optimizer stats collection                                  DISABLED
Run the following statement to enable it:
BEGIN  dbms_auto_task_admin.enable(  client_name => 'auto optimizer stats collection',  operation => NULL,  window_name => NULL);END;/
Make sure that it is enabled:
SYS@PROD> select client_name,status from DBA_AUTOTASK_CLIENT where client_name='auto optimizer stats collection';CLIENT_NAME                                                      STATUS---------------------------------------------------------------- --------auto optimizer stats collection                                  ENABLED
Appendix: Statement for closing the job:
BEGIN   dbms_auto_task_admin.disable(   client_name => 'auto optimizer stats collection',   operation => NULL,   window_name => NULL); END;  /

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.