通过案例学调优之--JOB管理

Oracle在创建和管理job主要借助两个包，分别为DBMS_JOB和DBMS_SCHEDULER

[oracle@rh6 ~]$ sqlplus '/as sysdba'

1 2 3 4 5 6 7 8 9 10

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76

FUNCTION BACKGROUND_PROCESS RETURNS BOOLEAN PROCEDURE BROKEN  Argument Name                  Type                    In/Out Default?  ------------------------------ ----------------------- ------ --------  JOB                            BINARY_INTEGER          IN  BROKEN                         BOOLEAN                 IN  NEXT_DATE                      DATE                    IN     DEFAULT PROCEDURE 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     DEFAULT PROCEDURE INSTANCE  Argument Name                  Type                    In/Out Default?  ------------------------------ ----------------------- ------ --------  JOB                            BINARY_INTEGER          IN  INSTANCE                       BINARY_INTEGER          IN  FORCE                          BOOLEAN                 IN     DEFAULT PROCEDURE INTERVAL  Argument Name                  Type                    In/Out Default?  ------------------------------ ----------------------- ------ --------  JOB                            BINARY_INTEGER          IN  INTERVAL                       VARCHAR2                IN PROCEDURE 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     DEFAULT FUNCTION IS_JOBQ RETURNS BOOLEAN PROCEDURE NEXT_DATE  Argument Name                  Type                    In/Out Default?  ------------------------------ ----------------------- ------ --------  JOB                            BINARY_INTEGER          IN  NEXT_DATE                      DATE                    IN PROCEDURE REMOVE  Argument Name                  Type                    In/Out Default?  ------------------------------ ----------------------- ------ --------  JOB                            BINARY_INTEGER          IN PROCEDURE RUN  Argument Name                  Type                    In/Out Default?  ------------------------------ ----------------------- ------ --------  JOB                            BINARY_INTEGER          IN  FORCE                          BOOLEAN                 IN     DEFAULT PROCEDURE 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     DEFAULT PROCEDURE USER_EXPORT  Argument Name                  Type                    In/Out Default?  ------------------------------ ----------------------- ------ --------  JOB                            BINARY_INTEGER          IN  MYCALL                         VARCHAR2                IN/OUT PROCEDURE USER_EXPORT  Argument Name                  Type                    In/Out Default?  ------------------------------ ----------------------- ------ --------  JOB                            BINARY_INTEGER          IN  MYCALL                         VARCHAR2                IN/OUT  MYINST                         VARCHAR2                IN/OUT PROCEDURE WHAT  Argument Name                  Type                    In/Out Default?  ------------------------------ ----------------------- ------ --------  JOB                            BINARY_INTEGER          IN  WHAT                           VARCHAR2                IN

DBA_JOBS

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

字段（列）    类型            描述   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 正在运行任务的开始时间，如果没有运行任务则为null THIS_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 ( 

1 2 3 4 5 6 7 8 9

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、建立测试表

1 2 3 4 5 6 7 8

117:17:45 SCOTT@ prod >CREATE TABLE DATE_LOG 17:18:03   2         (create_date DATE CONSTRAINT create_date_pk PRIMARY KEY); Table created. Elapsed: 00:00:01.15 17:18:06 SCOTT@ prod >desc date_log  Name                                                              Null?    Type  ----------------------------------------------------------------- -------- --------------------------------------------  CREATE_DATE                                                       NOT NULL DATE

2、建立一个procedure

1 2 3 4 5 6 7

17:18:15 SCOTT@ prod >CREATE OR REPLACE PROCEDURE create_date_log_row 17:18:27   2        IS 17:18:27   3        BEGIN 17:18:27   4        INSERT INTO date_log(create_date)VALUES(sysdate); 17:18:27   5        END; 17:18:27   6        / Procedure created.

3、建立一个job

1 2 3 4 5 6

17:40:02 SCOTT@ prod >begin 17: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、查看测试表情况

1 2 3 4 5 6 7

17:41:31 SCOTT@ prod >select * from DATE_LOG; CREATE_DA --------- 25-AUG-14 25-AUG-14 25-AUG-14 25-AUG-14

5、查看job的工作情况

1 2 3 4 5 6 7 8 9 10 11 12

17:42:28 SCOTT@ prod >col interval for a50 17:42:36 SCOTT@ prod >r   1  select last_date,last_sec,next_date,next_sec,interval,broken from user_jobs   2* where job=24 LAST_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的执行

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

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

1 2 3 4 5 6

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;

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

查询目前下次执行时间           11:17:40 SCOTT@ prod >select next_date,next_sec from user_jobs; NEXT_DATE NEXT_SEC --------- ---------------- 26-AUG-14 11:20:40 26-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     

1 2 3 4 5 6 7

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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44

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）'

本文转自 客居天涯 51CTO博客，原文链接：http://blog.51cto.com/tiany/1545061，如需转载请自行联系原作者