Geek Answers Handbook

Run / execute multiple procedures in Parallel - Oracle PL / SQL

I have an action table that receives all the table events in the system. Events such as new orders, insert / delete in all system tables will be inserted into this table. Thus, the number of events / sec is not very large for the Activity table.

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

I used the same link Parallel calls in PL / SQL

As a solution, I created several dbms_scheduler jobs that will be called simultaneously. 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 : this procedure, in turn, will call 6 other procedures Proc1,proc2,proc3 --- -- - -- - Proc6 sequential manner. I also want to achieve parallel processing.

PS: We cannot create additional tasks for parallel processing in ProcForAll_Processing , since this can lead to the consumption of additional resources, and DBA does not agree to create additional tasks. In addition, I cannot use dbms_parallel_execute for parallel processing.

Please help me as I am really stuck to do this.

+1
source share
3 answers

In the general case, it is impossible without tasks, and for this he will do several sessions. With a few exceptions, there is no PL\SQL multithreading. One of them is the parallel execution of sql [1] statements . Thus, there are several attempts to abuse this material for parallel execution of PL \ SQL code, for example, try looking here [2].

But, as I said, this is an abuse of IMHO.

Link:

+3
source

Get the new database administrator. Or even better, cut them out of any decision-making process. The database administrator should not check your code and should not indicate that you are not creating jobs unless there is a good specific reason.

Using DBMS_SCHEDULER for parallel operation is the easiest and most common way to achieve this result. Of course, it will consume more resources, which will inevitably make parallelism.

Another, whiter option is to use parallel pipelined table functions. This is an advanced PL / SQL function that cannot be easily explained with a simple example. The best I can do is take you to leadership .

+3
source

More articles:


All Articles