明明设定的维护窗口时间是22点，但是统计信息收集任务却在第二天14点启动了

oracle19.9 rac pdb+oracle linux7.9

oracle19c多租户环境默认安装似乎都存在这个问题，建议您查查看自己的数据库环境，及早避免此坑。

select client_name,status from dba_autotask_client;

故障现象：

明明设定的维护窗口时间的22点，但是统计信息收集任务却在第二天14点启动了

select * FROM DBA_SCHEDULER_WINDOWS;
select t1.window_name,t1.repeat_interval,t1.duration from dba_scheduler_windows t1,dba_scheduler_wingroup_members t2
where t1.window_name=t2.window_name and t2.window_group_name in ('MAINTENANCE_WINDOW_GROUP');

select * from (select ACTUAL_START_DATE,RUN_DURATION,STATUS FROM dba_scheduler_job_run_details
where job_name like 'ORA$AT_OS_OPT%'
order by actual_start_date desc) where rownum < 30;

SELECT * FROM DBA_AUTOTASK_CLIENT_HISTORY order by WINDOW_START_TIME;

问题分析：

平常时候统计信息收集job并没有每隔四个小时跑一次，每天只运行一次。
文档 ( Doc ID 1576688.1 )讲的是如果窗口期设置的非常长的时候，比如7-8个小时，会每隔4个小时跑一次维护作业。
查找时区发现：
目前看是schedule的时区设置有一些问题。根据提供的信息显示，当前环境 scheduler 的默认时区是 PST8PDT，
这是美国的太平洋标准时间的西8区，也就是比国际时间慢8小时。而中国统一时间是东8区，比国际时间快8小时。之间差16个小时。
PST8PDT 的时候，如果是夜间22点，那么实际22+8+8=次日的14点，所以统计信息收集作业就会在14点执行。

set serveroutput on
DECLARE
PROCEDURE display(p_param IN VARCHAR2) AS
l_result VARCHAR2(50);
BEGIN
DBMS_SCHEDULER.get_scheduler_attribute(
attribute => p_param,
value => l_result);
DBMS_OUTPUT.put_line(RPAD(p_param, 30, ' ') || ' : ' || l_result);
END;
BEGIN
display('current_open_window');
display('default_timezone');
display('email_sender');
display('email_server');
display('event_expiry_time');
display('log_history');
display('max_job_slave_processes');
END;
/

处理办法：

执行下面的语句，将SCHEDULER的默认时区修改为Asia/Shanghai,这样可以恢复为夜间22点收集统计信息。

设置:

exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('default_timezone','Asia/Shanghai');

SELECT * FROM DBA_AUTOTASK_WINDOW_CLIENTS;

select * FROM DBA_SCHEDULER_WINDOWS;

关于oracle统计信息job任务相关，metalink提供：

1. 上传生成的/tmp/scheduler.html

conn / as sysdba
set wrap on
set pagesize 9999
spool /tmp/scheduler.html
set markup html on
set time on
alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
select sysdate from dual;
select sessiontimezone from dual;
SELECT * FROM DBA_SCHEDULER_PROGRAMS;
SELECT * FROM DBA_SCHEDULER_WINDOWS;
SELECT * FROM DBA_SCHEDULER_JOB_CLASSES;
SELECT * FROM DBA_SCHEDULER_WINGROUP_MEMBERS;
SELECT * FROM DBA_SCHEDULER_JOBS;
SELECT * FROM DBA_AUTOTASK_CLIENT_JOB;
SELECT * FROM DBA_AUTOTASK_CLIENT;
SELECT * FROM DBA_AUTOTASK_OPERATION;
SELECT * FROM DBA_AUTOTASK_TASK;
SELECT * FROM DBA_AUTOTASK_SCHEDULE ORDER BY 2;
SELECT * FROM DBA_AUTOTASK_JOB_HISTORY order by WINDOW_START_TIME;
SELECT * FROM DBA_AUTOTASK_CLIENT_HISTORY order by WINDOW_START_TIME;
SELECT * FROM DBA_AUTOTASK_WINDOW_CLIENTS;
SELECT * FROM DBA_SCHEDULER_WINDOW_GROUPS;
SELECT * FROM DBA_AUTOTASK_WINDOW_HISTORY;
DECLARE
PROCEDURE display(p_param IN VARCHAR2) AS
l_result VARCHAR2(50);
BEGIN
DBMS_SCHEDULER.get_scheduler_attribute(
attribute => p_param,
value => l_result);
DBMS_OUTPUT.put_line(RPAD(p_param, 30, ' ') || ' : ' || l_result);
END;
BEGIN
display('current_open_window');
display('default_timezone');
display('email_sender');
display('email_server');
display('event_expiry_time');
display('log_history');
display('max_job_slave_processes');
END;
/
set markup html off
spool off

2. 上传生成的/tmp/jobtime.txt

