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