How far I am in my delete ?

Hi friends,

Few nights ago I had an interesting experience. I was deleting 90% of rows from a decently large table (300M, 5.5M rows). At the same time the table was being constantly inserted (approx. 8 rows per minute). After few hours, I started wondering when it is going to be finished. Rollback space kept increasing and increasing, as well as the number of redo log files generated.
To make the long story short I had to stop the process (it was slowing down the other stuff, and the start of a busy day was close) after five hours of deletes, and around 1G of rollback space taken. The rollback itself took almost another four hours.
I learned my lesson - never do massive deletes if you do not have to. Next time I will copy the records to another table, drop the existing one, rename the new one. The problem with that is that I will have to stop inserts, but I am not going through the same ordeal again. Questions for you gurus:
1. Is there a way to find out how far a delete has gone. I was looking at statistics from v$sesstat but could not find anything indicative. 2. How would you estimate the amount of rollback that is going to be generated in an operation. In my case deleting 270M form a 300M would have generated more than 1G of logs - I expected that those numbers should be comparable, and definitely not 1:3 or more. 3. Was the relation between rollback and operation time, 4:5, for a delete a typical one. Similar question to the previous one - can one estimate how long a rollback operation would last ? 4. I was watching processes while the rollback was on. Earlier I was under the impression that the rollback would be performed by PMON, but it seemed to me that it was done by the server process itself (it was in "marked to kill" state) - with the help of DBWR and LGWR, but PMON was not involved. Is that true ? What would happen if that server process was terminated on the unix side - who and how would end the rollback ? 5. One last question: if I had to shutdown the instance abort while it was rolling back, what would happen when I restart it. Would the database be opened for all the users, while that process would is being rolled back in parallel (as far as I remember this is what I learned from a manual), or oracle would oracle wait to rollback the transaction and only than open the database ?

Thanks for all your time, but I thought those questions may be of interest Received on Mon Jun 12 2000 - 13:03:06 CDT