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

Question

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.

I used the same link Parallelizing calls in PL/SQL

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: 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: 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

Raw

It'd be better to move all the multithreading to some other place in the application (frontend, middle-tier). Nearly all OOP languages support multithreading. – Venturesome

using OOP language is not possible here as all the work done in the database only. Even output is being sent via the web service call at DB level. – Raw

In that case, you can try adapting Producer-Consumer design pattern with some limited size of Queue and number of thread-like jobs, not to create too much load on your database. – Venturesome

I am using thread like jobs but the no. of producers events is so large that I can't create large no of jobs for the consumers. I thought to create 10 jobs initially, but in that case the no of events for 1 job is large so I want to achieve parallel process inside the job level as well, but I can't create jobs further in the job action procedure. – Raw

Well. The easiest option seems to be writing some simple multithreading application in Java/C# using JDBC. It shouldn't be that hard even if you're a back-end developer. Here you got some simple examples of Consumer-Producer implementations tutorialspoint.com/javaexamples/thread_procon.htm, caveofprogramming.com/java-multithreading/… plus some JDBC tutorials tutorialspoint.com/jdbc/index.htm, docs.oracle.com/javase/tutorial/jdbc/basics. – Venturesome

Answer 1

It is impossible in general case without jobs, and it will make multiple sessions for this. There is no such thing as multithreading PL\SQL with a few exceptions. One of them is parallel execution of sql statements [1]. So there are some attempts to abuse this stuff for parallel execution of PL\SQL code, for example try to look here [2].

But as i've said it's abuse IMHO.

Reference:

1. https://docs.oracle.com/cd/B19306_01/server.102/b14223/usingpe.htm
2. http://www.williamrobertson.net/documents/parallel-plsql-launcher.html

Maidy

If your processing logic is row by row, you might want to consider converting it to use a set based algorithm; that then lends itself to parallel execution. Running multiple threads of row by row will only scale so far. – Saxhorn

I just quickly reviewed Reference #2 above. I think the Author said it himself. Quote: What this was really telling me is that I didn't fully understand PQ. – Saxhorn

@Saxhorn but eventually he managed to get parallel execution, maybe without deep understanding of guts under the hood. – Maidy

@IlyaMaskov. His work leaves a lot to be desired. There are a few missteps, misconceptions and misunderstandings. So would you really want to base you work on this? That's one of the problem with the internet; everything is assumed to be good and true. – Saxhorn

@Saxhorn His work leaves a lot to be desired — exactly. Nobody said that it's good and appropriate for real use. Have you seen the bold word abuse in my text? It's just a survey of the problem. – Maidy

Answer 2

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.

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.

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.

Rupe

Thank You Jon, you are a champ! Now, I can put both the options DBMS_SCHEDULER and parallel pipelined table functions – Raw

Answer 3

You should try to use DBMS_PARALLEL_EXECUTE (since RDBMS 11).
https://blogs.oracle.com/warehousebuilder/entry/parallel_processing_in_plsql
https://oracle-base.com/articles/11g/dbms_parallel_execute_11gR2

Cortege