Running a stored procedure in multi threaded way in oracle

0
votes

I have a job which picks a record from a cursor and then it calls a stored procedure which processes the record picked up from the cursor.

The stored procedure has multiple queries to process the records. In all, procedure takes about 0.3 seconds to process a single record picked up by the cursor but since cursor contains more than 100k records it takes hours to complete the job.

The queries in the stored procedure are all optimized

I was thinking of making the procedure run in multi threaded way as in java and other programming language. Can it be done in oracle? or is there any other way I can reduce the run time of my job.

1
oracle
First you should be certain that you can't complete your task in a set based fashion. - Nick.McDermaid
You can run different jobs, after you separate the data in buckets (just after bulk collecting the cursor data), then run parallel dbms_job(s), that would do the work at the same time on the different sets. - g00dy
Like @Nick.McDermaid said, probably the biggest problem is that you are using a cursor. - James Z

1 Answers

0
votes

I agree with the comments regarding processing cursors in a loop. As Tom Kyte often said "Row at a time [processing] is slow at a time"; Oracle performs best with set based operations and row-at-a-time operations usually have scalability issues (i.e. very susceptible to poor performance when things change on the DB such as CPU capacity, workload, number of records that need processing, changes in size of underlying tables, ...).

You probably already know that Oracle since 8i has a Java VM built in to the DB engine, so you might be able to have java code wrappered as PL/SQL, but this is not for the faint of heart [not saying that you are, just sayin'].

Before going to the trouble of re-writing your application, I would recommend the following tuning approach as it may yield some actionable tunings [assumes diagnostics and tuning pack licenses; won't remove the scalability issues but may lessen the impact of them]:

In versions of oracle 11g and above:

  1. Find the the top level sql id recorded in gv$active_session_history and dba_hist_active_sess_history for the call to the PL/SQL procedure.

  2. Examine the wait events for the sql_id's under that top_level_sql_id. (they tell you what the SQL is waiting on).

  3. Run the tuning advisor on those sql_id's and check for any tuning recommendations. Sometimes if SQL is already sub-second getting it from hundredths of a second to thousandths of a second can have a big impact when call many times.

  4. Run the ADDM report for the period when the procedure is running. Often you will find that heavy PL/SQL processes require increase in PGA. Further, ADDM may advise other relevant actions (e.g. increase SGA, session cached cursors, db writer processes, log buffer, run segment tuning advisor, ...)