Solved

Multithreading in PL/SQL. Can it be done?

Posted on 2004-09-01
12
1,791 Views
Last Modified: 2008-01-09
If it can, how would one go about doing it?

Thanks.
0
Comment
Question by:soccerplayer
12 Comments
 
LVL 22

Expert Comment

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

Expert Comment

by:grant300
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?
0
 
LVL 2

Expert Comment

by:cheyennejk
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',
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

}

0
 

Author Comment

by:soccerplayer
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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 19

Expert Comment

by:grant300
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,
Bill
0
 

Author Comment

by:soccerplayer
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.
0
 
LVL 19

Accepted Solution

by:
grant300 earned 125 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,
Bill
0
 

Author Comment

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

Expert Comment

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

Bill
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to recover a database from a user managed backup

760 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now