Re: How far I am in my delete ?

> 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 ?

You don't say what version of Oracle you are on.

In versions prior to 7.3, all rollbacks had to be completed during instance recovery before the database was opened. These happened in serial.

As of 7.3, the 'warm start' feature may enable the database to be opened sooner. See the Concepts Manual for details. Look for 'warm start' and 'rollback' in the index.

Rollback always occurs as a serial operation in these versions.

As of 8.0 or 8.1, parallel rollbacks can occur during instance recovery.

In addition, the 'fast start' feature can allow the database to be opened much faster than version 7.x databases. Again, see the Concepts Manual for details.

Jared

On Mon, 12 Jun 2000, Djordje Jankovic wrote:

> 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
> also send the HELP command for other information (like subscribing).
>

Jared Still
Certified Oracle DBA and Part Time Perl Evangelist ;-) Regence BlueCross BlueShield of Oregon Received on Tue Jun 13 2000 - 09:41:42 CDT