Oracle 数据库中可以定时执行一些任务( oracle job )，如何查看指定任务的历史执行时间？
实际应用的故事背景是这样的：
医院的医生挂号排班表是通过 oracle job 定时执行存储过程写入排班信息，存储过程执行完后患者就可以在微信小程序上挂一个星期内的号。
实际观察发现定时任务执行越来越慢，直到患者晚上守着时间挂不出来指定日期的号，最终打了投诉电话这事儿才暴露出来：定时任务执行出了问题。
根据业务工程师反馈，最早的任务执行时间是晚上10:00点钟，后来定时任务运行得越来越慢，超过夜里12:00点任务都没有执行完。患者本打算守到夜里12点抢号结果排号的定时任务执行超时了，于是将这事儿投诉给了12345市政府便民热线。
Oracle 的定时任务信息可以通过 dba_jobs 查看：
select t.JOB,t.WHAT 执行内容,t.TOTAL_TIME 总耗时,t.LAST_DATE 上次执行时间,t.FAILURES 失败次数,t.BROKEN 是否定时执行,t.INTERVAL 执行间隔from dba_jobs torder by t.job;
dba_jobs 数据字典中存了 job ID, 执行内容、总耗时、上次执行时间等信息，但并没有记录某一次执行结果花费的时间。结合 TOTAL_TIME 和 INTERVAL 可以通过记录历史数据推测出某一次任务的执行耗时。
TOTAL_TIME 是任务创建以来总的执行耗时，即对每次执行时间累加后的结果，单位是秒。我们可以创建一个中间表用来记录历史数据，通过对比两次 TOTAL_TIME 的结果即可计算出后面每次任务的执行耗时。
中间表创建语句为：
create table MYDBA_JOB_HISTORY(job NUMBER not null,what VARCHAR2(4000),total_time NUMBER,last_date DATE,failures NUMBER,broken VARCHAR2(1),mark_time DATE default sysdate);
插入历史数据的语句：
insert into MYDBA_JOB_HISTORYselect t.JOB,t.WHAT,t.total_time,t.LAST_DATE,t.FAILURES,t.BROKEN,sysdatefrom dba_jobs torder by t.job;
在原 dba_jobs 表的基础上加了一个 mark_time 字段用来记录数据插入时间，通过 mark_time 来区分不同时段的历史数据。
如果只是临时观察，可以手动执行 insert 语句。如果需要长期观察，可以将 insert 语句写到存储过程中，通过 oracle job 定时执行。
创建存储过程：
create or replace procedure myproc_job_history asbegininsert into MYDBA_JOB_HISTORYselect t.JOB,t.WHAT,t.total_time,t.LAST_DATE,t.FAILURES,t.BROKEN,sysdatefrom sys.dba_jobs t;commit;end;
创建 job :
declare job number;beginsys.dbms_job.submit(job => job,what => 'MYPROC_JOB_HISTORY;',next_date => to_date('2024-09-03 22:05:00','yyyy-mm-dd hh24:mi:ss'),interval => 'sysdate + 1');commit;end;
如果后期不再需要任务自动运行，可以将 job 禁用或删除：
通过 dba_jobs 查看 job ID:
select t.JOB, t.WHAT, t.BROKEN from dba_jobs t order by t.JOB desc;禁用 job :
begindbms_job.broken(843, true);commit;end;
删除 job :
begindbms_job.remove(843);commit;end;
注意
创建存储过程时需要 dba_jobs 的 select 权限，不然存储过程会出现编译错误无法执行。通过 user_errors 视图可以查看创建存储过程中遇到的错误：
SELECT * FROM USER_ERRORSWHERE NAME = 'MYPROC_JOB_HISTORY';-- PL/SQL: ORA-00942: table or view does not exist
可以使用 sys 账号登录数据库，给创建存储过程的用户授权：
sqlplus / as sysdbaSQL> grant select on sys.dba_jobs to user_name;
全文完。
原文始发于微信公众号（生有可恋）：如何查看 Oracle 定时任务执行耗时
- 左青龙
- 微信扫一扫
-
- 右白虎
- 微信扫一扫
-
评论