Calling two SPs at once....

Hi,

I have been assigned this Performance tuning project. It consists of a single SP being called that processes data by calling other SPs from within itself, one after the other. There are 20 such SPs being called. On studying the code, i found that some SPs are independant of each other, i.e., they do not depend on the other's output parameters for their processing. Therefore there is no need to wait for one SP to finish before calling the other.

Is there any way in oracle to call such SPs simultaneously, rather than seraily??

As close as it gets might be to create asynchronous transactions.

Otherwise, after asking around, I'd say "No".

Why don't you simply take those that can run independently out of the "main" procedure and schedule them at their own?

Thanks for the reply. Can u please guide me to a site that has more information regarding asynchronous transactions, possibly with examples.

They depend on the parent procedure for their input params but not on one another.

How many input params are we talking about? How difficult is it to get those params? If it's not too many and not too difficult then, you could simply feed those to a second (,third, fourth, ...) procedure.

I wanna rephrase the question. The details are the same, but the question is :

How do i multithread an ORACLE SP? Is there any way to multithread in ORACLE?

Do you know tkprof?

Instead of trying to thread a needle with a camel, have you run extensive profiling?
I can usually cut as much as 90% off Oracle run-time by creating good queries on indexed columns, adding indexes, or using hints like:
/*+ parallel */ for massive queries on multi-cpu boxes.

/*+ parallel */ "multithreads" a single operation that returns a lot of rows, for example.

Actual multithreading has to be done at a different level than from inside Oracle. You will have to rewrite PL/SQL into Proc*C and call pthreads (if you're on unix). You can do it in Windows as well with Pro*C and the Windows API.

If you really want to keep it in Oracle, then write the C code, then put it into a library, and then call the Pro*C procedure from within Oracle. Your DBA will have to set up a listener, and you'll have to do a lot of re-coding. Hence my profiling comment.

I have another thought:

Don't know if you've used the job queue (dbms_job) but you could have multiple job threads running and then submit each procedure as a job. Then your wrapper program would have to poll the job queue to see when all were finished.

Bit of a hack but probably easier than going the pro C route.

Thanks guys for your comments...

I am a rookie at this entire performance tuning scenario. I am learning as i go along.

I am currently exploring the possibilities Jim mentioned. I am running dbms_stat to gather statistics for the tables and indexed columns, etc., to give the CBO the data it requires. Have noticed in a few cases that the explain plan has changed to fast full scan of index rather than a full table scan after the sttistics were gathered.

I am sure that this will result in performance gain. This is however, a maintenance kind of thing and will be an ongoing affair.

However, i do need to find a way to make many function calls at once. I have done some reading on <b>dbms_job</b>, and would appreciate if someone pointed me to a site where i can get some working examples of the same.

Some doubts i guess i can ask right here:

1) The oracle documentation says that a job is broken (after 16 retries) if oracle finds an exception. If i handle the exception to return an error value, will that do? (Probably yes, but just wanted to confirm)

2) How will i convey to the parent procedure that the job completed successfully? I read that one can use dbms_pipe to do that but i am not sure how to implement it. Please please point me to a resource regarding this, or any other method.

3) Can i inform the different error states to the parent procedure?

ex:

select ...;
EXCEPTION
WHEN no data found THEN
return 77; (failure)
When too many rows THEN
return 88; (failure)
WHEN OTHERS THEN
return 99; (failure)

return 0; (success)

Thanks for all your help guys...

dbms_job is like a queue manager - cron or Windows scheduler.
You can use it to "multiplex" jobs if you need it, but if the process over all is not a batch kind of thing it may be more problem than it's worth.

After you finish analyzing tables, you really should

Then run tkprof against the trace file. Save the report.

Next go to ask tom and search on how to use tkprof. He has a lot of information on interpreting tkprof output.
http://asktom.oracle.com/pls/ask/f?p=4950:1::RESET::1
And while you are there download SQL developer, which does a lot of this stuff for you.
If you have an Oracle license it's free.

My experience is that almost always somebody has constructed a query or two that is
a complete bottleneck.

Yes, return <error value> will work. You may want to learn about RAISE in pl/sql for user-defined errors. If there are user defined conditions that are errors, this is may be a better approach.

Basic question as something to consider:

*Are any of the procedures recursive (calls itself)?
*Do they all access the same tables?
*Are indexes built on the main tables?
*Have you tried optimizing the queries within the procedures first? (that's where I would start)

Thanks for the info jim...we use RAISE when we want to exit the procedure at the point of error. If we need to give information regarding the error to the calling process, we use the return method.

Also, just to clarify...if i have two out params in the function call, plus a return value, will the dbms_job automatically return them back to the calling function after execution?

Hi mateoc15....

the answers are:

*no
*they all access the same table
*yes
*on-going