| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: How far I am in my delete ?
Thanks Jared,
Yes I forgot to say I am in 8.0.4.
I've heard of the 'warm start' (though I did not know how it was called) however I never had a chance to see it in action. As this was the production database close to the begging of a high activity day I was not too happy to try it than, and I wanted to find out whether somebody on the 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.
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 ?serial.
> 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
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 Received on Tue Jun 13 2000 - 15:17:18 CDT
 |
 |