PL/SQL中的并行调用 [英] Parallelizing calls in PL/SQL

问题描述

我有一个带有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;

这篇关于PL/SQL中的并行调用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！