CODEWITHSUNDEEP
.netmultithreadingoracle-databasestored-proceduresplsql
Waqas Ali
Waqas Ali

Reputation: 1529

Multi threading Concept in Oracle pl/sql

I created a procedure, which takes some input in the form of an Array, which is being passed from a .NET Web Application.

IN PROCEDURE: I open a cursor containing all data in the table, then fetching data from cursor row by row and apply validation rules on it.

For each row I need multiple hits to DB (almost containing nested queries); if data validation fails then I update remarks field in same table (by using an update query immediately), and if validation is successful then inserting/updating data in other table.

I tried it on 0.25 Million records, and I noticed that it takes more than one hour to process it.

I need to improve the performance of this Stored Procedure. Please let me know how to achieve this.

I have an idea about this.

Making multiple sets of record (10 k in each set) and then process each set just like the way we use multi threading.

Is this possible? If yes then how?

Upvotes: 1

Views: 2156

Answers (2)

ibre5041
ibre5041

Reputation: 5298

There is noting like multithreading in Oracle. If you really need to run something in background(parallel) you need either JOB or SCHEDULER_JOB. You can "theoretically" speedup your sequential(batch) processing by using:

  • bulk/forall constructs
  • "WHERE OF CURRENT" condition in UPDATE statement
  • replacing INSERT/UPDATE with MERGE statement
  • use "in memory" tables in joins - SQL type "TABLE OF <something>"
  • use PARALLEL hint - in some complex SQL queries

Just keep in mind that "PL" and "SQL are two distinct "virtual" machines and context switches between them are(can be) slow.

Use of parallelism/multithreading is common solution in the Java/.NET world. In Oracle this approach is not used to often.

Upvotes: 3

igr
igr

Reputation: 3499

Very likely, your procedure can be made faster by use of PL/SQL batch processing capabilities. To run code in parallel, look at DBMS_JOB and DBMS_SCHEDULER packages. Also, check if parts of your code can be speed up by Parallel Query and/or Parallel DML.
Line by line is the slowest thing, especially if you used explicit cursor.

Upvotes: 2

Related Questions