---11g is Monday to Friday 22:00-2:00 Saturday Sunday 6:00-4:00
SELECT W.window_name, W.repeat_interval, W.duration, w.enabled
From Dba_autotask_window_clients C, Dba_scheduler_windows W
WHERE C.window_name = W.window_name
and c.optimizer_stats = ' ENABLED ';
Window_name Repeat_interval DURATION
-------------------- ------------------------------------------------------------ ---------------
Sunday_window freq=daily;byday=sun;byhour=6;byminute=0; Bysecond=0 +000 20:00:00
Friday_window freq=daily;byday=fri;byhour=22;byminute=0; Bysecond=0 +000 04:00:00
Wednesday_window freq=daily;byday=wed;byhour=22;byminute=0; Bysecond=0 +000 04:00:00
Saturday_window freq=daily;byday=sat;byhour=6;byminute=0; Bysecond=0 +000 20:00:00
Thursday_window freq=daily;byday=thu;byhour=22;byminute=0; Bysecond=0 +000 04:00:00
Tuesday_window freq=daily;byday=tue;byhour=22;byminute=0; Bysecond=0 +000 04:00:00
Monday_window freq=daily;byday=mon;byhour=22;byminute=0; Bysecond=0 +000 04:00:00
--Disable automatic collection
EXEC dbms_auto_task_admin. DISABLE (client_name = ' Auto Optimizer stats collection ', operation = Null,window_name = NULL);
--Enable automatic collection
EXEC dbms_auto_task_admin. ENABLE (client_name = ' Auto Optimizer stats collection ', operation = Null,window_name = NULL);
--View Job
Select Client_name,status from Dba_autotask_client;
client_name &NB Sp status
- -----------------------------------------------------------------------
Auto Optimizer stats collection enabled
Auto Space advisor &NBSP ; ENABLED
SQL Tuning Advisor & nbsp , ENA BLED
--Collect statistics for all users under the current database
EXEC dbms_stats.gather_database_stats (user);
--collects statistics for all objects under the current database user
EXEC dbms_stats.gather_schema_stats (user);
--collecting statistics for data dictionaries
exec dbms_stats.gather_dictionary_stats ();
--Get Global Statistics collection settings options
Select Dbms_stats.get_prefs (' method_opt ') from dual;
Select Dbms_stats.get_prefs (' concurrent ') from dual;
Select Dbms_stats.get_prefs (' granularity ') from dual;
--Set STATISTICS collection options for global
EXEC Dbms_stats. Set_param (' degree ', 4);
Some knowledge of the automated collection of statistics by Oracle 11g Systems
