Re: Delete Performance Issue

Hi,

 Thanks for all the replies.  I will try re-writing the code using PL/SQL.
There is a good chance for that to work. Generally we have around 200,000
rows a day from January to Mid November.  After that the volume starts to
increase because of increased sales activity.  when we are deleting 200,000
rows that operation is complete
in about 2 minutes.  But when the volume increases to 2.5 million (about 12
times) the response time increase is not proportionate.  If that were the
case the process should have completed in 25 minutes.  But it takes
four hours.  So rewriting the code as Thomas suggested could improve
performance because that will make the volume less for individul delete
operations.  This is a third party
product and we have to deal with the vendor to get it done.

I am not interested to know why the increase in response time is not
proportionate to the increase in data volume. Could somebody tell where to
look?.

I tried parallel execution it was working fine in test environment but not
consistant in the production environment where this process runs with other
processes.  The value of parallel_adaptive_multi_user is true and I got the
answer from Jonathan's new book page 30.

Thanks again for all the replies.

Don



On 12/21/06, Mercadante, Thomas F (LABOR) <
Thomas.Mercadante@xxxxxxxxxxxxxxxxx> wrote:


   Don,



Try rewriting this as a PL/SQL block like below.  Believe it or not,
simple rewrites get unbelievable results.  You could try some simple timings
by running the original delete for a couple of thousand records and then run
it this way.



If you changed the statement below to bulk collect the cursor into a
PL/SQL table, you could then perform a bulk delete and see even better
improvements with periodic commits to get you a restart point if you need
it.



As always, test this out to make sure you will be happy.


Good Luck!

 Tom

 Declare

Cursor c1 is

Select RunSeq

from   C_Run pr,
               C_Summary ss,
               C_Stage st
      where  pr.RunSeq = ss.RunSeq
                and    ss.stageType = st.stageType
                and    st.name = 'load'
              and    pr.period = 361
       and    ss.Active   = 'yes');

begin

 for c1_rec in c1 loop

delete from c_trace
 where targetperiod= 200612

  and runseq = c1_rec.runsq;

end loop;

end;

/

Other related posts: