We have similar problem.
Dilip M / JPMC, April 25, 2002 - 9:40 am UTC
Tom,
We have similar problem. (DBMS oracle 8.1.7/Frontend PB7).
appplication calls a backend procedure which takes about 4 minutes to complete the task. In the mean time users wait for task to complete.
Is there anyway, a job can be submitted to oracle and user can still work in the current session?
Does oracle session work always sequentially or we can spawn thread?
If it allows to create thread or something similar, where can I find more information?
If not, is it because RDBMS concepts doesnot premit or because of implementation issues?
Thank you!
April 25, 2002 - 6:32 pm UTC
That is a great example of when you might want to use DBMS_JOB!!! It was designed to that.
Yes, your PB app can submit the stored procedure to be executed. Soon after committing, the job will be run in *another* session. Your end user is free to continue working. I would suggest using DBMS_APPLICATION_INFO as a means of "communicating" the jobs progress to PB.
Multi tasking in PL/SQL
Saradha, April 25, 2002 - 3:57 pm UTC
Tom,
Thank you very much for your quick response.
I feel very confident now after I got the reply back from you. Can you please clarify the following on this issue.
Assume that there are 100 users and had sumitted 10 jobs each at the same time and the next date value for all the submitted 1000 jobs are SYSDATE. (almost same time, may be differences in seconds )
The parameters are set to the following.
JOB_QUEUE_PROCESS=36
JOB_QUEUE_INTERVAL=60
1) Does it mean that at any point of time oracle can only run 36 jobs in 36 sessions simultaneously and the next set will be picked up after 60 seconds only if the already running 36 jobs completed.
2) Can I schedule a job in another instance through a db link. ?
I have two instanances in the same server and I have database link created in each instance.
Out of 10 jobs, 8 jobs are executing a stored procedure compiled in the other instance. So my question
is instead of scheduling all the 10 jobs in same instance, can I schedule the 8 jobs in the remote database. So that the work load on one database will be minimized . Is it possible ?
3) Are there any other system parameters or system resources (OS level and Database level) should I be considering to implement this in a more efficient way so that the overall performance can be improved.
4) What are the disadvantages of using DBMS_JOB for an online application where the jobs will be keep submitted for every minute or so.
5) Can you suggest me some books where I can get more information on this issue.
6) Are there any other tool (like Pro*C ) or Utility can be considered at all in a replacement for DBMS_JOB.
Thanks again for your great help Tom.
Pushparaj
April 25, 2002 - 6:50 pm UTC
1) That means there will be 36 concurrent threads of execution. As jobs finish, others will start. You do not wait for the 36 to complete, when one completes -- another is able to start.
2) Yes, you would run dbms_job.submit@remote_site(....) There is an issue where dbms_job takes a default parameter (instance) that is defaulted to a package variable value. You will HAVE to supply the value for that in this case.
3) besides thinking about why a process needs 10 parallel steps to complete? No, not really.
4) I use dbms_job lots, nothing I am aware of.
5) using dbms_job? I have a pretty good chapter on it (and other stuff) in my book. Not aware of any specific resource germane to your problem.
6) AQ (advanced queues) would be something else to look at.
Can this also work for Forms?
Ken Niedermeyer, April 06, 2004 - 4:02 pm UTC
Would Oracle Forms be able to use this same logic to spawn off long running processes as well?
April 07, 2004 - 8:49 am UTC
yes
dbms_alert.waitone in a proc executed via dbms_job
Rae, January 05, 2007 - 4:05 am UTC
Hi Tom,
I need to execute simultaneously a number of instances of a procedure.
Each instance of the procedure is processing a set of records from a queue table.
There may be times that the queue table has a lot of records and it may empty as well.
Due to the nature of the queue table contents, there should always be one instance of the procedure running.
My intention is to have a dbms_alert.waitone inside the procedure and a trigger from the queue table to send alert when a record is inserted.
Because of the wait state, is it still recommended that the procedure be executed from dbms_job?
Thanks,
Rae
January 05, 2007 - 9:55 am UTC
if you do this from dbms_job, that job queue process will never be able to run anything else.
why not use AQ and automatic plsql notification - the AQ stuff will run your procedure as messages are received.
multiple job processes and parallelism
Rohit, August 29, 2008 - 9:13 am UTC
Hi Tom,
We have a table which will have 2000 records per day, For each record we need to join with another 3 tables to get info from those tables.
These 3 tables would contain around billons of records and these are partitioned tables.
To do the process faster, we are planning to use oracle parallelism with degree 8.
Apart from that we are planning to submit multiple jobs simultaneously by splitting those 2000 records in 10 different batches of 200 records giving each batch record start no. and record end no.
Then we will set the job_queue_processes as 10 and submit the same job with different batch id.
Could you suggest your view on this issue.
Oracle Version : 10gR2
Regards,
Rohit
August 30, 2008 - 9:28 am UTC
2000 records joined to 3 tables OF ANY SIZE is going to be so so fast, it'll amaze you.
JUST JOIN
select *
from two_thousand_row_table, big1, big2, big3
where join_conditions
as long as we know two thousand and "really really big", we'll nested loops join and it'll be so fast you won't know it even executed.
Asynchronous Commit
Govind, August 31, 2008 - 8:20 am UTC
Tom,
Is Asynchronous Commit of any use for parallel execution in PL/SQL?
Regards, Govind
searchingoracle.com
August 31, 2008 - 9:48 am UTC
since plsql does not do parallel execution, I'm not sure what you mean.