Re: How far I am in my delete ?

Djordje, sounds like an awful experience. Good questions.

I can help explain the amount of rollback space that was taken I think: From class - rollback entries are by column, not by row. So it would seem to me that deleting a row would cause a rollback entry for every single column in the row you are deleting.

You can structure your deletes in such a way that you can see how far it has gone:

delete from mytable
where <all your conditions>
and rownum < [some number ];
commit;

write yourself a log or even select count(*) against the table while the delete is running.

Since you are doing a large delete, I'm guessing that a lot of the entries got written to the datafiles and that's why you would see dbwr and lgwr firing away when the rollback operation started. They are doing the necessary rollback changes to the datafiles.

in re: to when does the rollback take place after shutdown abort: Ver 7, I believe it's prior to the database opening. Ver 8+ the rollback takes place after the database opens.

If I am wrong, I am sure someone will correct me. In fact please do so I can learn from it.
Thanks
Lisa

Djordje Jankovic <djankovic_at_corp.attcanada.ca> on 06/12/2000 02:07:37 PM

Please respond to ORACLE-L_at_fatcity.com

To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> cc: (bcc: Lisa Koivu/GELCO)

Subject: 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 to other people too.
Djordje

--
Author: Djordje Jankovic
  INET: djankovic_at_corp.attcanada.ca

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may