Oracle's automated collection of statistical information

One, Oracle 11g

The automatic collection of statistics data is available in the 11g version of Oracle. One of the steps in deploying 11g Oracle software during deployment is to prompt for the ability to start this feature (which is enabled by default).

Here's how to enable and disable this feature:

1. View the tasks and status of automatically collecting statistics:

Sql> select Client_name,status from Dba_autotask_client; Client_name                 STATUS--------------------------------------------------------Auto Optimizer stats collection      Enabledauto Space Advisor             Enabledsql Tuning Advisor             enabled where "Auto Optimizer stats Collection" is the task name we are looking for to automatically collect statistics, and its status is currently enabled.

2. Prohibit automatic collection of statistical information tasks

Tasks that disable automatic collection of statistics can use the Dbms_auto_task_admin package to complete:sql> exec dbms_auto_task_admin. DISABLE (client_name = ' Auto Optimizer stats collection ', operation = Null,window_name = NULL);P The L/SQL process has completed successfully. Sql> select Client_name,status from Dba_autotask_client; Client_name                 STATUS--------------------------------------------------------Auto Optimizer stats collection      Disabledauto Space Advisor             Enabledsql Tuning Advisor             enabled this time the "Auto Optimizer Stats Collection" task has been disabled.

3. Enable automatic collection of statistical information tasks

Sql> exec dbms_auto_task_admin. ENABLE (client_name = ' Auto Optimizer stats collection ', operation = Null,window_name = NULL);P The L/SQL process has completed successfully. Sql> select Client_name,status from Dba_autotask_client; Client_name                 STATUS--------------------------------------------------------Auto Optimizer stats collection      Enabledauto Space Advisor             Enabledsql Tuning Advisor             ENABLED

This feature seems to bring the convenience of statistical data collection, but the time that Oracle 11g automatically collects statistics is 22:00--2:00
This time period is often the peak of the business, to the already tense system to bring a greater burden. Therefore, the time of automatic execution should be changed to the idle period.

4. Get the execution time of the current automatic collection statistics:

Sql> Select T1.window_name,t1.repeat_interval,t1.duration from Dba_scheduler_windows t1,dba_scheduler_wingroup_ Members T2 2 where T1.window_name=t2.window_name and T2.window_group_name in (' Maintenance_window_group ', ' Bsln_ Maintain_stats_sched '); Window_name Repeat_interval duration-- ---------------------------- -------------------------------------------------------------------------------- --- ----------------------------------------------------------------------------Monday_window Freq=daily;byda y=mon;byhour=22;byminute=0; Bysecond=0 +000 04:00:00tuesday_window freq=daily;byday=tue;byhour=22;byminute= 0; Bysecond=0 +000 04:00:00wednesday_window freq=daily;byday=wed;byhour=22;byminute= 0; Bysecond=0 +000 04:00:00thursday_window freq=daily;byday=thu;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:00sunday_window freq=daily;byday=sun;byhour=6;byminute= 0; Bysecond=0 +000 20:00:00 7 rows selected

which
Window_name: Task Name

Repeat_interval: Task recurrence interval

DURATION: Duration

The steps to modify are as follows:

1. Stop Task:sql> BEGIN 2 Dbms_scheduler. DISABLE (3 name = "SYS". ")  Friday_window "', 4 force = TRUE);  5 END; The 6/PL/SQL process has completed successfully. 2. Modify the duration of the task, in minutes:sql> BEGIN 2 Dbms_scheduler. Set_attribute (3 name = "SYS". ")  Friday_window "', 4 attribute = ' DURATION ', 5 value = = Numtodsinterval (" minute ");    6 END; The 7/PL/SQL process has completed successfully. 3. Start execution time, byhour=2, indicating that the execution begins at 2 o'clock:sql> begin 2 Dbms_scheduler. Set_attribute (3 name = "SYS". ") Friday_window "', 4 attribute = ' Repeat_interval ', 5 value = ' freq=weekly; Byday=mon; byhour=2; byminute=0;  Bysecond=0 ');  6 END; The 7/PL/SQL process has completed successfully. 4. Start Task:sql> BEGIN 2 Dbms_scheduler. ENABLE (3 name = "SYS". ")  Friday_window "');  4 END; The 5/PL/SQL process has completed successfully. 5. View the modified condition:sql> Select T1.window_name,t1.repeat_interval,t1.duration from Dba_scheduler_windows t1,dba_scheduler _wingroup_members T2 2 where T1.window_name=t2.window_name and T2.window_group_name in (' Maintenance_window_group ', ' Bsln_mainTain_stats_sched '); Window_name Repeat_interval duration-- ---------------------------- -------------------------------------------------------------------------------- --- ----------------------------------------------------------------------------Wednesday_window Freq=daily;byda y=wed;byhour=22;byminute=0; Bysecond=0 +000 04:00:00friday_window freq=weekly; Byday=mon; byhour=2; byminute=0; Bysecond=0 +000 03: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 7 rows selected

Oracle's automated collection of statistical information