Re: Rollback performance and delete

"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.
> >
Received on Tue Nov 07 2000 - 11:43:59 CST