oracle - 并行运行过程

CREATE PACKAGE BODY pkg IS CREATE PROCEDURE do_parallel_execution IS BEGIN DBMS_SCHEDULER.RUN_JOB('pkg1.proc1', false); DBMS_SCHEDULER.RUN_JOB('pkg1.proc2', false); DBMS_SCHEDULER.RUN_JOB('pkg1.proc3', false); END; CREATE PACKAGE BODY pkg IS CREATE PROCEDURE run_in_parallel IS l_jobno pls_integer; BEGIN dbms_job.submit(l_jobno, 'pkg1.proc1; end;' ); dbms_job.submit(l_jobno, 'pkg1.proc2; end;' ); -- dbms_job.submit(l_jobno, 'pkg1.proc3; end;' ); END;

要并行执行其他不相关的过程，请使用调度程序作业链：

创建程序：

create or replace package test as
    procedure test1;
    procedure test2;
    procedure test3;
end test;
/

create or replace package body test as
    procedure test1 is
    begin
        sys.dbms_session.sleep(5);
    end test1;

    procedure test2 is
    begin
        sys.dbms_session.sleep(5);
    end test2;

    procedure test3 is
    begin
        sys.dbms_session.sleep(5);
    end test3;
end test;
/

为每个过程创建调度程序：

BEGIN
    DBMS_SCHEDULER.create_program(
        program_name => 'TEST1_PROGRAM',
        program_action => 'TEST.TEST1',
        program_type => 'STORED_PROCEDURE',
        number_of_arguments => 0,
        comments => NULL,
        enabled => FALSE);

    DBMS_SCHEDULER.ENABLE(name=>'TEST1_PROGRAM');    

    DBMS_SCHEDULER.create_program(
        program_name => 'TEST2_PROGRAM',
        program_action => 'TEST.TEST2',
        program_type => 'STORED_PROCEDURE',
        number_of_arguments => 0,
        comments => NULL,
        enabled => FALSE);

    DBMS_SCHEDULER.ENABLE(name=>'TEST2_PROGRAM');    

    DBMS_SCHEDULER.create_program(
        program_name => 'TEST3_PROGRAM',
        program_action => 'TEST.TEST3',
        program_type => 'STORED_PROCEDURE',
        number_of_arguments => 0,
        comments => NULL,
        enabled => FALSE);

    DBMS_SCHEDULER.ENABLE(name=>'TEST3_PROGRAM');    
END;
/

创建调度程序链：

BEGIN
  -- one step for each program
  SYS.DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
     chain_name          => 'TEST_CHAIN'
    ,step_name           => 'CHAIN_STEP1'
    ,program_name        => 'TEST1_PROGRAM');

  SYS.DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
     chain_name          => 'TEST_CHAIN'
    ,step_name           => 'CHAIN_STEP2'
    ,program_name        => 'TEST2_PROGRAM');

  SYS.DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
     chain_name          => 'TEST_CHAIN'
    ,step_name           => 'CHAIN_STEP3'
    ,program_name        => 'TEST3_PROGRAM');

  -- one rule with condition "true" to start each step immediately
  SYS.DBMS_SCHEDULER.DEFINE_CHAIN_RULE  (
      CHAIN_NAME  => 'TEST_CHAIN',
      rule_name  => 'TEST_RULE1',
      condition => 'TRUE',
      action => 'START "CHAIN_STEP1"');   

  SYS.DBMS_SCHEDULER.DEFINE_CHAIN_RULE  (
      CHAIN_NAME  => 'TEST_CHAIN',
      rule_name  => 'TEST_RULE2',
      condition => 'TRUE',
      action => 'START "CHAIN_STEP2"');   

  SYS.DBMS_SCHEDULER.DEFINE_CHAIN_RULE  (
      CHAIN_NAME  => 'TEST_CHAIN',
      rule_name  => 'TEST_RULE3',
      condition => 'TRUE',
      action => 'START "CHAIN_STEP3"');   

  -- one rule to close out the chain after all steps are completed    
  SYS.DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
     chain_name          => 'TEST_CHAIN',
     rule_name           => 'TEST_RULE4',
     condition           => 'CHAIN_STEP1 Completed AND CHAIN_STEP2 Completed AND CHAIN_STEP3 Completed',
     action              => 'END 0');

END;
/

链流现在看起来像这样（由 SQL Developer 描述）：

现在创建一个调度器作业来运行链：

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
            job_name => 'TEST_JOB',
            job_type => 'CHAIN',
            job_action => 'TEST_CHAIN',
            number_of_arguments => 0,
            start_date => NULL,
            repeat_interval => NULL,
            end_date => NULL,
            enabled => FALSE,
            auto_drop => FALSE,
            comments => '');

    DBMS_SCHEDULER.SET_ATTRIBUTE( 
             name => 'TEST_JOB', 
             attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_RUNS);

END;
/

并运行作业：

BEGIN
    DBMS_SCHEDULER.RUN_JOB(job_name => 'TEST_JOB', USE_CURRENT_SESSION => FALSE);
END;
/

现在查看作业的作业运行详细信息：

"LOG_ID" "LOG_DATE"                               "JOB_NAME" "JOB_SUBNAME" "STATUS"    "ERROR#" "ACTUAL_START_DATE"                                "RUN_DURATION"
"1548"   "14-JUN-20 12.15.46.744612000 AM -04:00" "TEST_JOB" "CHAIN_STEP3" "SUCCEEDED" "0"      "14-JUN-20 12.15.41.708043000 AM AMERICA/NEW_YORK" "+00 00:00:05.000000"
"1544"   "14-JUN-20 12.15.46.746544000 AM -04:00" "TEST_JOB" "CHAIN_STEP2" "SUCCEEDED" "0"      "14-JUN-20 12.15.41.690404000 AM AMERICA/NEW_YORK" "+00 00:00:05.000000"
"1546"   "14-JUN-20 12.15.46.748830000 AM -04:00" "TEST_JOB" "CHAIN_STEP1" "SUCCEEDED" "0"      "14-JUN-20 12.15.41.690891000 AM AMERICA/NEW_YORK" "+00 00:00:05.000000"
"1550"   "14-JUN-20 12.15.46.968592000 AM -04:00" "TEST_JOB" ""            "SUCCEEDED" "0"      "14-JUN-20 12.15.41.574115000 AM AMERICA/NEW_YORK" "+00 00:00:05.000000"

注意：

作业从“12.15.41.574115000”（ACTUAL_START_DATE，第 1550 行）开始。
每个作业步骤在整个作业开始的几分之一秒内开始（如 ACTUAL_START_DATE 中记录的第 1544、1546 和 1548 行中的每个步骤），并在预期的 5 秒内完成。
整个作业在“14-JUN-20 12.15.46.968592000”（LOG_DATE，第 1550 行）完成，完成所有三个步骤的总持续时间为 5 秒。
请注意，规则处理可能会给链的总执行时间增加一点点开销。

oracle - 并行运行过程 - Oracle PL/SQL

问题描述

解决方案

推荐阅读