在 PL/SQL 中并行化调用

2021-12-24 00:00:00 oracle oracle10g plsql

我有一个带有 proc 的包，它将执行许多其他过程，如下所示:

I have a package with a proc that will execute a number of other procedures, like so:

CREATE PACKAGE BODY pkg IS
    CREATE PROCEDURE do
    IS
    BEGIN
        other_pkg.other_proc;
        other_pkg2.other_proc2;
        other_pkg3.other_proc3;
    END;
END;

有没有办法让程序并行而不是串行执行?

Is there any way to have the procedures execute in parallel rather than serially?

这是在这种情况下使用 DBMS_SCHEDULER 的正确方法吗:

Is this the proper way to use DBMS_SCHEDULER in this instance:

CREATE PACKAGE BODY pkg IS
    CREATE PROCEDURE do
    IS
    BEGIN
        DBMS_SCHEDULER.CREATE_JOB('job_other_pkg.other_proc', 'STORED_PROCEDURE', 'other_pkg.other_proc;');
        DBMS_SCHEDULER.RUN_JOB('job_other_pkg.other_proc', FALSE);
        -- ...
    END;
END;

推荐答案

您可以使用 dbms_job(或 dbms_scheduler)包来提交将并行运行的作业.如果您使用 dbms_job，提交作业将成为事务的一部分，因此一旦事务完成，作业就会开始.

You can use the dbms_job (or dbms_scheduler) package to submit jobs that will run in parallel. If you are using dbms_job, submitting the jobs will be part of the transaction so the jobs will start once the transaction completes. 

CREATE PACKAGE BODY pkg IS
    CREATE PROCEDURE do
    IS
      l_jobno pls_integer;
    BEGIN
        dbms_job.submit(l_jobno, 'begin other_pkg.other_proc; end;' );
        dbms_job.submit(l_jobno, 'begin other_pkg2.other_proc2; end;' );
        dbms_job.submit(l_jobno, 'begin other_pkg3.other_proc3; end;' );
    END;
END;

如果您使用 dbms_scheduler，创建新作业不是事务性的(即每次创建新作业时都会隐式提交)，如果有其他工作，这可能会导致事务完整性问题在调用此过程的事务中完成.另一方面，如果您使用 dbms_scheduler，提前创建作业并简单地从过程中运行它们可能更容易(或使用 dbms_scheduler 创建一个运行作业以响应某些其他操作或事件(例如将消息放入队列)的链).

If you are using dbms_scheduler, creating a new job is not transactional (i.e. there would be implicit commits each time you created a new job) which may cause problems with transactional integrity if there is other work being done in the transaction where this procedure is called. On the other hand, if you are using dbms_scheduler, it may be easier to create the jobs in advance and simply run them from the procedure (or to use dbms_scheduler to create a chain that runs the job in response to some other action or event such as putting a message on a queue).

当然，无论采用哪种解决方案，您都需要构建基础设施来监控这三个作业的进度，前提是您关心它们何时以及是否成功(以及它们是否会产生错误).

Of course, with either solution, you'd need to then build the infrastructure to monitor the progress of these three jobs assuming that you care when and whether they succeed (and whether they generate errors).

如果您打算使用 DBMS_SCHEDULER

  • 无需使用动态 SQL.您可以放弃 EXECUTE IMMEDIATE 而直接调用 DBMS_SCHEDULER 包的过程，就像调用任何其他过程一样.
  • 调用RUN_JOB时，需要传入第二个参数.use_current_session 参数控制作业是在当前会话中运行(并阻止)还是在单独的会话中运行(在这种情况下，当前会话可以继续并做其他事情).由于要并行运行多个作业，因此需要传入 false 值.
  • 虽然不是必需的，但更传统的做法是创建作业一次(将 auto_drop 设置为 false)，然后从您的过程中运行它们.
  • There is no need to use dynamic SQL. You can ditch the EXECUTE IMMEDIATE and just call the DBMS_SCHEDULER package's procedures directly just like you would any other procedure.
  • When you call RUN_JOB, you need to pass in a second parameter. The use_current_session parameter controls whether the job runs in the current session (and blocks) or whether it runs in a separate session (in which case the current session can continue on and do other things). Since you want to run multiple jobs in parallel, you would need to pass in a value of false.
  • Although it is not required, it would be more conventional to create the jobs once (with auto_drop set to false) and then just run them from your procedure.

所以你可能想在包外创建作业，然后你的程序就会变成

So you would probably want to create the jobs outside the package and then your procedure would just become

CREATE PACKAGE BODY pkg IS
    CREATE PROCEDURE do
    IS
    BEGIN
        DBMS_SCHEDULER.RUN_JOB('job_other_pkg.other_proc', false);
        DBMS_SCHEDULER.RUN_JOB('job_other_pkg2.other_proc2', false);
        DBMS_SCHEDULER.RUN_JOB('job_other_pkg3.other_proc3', false);
    END;
END;

相关文章