RE: How far I am in my delete ?

> list has any experience with that. You also said "parallel rollbacks can
> occur" - is that "can" a sign of not being sure or it depends on something
> whether rollback will be in parallel or not.

You'll have to check the books, as I don't remember the circumstances controlling parallel rollback.

Jared

>
> Thanks.
>
> Djordje
>
>
> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com] On Behalf
> Of Jared Still
> Sent: June 13, 2000 11:54 AM
> To: Multiple recipients of list ORACLE-L
> Subject: 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
> jkstill_at_bcbso.com - Work - preferred address
> jkstill_at_teleport.com - private
>
>
> --
> Author: Jared Still
> INET: jkstill_at_bcbso.com
>
> 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 Received on Tue Jun 13 2000 - 17:12:21 CDT