Multithreading in PL/SQL. Can it be done?

Posted on 2004-09-01
Medium Priority
Last Modified: 2008-01-09
If it can, how would one go about doing it?

Question by:soccerplayer
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 22

Expert Comment

by:earth man2
ID: 11955394
You just would not want to do it.
LVL 19

Expert Comment

ID: 11958066
Kind of.  It really depends on what you are trying to accomplish.

Many queries and some PL/SQL constructs already benefit from parallelism; using threads to make use of more hardware.

You can use the JOB_SCHEDULER functions to light off one or more PL/SQL routines as quasi deamons.

You can use table functions which may, under certain circumstances, run in their own thread.

You can accomplish some degree of interprocess communciation in a couple of ways.  One is to use the message queue facilities in the UTIL library.  The other (though I have not tested this) way might be to use Java Functions with socket programming to send messages between processes.

Do you have a specific application in mind?

Expert Comment

ID: 11974785
This code may help. I have a procedure that requires 4 long running sub-processes. The first 3 process are independant of each other , while the 4th is dependant on the data returned from the first 3.

update flag_tb
set flag= 'false',
Process In ('Sub1,'Sub2','Sub3','Sub4');

// Execute All At Once
DBMS_JOB.SUBMIT(Jnum1, 'Sub1_P;' ,SYSDATE,null,FALSE);  

// sub 1 sets flag in table above to true when complete where Process= 'Sub1'
// sub 2 sets flag in table above to true when completewhere Process= 'Sub2'
// sub 3 sets flag in table above to true when complete where Process= 'Sub3'

// wait for

While v_flag < 40 Loop


select count(*)
into v_count
from  flag_tb
where Process In ('Sub1,'Sub2','Sub3');

If v_count = 3 Then
 v_flag := 100;      // set flag to break loop if all 3 processes are finished
 v_flag := v_flag + 1;
End If;

End Loop;

If v_flag = 100 Then

Do Process 4




time out error


Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 12007368
Thanks everyone for your responses. I already know about DBMS_JOB.SUBMIT but our DBA would not allow it so I was wondering if there are other options.

C++/proC allows multi-threading which improves performance. There have been situations where the same thing, when written using C++/proC multi-threading, improved performance from hours to minutes over PL/SQL.

So there is really no way of a similar multi-threading in PL/SQL.
LVL 19

Expert Comment

ID: 12007673
Multithreading is not a silver bullet.  All too often I have seen programmers turn in a poor to moderate coding effort and then spend a bunch of time multithreading it.  They invariably crow about how fast they made the program.  The problem, of course, is that you don't have an unlimited supply of hardware to run this stuff on.  If everybody solved their performance problems by multithreading, you would simply have hundreds of processes competing for the same handful of CPUs and the net performance change would be NEGATIVE!!!

In the example you gave above, you are comparing apples and oranges.  You first have to understand the performance differential between a single thread of the C++ program versus the PL/SQL program.  I am certain that accounted for a good part of the performance increase as it is unlikely you lit up 60 threads on a machine with 60 CPUs.  Another issue is that PL/SQL is often missused which produces non-optimal results.  Programmers often use cursors to do record-at-a-time processing when, infact, clever SQL coupled with a reasonable data model can accomplish most things with set processing.  There is no doubt that record-at-a-time processing favors 3GLs like C++

It sounds as if all you really want is to speed things up.  In that case, I would look first at the data model to see if it really fits the requirements of your application.  You may find that a few minor changes make a huge difference in the amount of work required to do certain processes.

Second, I would examine the processes in PL/SQL and look for ways to optimize them, particularly using set processing and bulk binding wherever possible.  This has the greatest potential to speed things up; however, you may need the cooperation of the DBA to help you get the right indexes in place.

Another problem I see frequently is programmers trying to use PL/SQL to do a lot of charactor manipulation.  Walking thru strings one charactor at a time will kill performance right away.  There are some tasks that PL/SQL is not well suited for.  The good news is that you can move that type of low level processing into JAVA quite nicely.  JAVA is very good at some of the same things that PL/SQL is not good at.

You can sometimes pipeline things with table functions to get more throughput by avoiding writing intermediate results to tables.  As mentioned in a prior comment, you can also get some multithreading this way.

Finally, you can compile PL/SQL into native code.  This option came along with 9i/9.2 and can help speed up any cursor or low level operations you still have in the code.  It does not speed up the SQL at all and you would not expect it too.  The truth about this is that it does not have much of an effect for well written, set-based PL/SQL code.  Irionically, it speeds up the stuff that you would try to minimize in your PL/SQL code anyway.

Hope that helps,

Author Comment

ID: 12034836
Bill, thank you for your response. I already know about almost all the stuff that you are talking about. The question remains, can the same stored procedure be called multiple times (as many times as we wish) simultaneously other than using DBSM_JOBS package. The answer seems to be, it cannot be done.
LVL 19

Accepted Solution

grant300 earned 500 total points
ID: 12039050
I can think of two methods, one if you need/want to stay in PL/SQL and other if you are willing to go outside the database engine a little bit.  Both will require a bunch of screwing around to create a way to get around a permission (DBMS_JOBS) set the wrong way.

The out-of-database way of doing things is to create a JAVA program that makes a connection to the database and waits for "job requests" to show up in a message queue you have created in the database.  When a request comes in, it lights up a thread, makes a connection to the database, and runs the requested stored procedure.  When completed, it needs to send a signal back, again probably through a message queue to the calling PL/SQL routine.  The code for this is simple and straight forward and is, in effect what the DBMS_JOBS package does.

The all-PL/SQL solution is messier but it will work.  Go read the Oracle documentation on table functions.  These are user-defined PL/SQL functions that output (and optionally input) a stream of records (table).  The documentation talks about the threading that occurs when you pipeline these guys, however, I believe the same threading occurs when you make simultaneous requests from multiple table functions.

The trick is to create table functions for each of the stored procedures you want to call in parallel.  These functions can query a parameter table if you like so that you can run multiple copies of the same function with different arguments.  In any event, the function calls your stored procedure then returns the OUTPUT arguments and status as the functions single output row.  To use these, you write a query that uses the table functions in the FROM clause for a JOIN forcing the engine to evaluate all of the table functions simultaneously.  (A UNION ALL query might work but I suspect that the query, and therefore the table functions, may be executed serially.)  When the query returns, you know that all of your stored procedures have completed.

Just to play deval's advocate here for a second.  It is possible that the DBA is not turning on DBMS_JOBS for you because of machine loading, other resource limitations, the impact on other processes, batch job restartability, and/or the belief that your procedures are not as effeciant as they might be.  Before you circumvent the limitation, you might want to ask 'why'.  You don't want to be the guy who screws up the nightly batch run for everybody else. ;-)  The other possiblity is that he just has his head lodged in an alternative portion of his anatomy.

If you take the table function route, post the code back for the group.

Best of luck,

Author Comment

ID: 12122519
So, to put it plainly, other than DBMS_JOB.SUBMIT it cannot be done.
LVL 19

Expert Comment

ID: 12123699
Correct.  Either Oracle's or your own similar implementation.


Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video shows how to recover a database from a user managed backup
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question