conn / as sysdba
spool /tmp/jobtime.txt
select to_char(systimestamp, 'DD/MM/YYYY HH24:MI:SS TZR') from dual;
select to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') from dual;
Drop table SYS.SYSTIME_TEST purge;
create table SYS.SYSTIME_TEST (col1 varchar2(30 char));
begin
DBMS_SCHEDULER.DROP_JOB ('SYSTIME_TEST_JOB');
end;
/
begin
dbms_scheduler.create_job('SYSTIME_TEST_JOB','PLSQL_BLOCK','begin insert into SYS.SYSTIME_TEST select to_char(systimestamp, ''DD/MM/YYYY HH24:MI:SS TZR'') from dual; insert into SYS.SYSTIME_TEST select to_char(sysdate, ''DD/MM/YYYY HH24:MI:SS'') from dual; insert into SYS.SYSTIME_TEST select SESSIONTIMEZONE from dual; commit; end;',start_date=>null, enabled=>true,repeat_interval => null);
end;
/
exec dbms_lock.sleep(5);
select * from SYS.SYSTIME_TEST;
Drop table SYS.SYSTIME_TEST purge;
spool off

进一步研究发现问题种子pdb的时区就是PST8PDT，所以建出来的pdb自然也是这了，创建完成后需要做时区修改：


Default Scheduler Timezone Value In PDB$SEED Different Than CDB (Doc ID 2702230.1)

On 12C, 18c and 19c, using DBCA General mode create DB, the default timezone of scheduler of PDB is different with CDB$ROOT.
The default timezone of the scheduler of PDB is PST8PDT on 19c and Etc/UTC on 12C & 18c no matter what the timezone of the scheduler is in CDB$ROOT.
But using create database command or using DBCA customize mode, the default timezone of the scheduler of PDB is same with CDB$ROOT.

It is expected behavior. 

Please refer to the below bug raised an enhancement request.
  Unpublished Bug 30076391 : DIFFERENT TIMEZONES OF SCHEDULER IN CDB$ROOT AND PDB USING DBCA GENERAL MODE CREATE DB

SOLUTION
Please set the default timezone of the scheduler of PDB, even using DBCA General mode to create DB.

Please try using DBCA in customize mode, where the default timezone of the scheduler of PDB is the same as CDB$ROOT.

Please refer the below document to set the scheduler timezone.

  DBMS_SCHEDULER or DBMS_JOB And DST / Timezones Explained (Doc ID 467722.1)

其它参考：

禁用收集：

exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);

启用收集：

exec DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);

select client_name,status from dba_autotask_client;

调整任务执行窗口时间:
--周一:9点执行，运行窗口4小时

begin
  sys.dbms_scheduler.set_attribute(name => 'SYS.MONDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=MON;byhour=9;byminute=0;bysecond=0');
  sys.dbms_scheduler.set_attribute(name => 'SYS.MONDAY_WINDOW', attribute => 'duration', value => '0 04:00:00');
end;
/

--周二:9点执行，运行窗口4小时

begin
  sys.dbms_scheduler.set_attribute(name => 'SYS.TUESDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=TUE;byhour=9;byminute=0;bysecond=0');
  sys.dbms_scheduler.set_attribute(name => 'SYS.TUESDAY_WINDOW', attribute => 'duration', value => '0 04:00:00');
end;
/

--周三:9点执行，运行窗口4小时

begin
  sys.dbms_scheduler.set_attribute(name => 'SYS.WEDNESDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=WED;byhour=9;byminute=0;bysecond=0');
  sys.dbms_scheduler.set_attribute(name => 'SYS.WEDNESDAY_WINDOW', attribute => 'duration', value => '0 04:00:00');
end;
/

--周四:9点执行，运行窗口4小时

begin
  sys.dbms_scheduler.set_attribute(name => 'SYS.THURSDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=THU;byhour=9;byminute=0;bysecond=0');
  sys.dbms_scheduler.set_attribute(name => 'SYS.THURSDAY_WINDOW', attribute => 'duration', value => '0 04:00:00');
end;
/

--周五:9点执行，运行窗口4小时

begin
  sys.dbms_scheduler.set_attribute(name => 'SYS.FRIDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=FRI;byhour=9;byminute=0;bysecond=0');
  sys.dbms_scheduler.set_attribute(name => 'SYS.FRIDAY_WINDOW', attribute => 'duration', value => '0 04:00:00');
end;

/

--周六:6点执行，运行窗口22小时

begin
  sys.dbms_scheduler.set_attribute(name => 'SYS.SATURDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=SAT;byhour=6;byminute=0;bysecond=0');
  sys.dbms_scheduler.set_attribute(name => 'SYS.SATURDAY_WINDOW', attribute => 'duration', value => '0 22:00:00');
end;

/

--周日:6点执行，运行窗口22小时

begin
  sys.dbms_scheduler.set_attribute(name => 'SYS.SUNDAY_WINDOW', attribute => 'repeat_interval', value => 'freq=daily;byday=SUN;byhour=6;byminute=0;bysecond=0');
  sys.dbms_scheduler.set_attribute(name => 'SYS.SUNDAY_WINDOW', attribute => 'duration', value => '0 22:00:00');
end;
/