Re: Rollback performance and delete

As Howard points out, the content of the rollback segment has to contain enough information to reverse the delete (which is why, in the case of a delete, the entire row is copied).

Apart from the actual data, there is an overhead which includes -

    locator information for the
    state information for the block
    state information for the rollback header segment     linked-list information for the rollback data

Try this as an estimate:

    (400 bytes + row len) for each row deleted     (200 bytes per index) for each index on the table

Multiply by number of rows and see how close this gets you to 5 Gb.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases
Publishers:  Addison Wesley Longman
Book bound date now 8th Dec 2000

Allen wrote in message <3A083F5F.B2BA914E_at_motorola.com>...


>"The only time that an entire block is

>affected by a record-sized update is when the tablespace involved is in the

>middle of a hot backup.  You're not doing a hot backup whilst deleting, are

>you?"

>

>No, I am not doing a hot backup that I know of, but maybe there is

 something to


>investigate here.  Could some parameter be set in Oracle that is causing

 some


>confusion or improper execution? The other items you mentioned I can answer

>completely.  I am the only user using this rollback segment.  I created it

 and I


>am the only one that has permission to it (our database is read only with

 only


>one user having update privileges.  Therefore, I need to find another

 answer to


>explain why so much data is written to the RBS.  I have also seen this

 problem


>with other deletes, so this is not the first time.

>

>If you know of some specific things I could look into, I would appreciate

 it.


>

>Thanks

>

>- Allen

>

>"Howard J. Rogers" wrote:

>

>> See below.

>> HJR

>> --

>>

>> -------------------------------------------------------------------------

 --


>> Opinions expressed are my own, and not those of Oracle Corporation

>> Oracle DBA Resources:               http://www.geocities.com/howardjr2000

>> -------------------------------------------------------------------------

 --


>>

>> "Allen" <allen.brost_at_motorola.com> wrote in message

>> news:3A080B24.D007A0DF_at_motorola.com...

>> > > Oracle copies before image copies of all changed blocks into the

>> > > rollback segment. So if your 1G of deleted data spread over 5G of

>> > > data blocks, 5G will be written into rollback segment.

>> >

>> > So far the above explanation is the only one I have seen to explain

 this.
 The


>> > index I have is about 1 G also, so if I delete 1G of data and 1/5th of

 the


>> > index, how does this result in a rollback segment that is >5G in size!

>> >

>>

>> It's not an explanation, it's a statement of utter rubbish.

>>

>> How's this: "whatever you delete will result in 5 times the amount of

>> rollback being generated"?

>>

>> There.  That's an "explanation".

>>

>> It's total rubbish, of course, but it 'deals' with your problem.

>>

>> The plain fact is, I have no idea of your particular set up.  I have no

 way


>> of knowing (since you don't show us) that what you are deleting is

>> *actually* a mere 1Gb in size.  I have only your word on it (since you

 show


>> us no output from queries) that there is only 250Mb-worth of index being

>> affected.  You mention nothing about the database being in restricted

>> session mode, so I have no idea whether yours is the only transaction

>> affecting the rollback segment (just because you've bagged it for your

>> delete doesn't mean its immune from being used for other transactions).

>>

>> Nevertheless, it remains the case that the rollback segment stores the

>> before image of a delete (ie, the entire record being deleted), and that

>> indexes on the deleted record will also have the relevant before image

>> stored.  There will be some house-keeping data stored as well, which

 means


>> that rather more than the entire record-length is stored in rollback.

 There


>> are no entire Oracle blocks involved.  The only time that an entire block

 is


>> affected by a record-sized update is when the tablespace involved is in

 the


>> middle of a hot backup.  You're not doing a hot backup whilst deleting,

 are


>> you?

>>

>> Regards

>> HJR

>>

>> >

>> > "Howard J. Rogers" wrote:

>> >

>> > > "Alex Filonov" <afilonov_at_pro-ns.net> wrote in message

>> > > news:8u7chm$3mn$1_at_nnrp1.deja.com...

>> > > > In article <3A06DE5D.30933844_at_motorola.com>,

>> > > >   Allen <allen.brost_at_motorola.com> wrote:

>> > > > > I have looked through the Oracle documenation to understand

 exactly
 what the


>> > > > > rollback segment is doing during a DELETE and I am still a little

 confused with


>> > > > > the results I am finding.

>> > > > >

>> > > > > I am doing a delete of a large table.  The table is 5G.  I am

 deleting
 about 1G


>> > > > > of the the table.

>> > > > >

>> > > > > I have created a large rollback segment to handle the transaction

 and
 I have set


>> > > > > the size of the rollback segment to about 2G.  I figured that

 this
 should be


>> > > > > plenty.  When I do the delete, I see the rollback segment grow to

 5G.
 I don't


>> > > > > understand this.  I thought the rollback segment held all of the

 original rows


>> > > > > before the change.  If this is so, then I would expect to see

 about
 1G
 in the


>> > > > > segment.  It seems like Oracle is copying everything in the table

 to
 the


>> > > > > rollback segment.  Anyone know why?

>> > > > >

>> > > > > - Allen

>> > > >

>> > > > Oracle copies before image copies of all changed blocks into the

>> > > > rollback segment. So if your 1G of deleted data spread over 5G of

>> > > > data blocks, 5G will be written into rollback segment.

>> > >

>> > > If you are suggesting that an update to one record will result in the

 entire


>> > > block being copied into rollback, then that's not correct.

>> > >

>> > > Regards

>> > > HJR

>> > >

>> > > > >

>> > > > >

>> > > >

>> > > >

>> > > > Sent via Deja.com http://www.deja.com/

>> > > > Before you buy.

>> >

>