通过案例学调优之--JOB管理
Oracle在创建和管理job主要借助两个包，分别为DBMS_JOB和DBMS_SCHEDULER
[oracle@rh6 ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.1.0 Production on Mon Aug 25 17:02:15 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
11:03:43 SYS@ prod >show parameter job
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 1000
Job_queue_processes = 1000; 这个是运行JOB时候所起用的进程数，当然系统里面JOB大于这个数值后，就会有排队等候的，最小值是0，表示不运行JOB，最大值是1000
11:26:14 SCOTT@ prod >desc dbms_job
FUNCTION BACKGROUND_PROCESS RETURNS BOOLEANPROCEDURE BROKEN Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- JOB BINARY_INTEGER IN BROKEN BOOLEAN IN NEXT_DATE DATE IN DEFAULTPROCEDURE CHANGE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- JOB BINARY_INTEGER IN WHAT VARCHAR2 IN NEXT_DATE DATE IN INTERVAL VARCHAR2 IN INSTANCE BINARY_INTEGER IN DEFAULT FORCE BOOLEAN IN DEFAULTPROCEDURE INSTANCE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- JOB BINARY_INTEGER IN INSTANCE BINARY_INTEGER IN FORCE BOOLEAN IN DEFAULTPROCEDURE INTERVAL Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- JOB BINARY_INTEGER IN INTERVAL VARCHAR2 INPROCEDURE ISUBMIT Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- JOB BINARY_INTEGER IN WHAT VARCHAR2 IN NEXT_DATE DATE IN INTERVAL VARCHAR2 IN DEFAULT NO_PARSE BOOLEAN IN DEFAULTFUNCTION IS_JOBQ RETURNS BOOLEANPROCEDURE NEXT_DATE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- JOB BINARY_INTEGER IN NEXT_DATE DATE INPROCEDURE REMOVE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- JOB BINARY_INTEGER INPROCEDURE RUN Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- JOB BINARY_INTEGER IN FORCE BOOLEAN IN DEFAULTPROCEDURE SUBMIT Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- JOB BINARY_INTEGER OUT WHAT VARCHAR2 IN NEXT_DATE DATE IN DEFAULT INTERVAL VARCHAR2 IN DEFAULT NO_PARSE BOOLEAN IN DEFAULT INSTANCE BINARY_INTEGER IN DEFAULT FORCE BOOLEAN IN DEFAULTPROCEDURE USER_EXPORT Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- JOB BINARY_INTEGER IN MYCALL VARCHAR2 IN/OUTPROCEDURE USER_EXPORT Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- JOB BINARY_INTEGER IN MYCALL VARCHAR2 IN/OUT MYINST VARCHAR2 IN/OUTPROCEDURE WHAT Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- JOB BINARY_INTEGER IN WHAT VARCHAR2 IN
DBA_JOBS
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
字段（列） 类型 描述JOB NUMBER 任务的唯一标示号LOG_USER VARCHAR2(30) 提交任务的用户PRIV_USER VARCHAR2(30) 赋予任务权限的用户SCHEMA_USER VARCHAR2(30) 对任务作语法分析的用户模式LAST_DATE DATE 最后一次成功运行任务的时间LAST_SEC VARCHAR2(8) 如HH24:MM:SS格式的last_date日期的小时，分钟和秒THIS_DATE DATE 正在运行任务的开始时间，如果没有运行任务则为nullTHIS_SEC VARCHAR2(8) 如HH24:MM:SS格式的this_date日期的小时，分钟和秒NEXT_DATE DATE 下一次定时运行任务的时间NEXT_SEC VARCHAR2(8) 如HH24:MM:SS格式的next_date日期的小时，分钟和秒TOTAL_TIME NUMBER 该任务运行所需要的总时间，单位为秒BROKEN VARCHAR2(1) 标志参数，Y标示任务中断，以后不会运行INTERVAL VARCHAR2(200) 用于计算下一运行时间的表达式FAILURES NUMBER 任务运行连续没有成功的次数WHAT VARCHAR2(2000) 执行任务的PL/SQL块CURRENT_SESSION_LABEL RAW MLSLABEL 该任务的信任Oracle会话符CLEARANCE_HI RAW MLSLABEL 该任务可信任的Oracle最大间隙CLEARANCE_LO RAW MLSLABEL 该任务可信任的Oracle最小间隙NLS_ENV VARCHAR2(2000) 任务运行的NLS会话设置MISC_ENV RAW(32) 任务运行的其他一些会话参数
创建job：
DBMS_JOB.SUBMIT (
job OUT BINARY_INTEGER, --job参数的名称（上例的test_job） what IN VARCHAR2, --执行的存储过程 next_date IN DATE DEFAULT sysdate, --何时运行这个job interval IN VARCHAR2 DEFAULT 'null', --何时这个job被从新执行 no_parse IN BOOLEAN DEFAULT FALSE, --是否从新解析 instance IN BINARY_INTEGER DEFAULT any_instance, --指定哪个实例去执行（用在RAC环境下） force IN BOOLEAN DEFAULT FALSE --是否必须由执行的实例才能执行 );
案例测试：
1、建立测试表
117:17:45 SCOTT@ prod >CREATE TABLE DATE_LOG17:18:03 2 (create_date DATE CONSTRAINT create_date_pk PRIMARY KEY);Table created.Elapsed: 00:00:01.1517:18:06 SCOTT@ prod >desc date_log Name Null? Type ----------------------------------------------------------------- -------- -------------------------------------------- CREATE_DATE NOT NULL DATE
2、建立一个procedure
17:18:15 SCOTT@ prod >CREATE OR REPLACE PROCEDURE create_date_log_row17:18:27 2 IS17:18:27 3 BEGIN17:18:27 4 INSERT INTO date_log(create_date)VALUES(sysdate);17:18:27 5 END;17:18:27 6 /Procedure created.
3、建立一个job
17:40:02 SCOTT@ prod >begin17:40:23 2 dbms_job.submit(:jobno,'create_date_log_row;',sysdate,'sysdate+1/1440');17:40:31 3 commit;17:40:35 4 end;17:40:36 5 /PL/SQL procedure successfully completed.
4、查看测试表情况
17:41:31 SCOTT@ prod >select * from DATE_LOG;CREATE_DA---------25-AUG-1425-AUG-1425-AUG-1425-AUG-14
5、查看job的工作情况
17:42:28 SCOTT@ prod >col interval for a5017:42:36 SCOTT@ prod >r 1 select last_date,last_sec,next_date,next_sec,interval,broken from user_jobs 2* where job=24LAST_DATE LAST_SEC NEXT_DATE NEXT_SEC INTERVAL B--------- ---------------- --------- ---------------- -------------------------------------------------- -25-AUG-14 17:41:41 25-AUG-14 17:42:41 sysdate+1/1440 N LAST_DATE 第一次执行日期 LAST_SEC 第一次执行时间 NEXT_DATE 下一次执行日期 NEXT_SEC 下一次执行时间 INTERVAL 执行频率
6、中止和启动job的执行
17:43:22 SCOTT@ prod >exec dbms_job.broken(24,true);PL/SQL procedure successfully completed.17:43:45 SCOTT@ prod >commit; //注意要提交Commit complete.查看中断信息：17:43:47 SCOTT@ prod >select broken from user_jobs where job = 24;B-Y启动job：17:44:00 SCOTT@ prod >exec dbms_job.broken(24,false);PL/SQL procedure successfully completed.17:44:35 SCOTT@ prod >select broken from user_jobs where job = 24;B-N
7、修改job的执行时间
11:17:40 SCOTT@ prod >select job,log_user,last_date,last_sec,next_date,next_sec,interval,what from user_jobs
JOB LOG_USER LAST_DATE LAST_SEC NEXT_DATE NEXT_SEC INTERVAL WHAT---------- ---------- --------- ---------------- --------- ---------------- ---------- ---------- 23 SCOTT 26-AUG-14 11:17:40 26-AUG-14 11:18:40 sysdate+1/ create_dat 1440 e_log_row; 24 SCOTT 26-AUG-14 11:10:39 26-AUG-14 11:18:39 sysdate+3/ create_dat (24*60) e_log_row;
查询目前下次执行时间 11:17:40 SCOTT@ prod >select next_date,next_sec from user_jobs;NEXT_DATE NEXT_SEC--------- ----------------26-AUG-14 11:20:4026-AUG-14 11:21:40 修改下次执行之时间 SQL> exec dbms_job.next_date(24,sysdate+2/(24*60));SQL>commit; 查询目前下次执行时间 SQL> select next_date,next_sec from user_jobs; NEXT_DATE NEXT_SEC ---------- ---------------- 26-AUG-14 12:05:58 第五步 修改job执行频率 SQL> exec dbms_job.interval(24,'sysdate+3/(24*60)');SQL>commit; 查询执行频率 SQL> select interval from user_jobs; INTERVAL -------------------------------------- sysdate+3/(24*60)
8、删除job
SQL> exec dbms_job.REMOVE(24);SQL> commit; 11:25:33 SCOTT@ prod >exec dbms_job.remove(24);PL/SQL procedure successfully completed.11:25:49 SCOTT@ prod >commit;Commit complete.
附录：
其中的Interval的设置是一个难点，在此阐述几个常用的设置值：
1、 每分钟执行 Interval => TRUNC(sysdate,’mi’) + 1 / (24*60) 2、 每天定时执行 例如：每天的凌晨2点执行 Interval => TRUNC(sysdate) + 1 +2 / (24) 3、 每周定时执行 例如：每周一凌晨2点执行 Interval => TRUNC(next_day(sysdate,'星期一'))+2/24 4、 每月定时执行 例如：每月1日凌晨2点执行 Interval =>TRUNC(LAST_DAY(SYSDATE))+1+2/24 5、 每季度定时执行 例如每季度的第一天凌晨2点执行 Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 2/24 6、 每半年定时执行 例如：每年7月1日和1月1日凌晨2点 Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+2/24 7、 每年定时执行 例如：每年1月1日凌晨2点执行 Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'),6)+2/24 8、 每天午夜12点 'TRUNC(SYSDATE + 1)' 9、 每天早上8点30分 'TRUNC(SYSDATE + 1) + （8*60+30）/(24*60)' 10、 每星期二中午12点 'NEXT_DAY(TRUNC(SYSDATE ), ''TUESDAY'' ) + 12/24' 11、 每个月第一天的午夜12点 'TRUNC(LAST_DAY(SYSDATE ) + 1)' 12、 每个季度最后一天的晚上11点 'TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), 'Q' ) -1/24' 13、 每星期六和日早上6点10分 'TRUNC(LEAST(NEXT_DAY(SYSDATE, ''SATURDAY"), NEXT_DAY(SYSDATE, "S UNDAY"))) + （6×60+10）/（24×60）'
本文出自 “天涯客的blog” 博客，请务必保留此出处http://tiany.blog.51cto.com/513694/1545061