delete performance problem

Hi,

I have an archiving process (ProC) which retrieves rows from selected tables and writes the output to text files which are them compressed, the archived rows are then deleted. This occurs on 4 servers all running the same archiving process on the same set of tables. On one particular server the delete process takes an extremely long time to complete.

The table is described as follows

SQL> desc tag_1m;

 Name                            Null?    Type
 ------------------------------- -------- ----
 ID_TAG                          NOT NULL VARCHAR2(10)
 PARAM_TAG                       NOT NULL VARCHAR2(2)
 DATE_TIME                       NOT NULL DATE       
 CODE_ACCEPT                     NOT NULL VARCHAR2(1)
 VALUE_TAG                                NUMBER(16,4)

The primary key is composed of

   date_time,
   id_tag,
   paran_tag
   code_accept

The delete statement is basically

loop

   delete from tag_1m
   where date_time < 'specified date'
   and rownum < 2501;

   commit;
end loop

The table contains approx 2.2m rows, 324,000 are deleted each day. Another process on a SCO server inserts 13500 rows each hour into the table. It takes about 19 hours to complete the delete of 324,000 rows daily. The server is a Sun SparcServer5 with 96mb RAM.

I have noticed on a number of occassions when running the delete process manually is that the number of rows deleted each hour is less and less ie

1st hour	60,000
2nd hr		40,000
3rd hr		35,000
4th hr		30,000 

etc and eventually tapers off at 18-20,000 rows/hr. There have been no other users on the system at the times when this has been monitored.

Does anyone have any ideas as to what may cause this and where I should be looking ??

Thanks Received on Fri Oct 24 1997 - 00:00:00 CDT