并行运行/执行多个过程 - Oracle PL/SQL [英] Run/execute multiple procedures in Parallel - Oracle PL/SQL

问题描述

我有一个活动表，它正在获取系统的所有表事件.新订单、所有系统表上的插入/删除等事件都将插入到该表中.因此，活动表的事件数/秒非常大.

I have an Activity table which is getting all the table events of the system. Events like new orders, insertion/deletion on all the system tables will be inserted into this table. So, the no of events/sec is really huge for Activity table.

现在，我想根据负责引发事件的表根据业务逻辑处理传入事件.每个表可能有不同的程序来做处理.

Now, I want to process the incoming events based on the business logic depending on the table responsible for raising the event. Every table may have different procedure to do the processing.

我使用了相同的链接PL/SQL 中的并行调用

作为解决方案，我创建了多个将同时调用的 dbms_scheduler 作业.所有这些作业 (JOB1, JOB2--- - -JOB10) 将具有与 JOB_ACTIONProcForAll_Processing)代码> 实现并行处理.

As a solution I have created multiple dbms_scheduler jobs which will be called at the same time. All these jobs (JOB1, JOB2--- - -JOB10) will have the same procedure (ProcForAll_Processing) as JOB_ACTION to achieve parallel processing.

begin
    dbms_scheduler.run_job('JOB1',false);
    dbms_scheduler.run_job('JOB2',false);
  end; 

ProcForAll_Processing:这个过程会依次调用其他6个过程Proc1,proc2,proc3 --- -- - -- - Proc6 按顺序执行.我也想为这些实现并行处理.

ProcForAll_Processing: This procedure in turn will call 6 other procedures Proc1,proc2,proc3 --- -- - -- - Proc6 in sequential manner. I want to achieve parallel processing for these as well.

PS:我们无法在 ProcForAll_Processing proc 中创建更多作业来实现并行处理，因为这可能会导致消耗更多资源，而且 DBA 不同意进一步创建工作.还有，我不能用dbms_parallel_execute 用于并行处理.

P.S: We can’t create further jobs to achieve parallel processing in ProcForAll_Processing proc as it may lead to consume further resources and also DBA is not agreeing for creating further jobs. Also, I can't use dbms_parallel_execute for parallel processing.

请帮助我，因为我真的很难完成它

Please help me as I am really stuck to get it done

推荐答案

获取新的 DBA.或者甚至更好，将它们从任何决策过程中剔除.DBA 不应审查您的代码，也不应告诉您不要创建工作，除非有充分的具体原因.

Get a new DBA. Or even better, cut them out of any decision making processes. A DBA should not review your code and should not tell you to not create jobs, unless there is a good, specific reason.

使用 DBMS_SCHEDULER 并行运行是迄今为止实现这一结果的最简单和最常见的方法.当然它会消耗更多的资源，这是并行不可避免的.

Using DBMS_SCHEDULER to run things in parallel is by far the easiest and most common way to achieve this result. Of course it's going to consume more resources, that's what parallelism will inevitably do.

另一个更糟糕的选择是使用并行流水线表函数.这是一项高级 PL/SQL 功能，无法通过简单示例轻松解释.我能做的最好的事情就是让您参考手册.

Another, poorer option, is to use parallel pipelined table functions. It's an advanced PL/SQL feature that can't be easily explained in a simple example. The best I can do is refer you to the manual.

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