I have a fairly large table(200Mil rows) where data is inserted and
deleted regularly. Given in an hour data is deleted to almost
1-5million rows and the application is inserting of the similar load
in an hour.
The procedure is as follows, and this sp runs every 10 mins and
completes under 2 minutes, and lot of redo/undo is being generated, is
there a way to cut down the redo log size as I am noticing a BIG
problem when bouncing the database, it takes almost 2-3 hours to
cleanly shutdown the database.

<<Partial code>>
open c1;
loop
fetch c1 bulk collect into rids limit batchsize;
exit when rids.count = 0;
forall i in 1..rids.last
delete from DATA1 where rowid=rids(i) ;
v_del := v_del + SQL%ROWCOUNT;
commit;
dbms_application_info.set_client_info('purge_stat1 _proc processed
'||v_dell||' rows ');
end loop;
close c1;
commit;


Thank you
RA

On Mar 4, 1:21*am, python <ang... (AT) gmail (DOT) com> wrote:
Two main reasons:

DELETE rows one-by-one and commit inside a LOOP...

Two no-no's in one procedure.

Cheers.

Carlos.

On Mar 4, 4:01*am, Carlos <miotromailcar... (AT) netscape (DOT) net> wrote:

What version?

Sounds like you need a rework of your app so you are not having to
"hourly" delete so many records - also sounds like an adaptation of a
MySQL app.

Should you be fortunate enough to be using 11gR2 (11.2.0.x), look at
using DBMS_PARALLEL_EXECUTE. Works GREAT for these sort of things - in
parallel.