Oracle PL/SQL Procedure is running very slow

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

thanks


load-procedure.txt
TClevelAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jwahlCommented:
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?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MilleniumaireCommented:
Hi,

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
as
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

0
MilleniumaireCommented:
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.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

TClevelAuthor Commented:
I do not have an index on the staging table.
0
MilleniumaireCommented:
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.
0
TClevelAuthor Commented:
Okay I will try the set processing.

What is the syntax for the set processing?
0
MilleniumaireCommented:
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.
0
SujithData ArchitectCommented:
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.
PROCEDURE SUNCOM_CUSTOM_LOAD(p_file_name  VARCHAR)
AS
..
.. --declarations
..
  CURSOR Data_customer_load_cur
    IS SELECT * FROM  Data_custom_staging;
 
begin
-- 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;
  LOOP
  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 IF;
 
  end loop;
end;
/

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.