Dianna, June 16, 2003 - 7:27 am UTC
That is a very good idea as it will be useful for me to know if any of the jobs are taking a long time. If I do have the job update the parameter table row, what is the best way for me to check in the calling procedure for the completion of all jobs? I am thinking something like a loop that checks and if any items are stilling outstanding sleeps for a brief amount of time and then checks again. Does this sound reasonable? If you have a reference in your book, that would help as I do have a copy.
Thanks,
Dianna
June 16, 2003 - 8:14 am UTC
I typically do this:
begin
dbms_job.submit...
dbms_job.submit...
loop
dbms_lock.sleep(30);
look to see if they are done, exit when they are
end loop;
More detailed example
Rob, June 16, 2003 - 1:30 pm UTC
Tom:
Do you have a more detailed code example of submitting multiple jobs via dbms_job and then waiting until all are done.
What do you look for to determine that the jobs that you have just submitted are complete.
begin
dbms_job.submit...
dbms_job.submit...
loop
dbms_lock.sleep(30);
look to see if they are done, exit when they are
end loop;
June 17, 2003 - 6:46 am UTC
here is one I used to do a simulation of multiple users in the database running a procedure.
We submit the job -- placing the inputs in a "job parameter" table. When that table is emtpy -- jobs must be done (as the last step, these procedures deleted their inputs)
create or replace procedure simulation( p_procedure in varchar2, p_jobs in number, p_iters in number )
authid current_user
as
l_job number;
l_cnt number;
begin
for i in 1 .. p_jobs
loop
begin
execute immediate 'drop table t' || i;
exception
when others then null;
end;
execute immediate 'create table t' || i || ' ( x int )';
end loop;
for i in 1 .. p_jobs
loop
dbms_job.submit( l_job, p_procedure || '(JOB);' );
insert into job_parameters
( jobid, iterations, table_idx )
values ( l_job, p_iters, i );
end loop;
statspack.snap;
commit;
loop
dbms_lock.sleep(30);
select count(*) into l_cnt from job_parameters;
exit when (l_cnt = 0);
end loop;
statspack.snap;
end;
/
A reader, July 25, 2003 - 10:04 am UTC
What is l_job
A reader, October 20, 2003 - 6:14 pm UTC
Tom,
In your "simulation" procedure how do you set/get values for "l_job"?
Thanks!
October 20, 2003 - 8:07 pm UTC
dbms_job returns the job id it decided to assign the job. it is an OUT parameter.
Why statspack.snap?
A reader, October 21, 2003 - 12:12 am UTC
Thanks Tom.
Why are you using statspack.snap in the procedure?
October 21, 2003 - 7:15 am UTC
i wanted to see what happened during the simulation -- where did they contend for resources, what did they do, what were the statistics.
so, i take a snap, wait for them to finish, take another snap. now i can report.
Use a pipe
Neil, October 21, 2003 - 7:06 am UTC
There's an article in October's edition of Oracle Professional (www.oracleprofessionalnewsletter.com) by Parin Jhaveri and Lev Moltyana regarding parallel job submission. For those who don't subscribe, here's the gist of it:
A table holding the requested jobs, their parameters and duration/status is read by a main package. This executes the jobs via the job queue. The job queue calls a generic 'child' procedure which executes the task using execute immediate. Each invocation of the child communicates with the parent using a pipe given it as a parameter by the parent. So, several child processes using the same pipe to communicate with the parent provide the means for the parent to determine the status of all those child processes. If all children are successful, the parent commits, otherwise it can rollback.
That's the theory, and it seems sensible to me. I'm not convinced of the main procedure's ability to rollback or commit, but I may have go the wrong end of the stick. Either way, a quick protoype would establish this one way or the other.
HTH
October 21, 2003 - 4:55 pm UTC
the parent procedure would be committing or rolling back ONLY ITS WORK.
the child processes are "on their own", each will commit or not by itself.