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

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

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

[oracle@rh7 ~]$ 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 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

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

字段（列）    类型            描述

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 ( 

      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_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

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

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、查看测试表情况

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的工作情况

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的执行

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: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     

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