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

问题描述

我有一个Activity表，该表正在获取系统的所有表事件.所有系统表上的新订单，插入/删除之类的事件都将插入到该表中.因此，活动表的事件/秒数确实很大.

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中并行调用

I used the same link Parallelizing calls in PL/SQL

作为解决方案，我创建了多个dbms_scheduler作业，这些作业将被同时调用.所有这些作业(JOB1, JOB2--- - -JOB10)将具有与JOB_ACTION相同的过程( ProcForAll_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.

P.S:我们无法在 ProcForAll_Processing 进程中创建更多的作业来实现并行处理，因为这可能会消耗更多的资源，并且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屋！