Automatic maintenance tasks controls the automatic optimizer statistics

SELECT COALESCE(p.name, (SELECT name FROM v$database)) AS DATABASE, CASE WHEN c.con_id = 1 THEN 'CDB' WHEN c.con_id = 2 THEN 'SEED' ELSE 'PDB' END AS type_db, c.client_name, c.status, c.attributes FROM cdb_autotask_client c LEFT JOIN v$pdbs p ON c.con_id = p.con_id ORDER BY type_db, client_name; DATABASE TYPE_DB CLIENT_NAME STATUS ATTRIBUTES _______________ __________ __________________________________ __________ _________________________________________________________ CON1 CDB auto optimizer stats collection ENABLED ON BY DEFAULT, VOLATILE, SAFE TO KILL CON1 CDB auto space advisor ENABLED ON BY DEFAULT, VOLATILE, SAFE TO KILL CON1 CDB sql tuning advisor ENABLED ONCE PER WINDOW, ON BY DEFAULT, VOLATILE, SAFE TO KILL CON1PDB1 PDB auto optimizer stats collection DISABLED ON BY DEFAULT, VOLATILE, SAFE TO KILL CON1PDB2 PDB auto optimizer stats collection DISABLED ON BY DEFAULT, VOLATILE, SAFE TO KILL CON1PDB1 PDB auto space advisor DISABLED ON BY DEFAULT, VOLATILE, SAFE TO KILL CON1PDB2 PDB auto space advisor DISABLED ON BY DEFAULT, VOLATILE, SAFE TO KILL CON1PDB2 PDB sql tuning advisor DISABLED ONCE PER WINDOW, ON BY DEFAULT, VOLATILE, SAFE TO KILL CON1PDB1 PDB sql tuning advisor DISABLED ONCE PER WINDOW, ON BY DEFAULT, VOLATILE, SAFE TO KILL 9 rows selected. 

SELECT COALESCE(p.name, (SELECT name FROM v$database)) AS DATABASE, CASE WHEN c.con_id = 1 THEN 'CDB' WHEN c.con_id = 2 THEN 'SEED' ELSE 'PDB' END AS type_db, c.window_name, c.autotask_status, c.optimizer_stats FROM cdb_autotask_window_clients c LEFT JOIN v$pdbs p ON c.con_id = p.con_id ORDER BY type_db, window_name; DATABASE TYPE_DB WINDOW_NAME AUTOTASK_STATUS OPTIMIZER_STATS _______________ __________ ___________________ __________________ __________________ CON1 CDB FRIDAY_WINDOW ENABLED ENABLED CON1 CDB MONDAY_WINDOW ENABLED ENABLED CON1 CDB SATURDAY_WINDOW ENABLED ENABLED CON1 CDB SUNDAY_WINDOW ENABLED ENABLED CON1 CDB THURSDAY_WINDOW ENABLED ENABLED CON1 CDB TUESDAY_WINDOW ENABLED ENABLED CON1 CDB WEDNESDAY_WINDOW ENABLED ENABLED CON1PDB1 PDB FRIDAY_WINDOW DISABLED DISABLED CON1PDB2 PDB FRIDAY_WINDOW DISABLED DISABLED CON1PDB1 PDB MONDAY_WINDOW DISABLED DISABLED CON1PDB2 PDB MONDAY_WINDOW DISABLED DISABLED CON1PDB1 PDB SATURDAY_WINDOW DISABLED DISABLED CON1PDB2 PDB SATURDAY_WINDOW DISABLED DISABLED CON1PDB1 PDB SUNDAY_WINDOW DISABLED DISABLED CON1PDB2 PDB SUNDAY_WINDOW DISABLED DISABLED CON1PDB1 PDB THURSDAY_WINDOW DISABLED DISABLED CON1PDB2 PDB THURSDAY_WINDOW DISABLED DISABLED CON1PDB1 PDB TUESDAY_WINDOW DISABLED DISABLED CON1PDB2 PDB TUESDAY_WINDOW DISABLED DISABLED CON1PDB1 PDB WEDNESDAY_WINDOW DISABLED DISABLED CON1PDB2 PDB WEDNESDAY_WINDOW DISABLED DISABLED 21 rows selected. 

