Multithreading in PL/SQL. Can it be done?

You just would not want to do it.

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?

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',
where
Process In ('Sub1,'Sub2','Sub3','Sub4');
commit;



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

commit;
// 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
DBMS_LOCK.SLEEP (580);

-----------------------------------------------
While v_flag < 40 Loop

DBMS_LOCK.SLEEP (180);

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
 Else
 v_flag := v_flag + 1;
End If;

End Loop;
-----------------------------------------------------

If v_flag = 100 Then
{......

Do Process 4

...}

Else

{

time out error

}

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.

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,
Bill

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.

So, to put it plainly, other than DBMS_JOB.SUBMIT it cannot be done.

Correct.  Either Oracle's or your own similar implementation.

Bill