Oracle 11g system automatically collects statistics, oracle11g
--- 11 GB is from Monday to Friday-Saturday-
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 STATUS
------------------------------------------------------------------------
Auto optimizer stats collection ENABLED
Auto space advisor ENABLED
SQL tuning advisor ENABLED
-- Collect statistics of all users in the current database
Exec dbms_stats.gather_database_stats (user );
-- Collect statistics on all objects in the current database user
Exec dbms_stats.gather_schema_stats (user );
-- Collect statistics of data dictionaries
Exec dbms_stats.gather_dictionary_stats ();
-- Obtain the global statistics collection setting Option
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 the global statistics collection Option
EXEC DBMS_STATS.SET_PARAM ('degree', 4 );