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

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