| Fork / Spawn parallel PL/SQL processes [message #123732] |
Tue, 14 June 2005 10:54  |
Mike Muldoon
Messages: 24
Registered: March 2003
Location: Edinburgh
|
Junior Member |
|
|
A quick question for the PL/SQL experts out there. Can I fork or spawn several parallel instances of a PL/SQL procedure from a single one?
This is kind of a parent child relationship here where I would like the parent procedure to do much of the up-front processing and data gathering before calling x number of parallel child processes that will complete the required work. The child processes will receive as parameters appropriate pl/sql structures holding the data gathered by the parent process (to avoid having x number of streams looking up the same data).
The only provision is that each child process must be kicked off in a fire and forget kind of way and have it's own processing ID (probably UNIX PID).
Any guidance would be welcome as I don't particularly want to start coding this in Pro*C.
Thanks in advance. Mike.
|
|
|
|
| Re: Fork / Spawn parallel PL/SQL processes [message #123741 is a reply to message #123732] |
Tue, 14 June 2005 11:32  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
I have not run into a need to do something like this, although perhaps someone else has? In my experience it is the data access that takes up the bulk of the processing time.
So if you optimize your queries, be they selects or inserts or whatever, then you are fine. You can run those sql commands with parallel (degree), and let oracle basically handle for you the parallel processing of larger statements.
I've also run multiple sqlplus sessions, for example, as a sort of home grown parallel method. Like running 3 iterations of the same script at the same time with 3 different ranges of input values, for example. Or 4 separate sqlldr runs kicked off at the same time.
Likewise a perl program that forks children, with each child then calling a program or running a sqlldr or sqlplus script.
But parallel query and optimized sql do most of what I need. Why do you need to fork, what is it you are processing?
|
|
|
|
| Re: Fork / Spawn parallel PL/SQL processes [message #123757 is a reply to message #123732] |
Tue, 14 June 2005 13:53 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Two other options came to mind. The first is jobs, as in dbms_jobs. You submit a job to be executed, and it will fire once there is a commit. It has a job_id, which in a way would be like a PID. Another is Oracle Advanced Queues. I haven't used those yet, but it is a way to line up messages or actions that can later be acted on. There is an oracle guide on it.
|
|
|
|
| Re: Fork / Spawn parallel PL/SQL processes [message #123865 is a reply to message #123757] |
Wed, 15 June 2005 04:02 |
Mike Muldoon
Messages: 24
Registered: March 2003
Location: Edinburgh
|
Junior Member |
|
|
Thanks for the help. The reason I wanted to use the forking is because I do not have any real control of the data access. I have to use provided API's in order to update the data and I only have a limited time in order to complete the entire update.
The problem is that the performance of these API's is not certain and since I don't have the source for them I wanted to run them in parallel streams rather than in a serial fashion.
Yeah, I've done a bit with QUEUES but but not dbms_jobs so will have a quick look at that. Really I was looking for some Oracle functionality that would allow me to easily do this but I think it will take a bit of playing about whichever option I select.
|
|
|
|
| Re: Fork / Spawn parallel PL/SQL processes [message #123934 is a reply to message #123865] |
Wed, 15 June 2005 11:08 |
Mike Muldoon
Messages: 24
Registered: March 2003
Location: Edinburgh
|
Junior Member |
|
|
I have just been experimenting with the DBMS_JOB package and the results look good. I added a couple of stored procedures to the base (Here are the proc defs only)...
PROCEDURE test(streamCount IN INTEGER) IS
BEGIN
Insert into MJMTEST VALUES ('Mike', streamCount);
COMMIT;
END test2;
I added a table MJMTEST with two cols, a VARCHAR2(24) and a NUMBER.
I then ran the following script....
DECLARE
jobNo BINARY_INTEGER;
BEGIN
FOR i IN 1 .. 5
LOOP
DBMS_OUTPUT.put_line('Submitting Child Process Number: ' || i);
DBMS_JOB.SUBMIT(jobNo, 'PACKMJMTEST.test(' || i || ');', SYSDATE, NULL);
DBMS_OUTPUT.put_line('Submitted Job Number: ' || jobNo);
END LOOP;
COMMIT;
END test;
/
The output looks fine....
SQL> @testJob.sql
Submitting Child Process Number: 1
Submitted Job Number: 16
Submitting Child Process Number: 2
Submitted Job Number: 17
Submitting Child Process Number: 3
Submitted Job Number: 18
Submitting Child Process Number: 4
Submitted Job Number: 19
Submitting Child Process Number: 5
Submitted Job Number: 20
PL/SQL procedure successfully completed.
SQL> select * from mjmtest;
NAME COUNT
------------------------ ----------
Mike 1
Mike 3
Mike 5
Mike 2
Mike 4
The only thing I need to confirm is that the jobs are actually being run in parallel and not sequentially. As long as I can prove this then I am happy. Reading the DBMS_JOB documentation I think that they must be since the it is partly designed to allow jobs to be run on a schedule basis, therefore I guess that a previous job that is still running will not cause problems on another one starting.
Thanks for pointing me in the right direction smartin.
Mike
|
|
|
|
|
|
|
|
| Re: Fork / Spawn parallel PL/SQL processes [message #124434 is a reply to message #124377] |
Sat, 18 June 2005 08:31 |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
I did do something like this a while ago using DBMS_JOB and DBMS_ALERT to report back completion status. It was originally a package in 8i, but I rewrote it as an object type for 9i (see here) - however to be honest I think the smarter you try to make this type of thing, the closer you get to reinventing DBMS_AQ.
Oracle's native way of achieving parallel processing is with parallel query. You execute a single DML statement and it is automatically split among coordinated PQ slaves. Partitioned tables work well with this. Even then, however, I've usually found PQ to be more trouble than it's worth.
[Updated on: Sun, 26 February 2017 05:01] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
