Re: Delete Performance Issue
- From: "Don Doo" <doodon@xxxxxxxxx>
- To: "Mercadante, Thomas F (LABOR)" <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>
- Date: Thu, 21 Dec 2006 15:49:21 -0500
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:
