Query to check the current state of jobs in CDB_AUTOTASK_CLIENT of the database, auto optimizer stats collection job is disabled in PDBs.
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.
Query to check the configured window, here the autotask was disabled in PDBs.
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.
PL/SQL block to enable the auto optimizer stats job for each of the maintenance window
Also, enable the AUTO_TASKS window
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;
/
Used the above PL/SQL block to execute it in all the PDBs,
perl /u02/app/oracle/product/19.0.0.0/dbhome_2/rdbms/admin/catcon.pl -b autostatsupdate -l /tmp /tmp/autostats.sql
Check the configured window and the stats collection job
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.
Check the current state of jobs in DBA_AUTOTASK_CLIENT
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.
Other relevant queries
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');
Top comments (0)