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 ENABLED
Auto Space Advisor ENABLED
SQL Tuning Advisor ENABLED
where "Auto Optimizer stats Collection" is the task name we're 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 be completed using the Dbms_auto_task_admin package: Sql>exec dbms_auto_task_admin. DISABLE (client_name = ' Auto Optimizer stats collection ', Operation =>null,window_name =>null);P The L/sql process was 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 2where 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+00004:00:00tuesday_window freq=daily;byday=tue;byhour=22;byminute=0; Bysecond=0+00004:00:00wednesday_window freq=daily;byday=wed;byhour=22;byminute=0; Bysecond=0+00004:00:00thursday_window freq=daily;byday=thu;byhour=22;byminute=0; Bysecond=0+00004:00:00friday_window Freq=daily;byday=fri;byhour=22;byminute=0; Bysecond=0+00004:00:00saturday_window freq=daily;byday=sat;byhour=6;byminute=0; Bysecond=0+00020:00:00sunday_window freq=daily;byday=sun;byhour=6;byminute=0; bysecond=0+00020:00:007 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>begin2 Dbms_scheduler. DISABLE (3 name = "SYS". ") Friday_window "', 4 force = TRUE); 5END; The 6/PL/SQL process has completed successfully. 2. Modify the duration of the task in minutes: Sql>begin2 Dbms_scheduler. Set_attribute (3 name = "SYS". ") Friday_window "', 4 attribute = ' DURATION ', 5 value = = Numtodsinterval (" minute "); 6END; The 7/PL/SQL process has completed successfully. 3. Start execution time, byhour=2, indicating 2 o'clock start of execution: Sql>begin2 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 '); 6END; The 7/PL/SQL process has completed successfully. 4. Open task: Sql>begin2 dbms_scheduler. ENABLE (3 name = "SYS". ") Friday_window "'); 4END; 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 2where 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;byday=wed;byhour=22;by minute=0; Bysecond=0+00004:00:00friday_window freq=weekly; Byday=mon; byhour=2; byminute=0; Bysecond=0+00003:00:00saturday_window freq=daily;byday=sat;byhour=6;byminute=0; Bysecond=0+00020:00:00thursday_window freq=daily;byday=thu;byhour=22;byminute=0; Bysecond=0+00004:00:00tuesday_window freq=daily;byday=tue;byhour=22;byminute=0; Bysecond=0+00004:00:00sunday_window freq=daily;byday=sun;byhour=6;byminute=0; Bysecond=0+00020:00:00monday_window freq=daily;byday=mon;byhour=22;byminute=0; bysecond=0+00004:00:007 rows selected
Oracle automatically collects statistics