Comments below...
Regards
HJR
--
--------------------------------------------------------------------------
Opinions expressed are my own, and not those of Oracle Corporation
Oracle DBA Resources: http://www.geocities.com/howardjr2000
--------------------------------------------------------------------------
"Pierre Charpenay" <pcharpenay_at_unilog.fr> wrote in message
news:39D1DE3C.FEF72B67_at_unilog.fr...
> Hello !
>
> Does anyone know some rules about the rollback segments consumption for
> the statements :
>
> SELECT (none I suppose)
Not true, sort of. If I select a row that is currently undergoing an
update, then Oracle will generate a read-consistent image of the data (ie,
the old value) -and it obtains the required prior values by using the
rollback block used by the original transaction. So it's true that a select
doesn't consume "new" rollback blocks, but it does use other people's
rollbacks. And if it can't use the requisite block at the right time, then
the infamous ORA-01555 Snapshot too old error is returned.
> INSERT (?)
> DELETE (?)
> UPDATE (?)
>
> I write them in a supposed growing consumption order. Can anyone confirm
> that ?
>
Also not true. A rollback block stores the prior image of the data. The
before image of an insert is the rowid where the insert is to take place.
Which is trivial in size -but it would still require an entire rollback
block.
The before image of a delete is the entire row -which sounds like a lot, but
again, if you were doing a single delete, you'd still end up using the
entire rollback block (assuming your record length was less than the size of
a single Oracle block).
An update stores only the piece of information being changed -but (getting
the picture?!) a single update would still use an entire block.
When you are doing mass updates, deletes and inserts, then it is true that
you will charge through your extents at a fast rate of knots doing deletes,
a slower rate of knots doing inserts, and a positively glacial pace when
doing small updates.
> Thanks
>
> --
> Pierre CHARPENAY
Received on Thu Sep 28 2000 - 07:02:11 CDT
