Oracle Database Discussions

We are attempting to run a PL/SQL loop that deletes from 3 very active tables in our production instance. It commits every 500 iterations. After running this loop for about 2-3 minutes, we saw many ACTIVE connections queuing up, causing a high load. This was not expected because the data that is being deleted from these 3 tables is essentially inactive; it's old and we're purging it.

Here's pseudocode:

begin
get min date from master table
get current date - 480 days (16 months); this will be our retention
loop until the min date variable reaches the current date variable
delete from child table 2
delete from child table 1
delete from master table
commit every 500 iterations
end loop
end
final commit

This is causing issues when running against the live instance. Is there a way we can avoid locking issues (row exclusive) that the DELETE statements are causing - or is there absolutely no workaround around there?

We deleted the rest of the data beyond 16 months using a CTAS (CREATE TABLE... AS SELECT) and bulk insert. We are using this loop to carefully round out the data that's going to be removed just before the 16 months retention so we do not have any bad data that needs to be kept as usable.

We are running one instance of Oracle EE 10.2.0.4. All 3 tables are LOCALLY MANAGED using ASSM.

Thanks.

Edited by: Bob Sislow on Jul 2, 2009 12:42 PM