Skip to Main Content
  • Questions

  • Implementation of threading in Oracle

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Raju.

Asked: December 21, 2015 - 5:14 pm UTC

Last updated: December 22, 2015 - 9:06 am UTC

Version: 11g R2

Viewed 1000+ times

You Asked

I am Java developer and often we use stored procedures to perform certain business tasks.
In our project, for performance improvement we need to fire multiple procedures from a single procedure to perform multiple task at same point of time.

For example:
There is a procedure Proc1. Now I want to execute multiple procedures (procInner1, procInner2 ....) from this procedure in parallel so that I can perform my task simultaneously in different procedures. I don't want to execute each procedures sequentially (one after another).

Hence, I want to know the process of executing multiple procedures at a single point of time i.e., performing tasks in parallel. In java, we have concept of multiple threading to perform simultaneous task (parallel processing). However, I want the above implementation in oracle only.

and Chris said...

To run multiple different procedures in parallel, just create as many sessions as you want to run at once. Then instruct each session to call the PL/SQL as needed.

If these procedures run SQL, take care however. If they all start at the same time, they will all get a read consistent view of the database at that point. If they work with the same data and they need to see each-other's changes then you'll still need to serialize the calls.

I suggest you read up on concurrency and consistency before you start down this path:

http://docs.oracle.com/database/121/CNCPT/consist.htm#CNCPT020

If you're referring to executing the same code in parallel, have a read of these:

http://docs.oracle.com/database/121/VLDBG/GUID-3E2AE088-2505-465E-A8B2-AC38813EA355.htm#VLDBG010
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4248554900346593542

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Very Quick and Clear Response

Raju, December 21, 2015 - 6:35 pm UTC

Hi Tom,
Thanks your reply. However, can you please let me know how I can create multiple sessions to call different procedures.
As per our current code implementation, we are calling a single procedure (Proc1) from Java and this procedure subsequently calling few other procedures (innProc1, innProc2 ...) sequentially which are independent of each other (perform different tasks).
Now I want to implement the code like below:
From Java -> Proc1 -> (1) innProc1, (2) innProc2 ..
Also once when all the inner procedures gets completed, then I will call another final procedure e.g., finalProc to perform some task based on the out come of innProc1, innProc2 ....
Also please note that, I don't want to commit any DML operation in these procedure as I am using a transaction model (java implementation) to commit the task at the end.
Please let me know, is there anyway I can perform parallel different tasks simultaneously in oracle.
Chris Saxon
December 22, 2015 - 9:06 am UTC

You control how many session there are from the client. For each procedure you want to call in a separate DB session, you'll need a new Java thread.

You'll need to take the procedures out of the one you're currently calling. Then call these directly from your code. So you have something like:

Java -> Proc1
Java Thread 1 -> innProc1
Java Thread 2 -> innProc2
...
Java -> finalProc

How you do this is a Java programming question and beyond my expertise. If you need further help with this try the Java forums.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library