Unanswered: Calling many functions at the same time using dbms_job
I have an oracle stored procedure that makes calls to 10 other SPs from within itself. The thing is that the input parameters passed to each of the 10 SPs are the same. Currently, each of the SP is called one after the other. These make checks like comparing the address of the customer against the ones already present in the database, etc. As expected this processing is taking a lot of time (3 mins per record). There is no need to call the SPs one after the othr since they have the same input parameters.
I was searching for a way to call more than one SP at the same time, and was sugested to use dbms_job to do that. I checked it out but had some doubts regarding the same...i am wondering if someone could help me out
1) Each of the job (function call) should be executed only when called from within the parent SP.,i.e., the job should run and then wait for the parent SP to call it again. How do i set the next_date parameter to do that?
2) If one of the SPs fails, in terms of business logic, we have defined custom error codes to indicate the exact nature of the error. How do we return this error code to the parent SP if function call is made using dbms_job?
select col1, col2 from table-1 where <condition>;
when no data found then
when too many rows then
when others then
return 0; (success)
if (condition 1) then
(else continue with next line of code)
I would really really appreciate it if someone could guide me in this regard.
Sounds like there is something fundementally wrong if its taking 3 min to check one record. Are your procedures written efficiently, you may find rewritting the checks to operate on sets of records rather thana single record much more efficient. Also if its that slow you may have an indexing issue.
As for running in parallel through dbms_job why not get each sp to write to a log table with the error code if any.
The single record situation cannot be avioded...basically the check is being done on customer names and address to match ALL those that already exist. It is working with a lot of strings and "like '%'"s.
The indexing issue is being looked into. We're trying to make oracle do a Fast Full Scan on index rather than the Full Table Scan on the table, effectively giving a smaller table for scan.
However, we want to explore the possibility that if same input parameters are to be passed to each of these functions, can they be called at the same time...why wait for each to finish before calling the next!?
What we're currently thinking, thanx in part to you, is:
* Call the procedures through dbms_job, each as an autonomous transaction.
* Write return code to log table.
* Make calling function refer to log table for return code.
However, here is the issue:
* Many users may call the function at the same time. The return code of each can be differenciated by making use of the session id. BUT, how to inform the calling procedure the appropriate session id?
You could use global temporary table (GTT) for this purpose; every user would see only his own data, although all of them would write into the same GTT.
DBMS_JOB works in a different session than the calling proc...that won't help.
The scenario is something like this:
* Application calls Main Func A
* Func A calls 20 other functions for different types of comparisions. say, Func 1 t0 Func 20. They all get the same input parameters.
* If we call Func 1, 2 & 3 in 'parallel' using dbms_job, how to tell the Func A that the session ID of Func 1, 2, 3 are so and so....since dbms_job will open a new session.
Are your 20 functions doing selects against the customer/address table as if they are you may well end up with 20 full table scans. In this case convert the 20 functions into one function preferably using a single select to do all your tests (using OR ) as that way you'll do one FTS rather than 20. Do as much matching though sql rather than a plsql cursor loop as this will also slow it down quite a bit.
dbms_jobs isnt really designed for this fine level of parallelism and is better for large batch jobs.
do you mean dbms_sql.execute, if you do I cant see how this would help as it is to do with dynamic sql. Also the paralellising approach is unlikely to help much if you can have many session executing the query concurrently unless you have a lot of CPU/IO resources.