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 - 08:51:28 CST