set serveroutput on; declare type all_windows_name is table of varchar2(30); all_windows all_windows_name := all_windows_name('SUNDAY_WINDOW','MONDAY_WINDOW','TUESDAY_WINDOW','WEDNESDAY_WINDOW','THURSDAY_WINDOW','FRIDAY_WINDOW','SATURDAY_WINDOW'); begin for wname in all_windows.first .. all_windows.last loop dbms_auto_task_admin.enable( client_name => 'auto optimizer stats collection', operation => 'auto optimizer stats job', window_name => all_windows(wname) ); dbms_output.put_line('Enable stats job for: ' || all_windows(wname)); end loop; dbms_auto_task_admin.enable(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL); dbms_auto_task_admin.enable; end; / 

 perl /u02/app/oracle/product/19.0.0.0/dbhome_2/rdbms/admin/catcon.pl -b autostatsupdate -l /tmp /tmp/autostats.sql 

 DATABASE TYPE_DB WINDOW_NAME AUTOTASK_STATUS OPTIMIZER_STATS _______________ __________ ___________________ __________________ __________________ CON1 CDB FRIDAY_WINDOW ENABLED ENABLED CON1 CDB MONDAY_WINDOW ENABLED ENABLED CON1 CDB SATURDAY_WINDOW ENABLED ENABLED CON1 CDB SUNDAY_WINDOW ENABLED ENABLED CON1 CDB THURSDAY_WINDOW ENABLED ENABLED CON1 CDB TUESDAY_WINDOW ENABLED ENABLED CON1 CDB WEDNESDAY_WINDOW ENABLED ENABLED CON1PDB1 PDB FRIDAY_WINDOW ENABLED ENABLED CON1PDB2 PDB FRIDAY_WINDOW ENABLED ENABLED CON1PDB1 PDB MONDAY_WINDOW ENABLED ENABLED CON1PDB2 PDB MONDAY_WINDOW ENABLED ENABLED CON1PDB1 PDB SATURDAY_WINDOW ENABLED ENABLED CON1PDB2 PDB SATURDAY_WINDOW ENABLED ENABLED CON1PDB1 PDB SUNDAY_WINDOW ENABLED ENABLED CON1PDB2 PDB SUNDAY_WINDOW ENABLED ENABLED CON1PDB1 PDB THURSDAY_WINDOW ENABLED ENABLED CON1PDB2 PDB THURSDAY_WINDOW ENABLED ENABLED CON1PDB1 PDB TUESDAY_WINDOW ENABLED ENABLED CON1PDB2 PDB TUESDAY_WINDOW ENABLED ENABLED CON1PDB1 PDB WEDNESDAY_WINDOW ENABLED ENABLED CON1PDB2 PDB WEDNESDAY_WINDOW ENABLED ENABLED 21 rows selected. 

 DATABASE TYPE_DB CLIENT_NAME STATUS ATTRIBUTES _______________ __________ __________________________________ __________ _________________________________________________________ CON1 CDB auto optimizer stats collection ENABLED ON BY DEFAULT, VOLATILE, SAFE TO KILL CON1 CDB auto space advisor ENABLED ON BY DEFAULT, VOLATILE, SAFE TO KILL CON1 CDB sql tuning advisor ENABLED ONCE PER WINDOW, ON BY DEFAULT, VOLATILE, SAFE TO KILL CON1PDB1 PDB auto optimizer stats collection ENABLED ON BY DEFAULT, VOLATILE, SAFE TO KILL CON1PDB2 PDB auto optimizer stats collection ENABLED ON BY DEFAULT, VOLATILE, SAFE TO KILL CON1PDB1 PDB auto space advisor DISABLED ON BY DEFAULT, VOLATILE, SAFE TO KILL CON1PDB2 PDB auto space advisor DISABLED ON BY DEFAULT, VOLATILE, SAFE TO KILL CON1PDB2 PDB sql tuning advisor DISABLED ONCE PER WINDOW, ON BY DEFAULT, VOLATILE, SAFE TO KILL CON1PDB1 PDB sql tuning advisor DISABLED ONCE PER WINDOW, ON BY DEFAULT, VOLATILE, SAFE TO KILL 9 rows selected. 

select * from dba_autotask_client; select * from dba_autotask_window_clients; select * from dba_scheduler_windows; select * from dba_autotask_schedule order by start_time; -- Disable Auto Task exec dbms_auto_task_admin.disable; -- Disable auto stats collection job exec dbms_auto_task_admin.disable(client_name=>'auto optimizer stats collection',operation=>NULL,window_name=>NULL); -- To change timing of the auto stats collection job exec dbms_scheduler.set_attribute(name=>'SUNDAY_WINDOW',attribute=>'repeat_interval',value=>'freq=daily;byday=MON;byhour=5;byminute=0;bysecond=0'); -- To change duration of the maintenance window exec dbms_scheduler.set_attribute(name=>'SUNDAY_WINDOW',attribute=>'duration',value=> numtodsinterval(12,'hour')); -- Create new or additional maintenance window select * from dba_scheduler_groups; select * from dba_scheduler_group_members; exec dbms_scheduler.create_window(window_name=>'EXTRA_WINDOW',duration=>numtodsinterval(4,'hour'),resource_plan=>'DEFAULT_MAINTENANCE_PLAN',repeat_interval=>'FREQ=DAILY;BYHOUR=23;BYMINUTE=0;BYSECOND=0'); exec dbms_scheduler.add_group_member(group_name=>'MAINTENANCE_WINDOW_GROUP',member=>'EXTRA_WINDOW'); -- Drop maintenance window exec dbms_scheduler.drop_window(window_name=>'EXTRA_WINDOW'); exec dbms_scheduler.remove_group_member(group_name=>'MAINTENANCE_WINDOW_GROUP',member=>'EXTRA_WINDOW');