We help IT Professionals succeed at work.

Oracle PL/SQL Procedure is running very slow

TClevel asked
I have an oracle pl/sql that is running very slow. It is trying to process a million plus records. It is checking a field and will do an insert, update or delete depending on the value of the field. Can someone show a better way to write this procedure to process these records. the proccess has been running for 4 1/2 days now.
I have attach the procedure to this email below


Watch Question

because you do a count at every record:

is the column data_custom_accts.account_number indexed and of same type as data_custom_staging.account_number?

If you have millions of rows in the table you really shouldn't be using a plsql loop to process it, instead try using set processing i.e. re-design your code to use standard sql statements with as little plsql code as possible.

I've attached some example code to show what I mean.

This sample first identifies incorrect data in the staging table and moves problem rows to another table where it can be reported.  The problem rows are then removed from the staging table leaving only good data which can then be used to insert, update and delete the main table.

Obviously there may be an issue with performance depending on indexes, but generally, this approach should be used for processing large tables.

Hope you find this useful.
-- First need to tidy up the data in the staging table by
-- sidelining problem rows
insert into sideline_error_table
select account_number,purge_indicator,count(*)
from data_custom_staging s,
     data_custom_accts a
where a.account_number = s.account_number
group by account_number,purge_indicator
having count(*) > 1;
-- Now delete the sidelined data from the staging table
delete from data_custom_staging s
where exists (select null
              from sideline_error_table e
              where e.account_number = s.account_number
              and   e.purge_indicator = s.purge_indicator);
-- You can report on the problem rows by selecting them from the sideline
-- table if required.
-- The remaining staging data should be valid so just need to process it
insert into data_custom_accts a
select *
from data_custom_staging s
where s.purge_indicator = 'N';
update data_custom_accts a
where exists (select null
              from data_custom_staging s
              where s.account_number = a.account_number
              and   s.purge_indeicator = 'D';
delete from data_custom_accts a
where exists (select null
              from data_custom_staging s
              where s.account_number = a.account_number
              and   s.purge_indicator = 'P';

Open in new window

It's worth pointing out that the data_custom_staging table should, as a minimum, have an index on the account_number column.  This would probably help with the subqueries, however, it really depends on data volumes and which version of Oracle/Optimiser is being used.


I do not have an index on the staging table.
The index on the staging table would only be helpful with the set based sql example I gave you.  It wouldn't make any difference with your original plsql code as you only access the staging table once and read every row within a cursor loop.

Adding an index on account_number to the data_custom_accs table (as mentioned by jwahl above) would help your original script, however, I believe using set processing would give you a much bigger performance increase.


Okay I will try the set processing.

What is the syntax for the set processing?
Set processing is a concept rather than a specific syntax.

I'm sure you're aware that you can write straight forward SQL statements e.g. SELECT, INSERT, UPDATE, DELETE etc. to manipulate data.  These statements act on a "set" of data.  If you have a lot of data to process, it is preferable to write fewer SQL statements to act on large "sets" .

When you start to use cursor loops in plsql your are introducing lots of repetative processing which doesn't lend itself to processing large volumes of data.

So rather than writing cursor loops to process a single row at a time (or even multiple rows using bulk fetches etc) it is always better to write SQL statements that act on many rows in one go.

The sample code snippet I gave above is an example of completely changing the structure of your program to use SQL statements that perform a particular function on large sets of data e.g. identify ALL duplicate rows in the staging table and move them to a sidelined table, delete ALL duplicate rows from the staging table,  insert ALL new rows into the account table, update ALL existing rows in the account table and finally, delete ALL redundant rows from the accounts table.

The key here (and the essence of set processing) is that each sql statement performs a specific task, but it performs it on a large data set and performs it only once.

As these are still standard SQL statements you can run them from a PLSQL block or stored procedure, so the sample code snippet I gave earlier could exist within a PLSQL block.  Obviously, in the case that you want to report selected data (as in sidelined rows) you would need to perform the select as a cursor loop to enable dbms_output to write the data out for each row.  Using bulk fetch would increase performance, however I would assume that you wouldn't expect that many accounts to be in error so this is probably unnecessary.

Alternatively, the whole script could be run through SQL*Plus.  The choice on how to run the code is down to you.
SujithData Architect
Your audit logic must be taking a lot of time.

To speed up the code:
 -- move this audit logging procedure out of the row level processing
 -- Use bulk operations to perform the DML

As I cant really re-write your code, I can give a pseudo code, which you can use as a direction.
.. --declarations
  CURSOR Data_customer_load_cur
    IS SELECT * FROM  Data_custom_staging;
-- logic to do the audit logging for all the records at the beginning of the processing
-- here you check whether the account number from staging table is already there in Data_custom_accts
-- If present; based on the purge indicator do audit logging
-- You can further enhance this code by using bulk operations as well
-- pseudo code follows
FOR rec in (select a.ACCOUNT_NUMBER, a.purge_indicator 
            from Data_custom_staging a INNER JOIN (select distinct ACCOUNT_NUMBER from Data_custom_accts ) b
            ON a.ACCOUNT_NUMBER = b.ACCOUNT_NUMBER ) loop
  -- all the records in this loop are already present in the custom accts table.
 if rec.purge_indicator = 'INSERT' then
   --prepare the audit strings here.
 elsif ...
 end if;
 Data_insert_audit_log (....., rec.purge_indicator);
end loop;
-- Now the logic to do the DMLs based on the purge_indicator.
  OPEN Data_customer_load_cur;
  FETCH Data_customer_load_cur INTO Data_customer_load_rec;
EXIT WHEN Data_customer_load_cur%NOTFOUND;
IF Data_customer_load_rec.purge_indicator = 'N' THEN
 -- do insert
ELSIF Data_customer_load_rec.purge_indicator = 'D'
..-- do update
  end loop;

Open in new window

Explore More ContentExplore courses, solutions, and other research materials related to this topic.