Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> 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_at_labor.state.ny.us> 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;
>
> /
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Dec 21 2006 - 14:49:21 CST
![]() |
![]() |