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

Question

我正在尝试并行运行存储过程 - 使用 dbms_scheduler 的 Oracle PL/SQL，但我收到一个类似未知作业的错误，我也尝试过 dbms_job，这里我收到一个错误 - 必须声明标识符 dbms_jobs。有人可以帮帮我吗？以下是我尝试过的两种方法:

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;

其中 pkg1 中定义了所有 3 个过程。谢谢!

Answer 1

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

创建程序:

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 所描述的):

现在创建一个 Scheduler Job 来运行链:

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 行)。

• 每个作业步骤都在整个作业开始的几分之一秒内开始(如第 1544、1546 和 1548 行中每个步骤的 ACTUAL_START_DATE 中所记录)，并在预期的 5 秒内完成。

• 整个作业在“14-JUN-20 12.15.46.968592000”(LOG_DATE，第 1550 行)完成，总持续时间为 5 秒以完成所有三个步骤。

• 请注意，规则处理可能会给链的总执行时间增加一点点开销。