Re: optimal size for rollback

"Karen Abgarian" <abvk_at_ureach.com> wrote in message news:3D8CFB0B.9C1957AA_at_ureach.com...
> > I gave you the technical explanation, and whilst I could give you the
> > numbers (as I've done on many occassions in the past), its not actually
a
> > hard test to set up for yourself: build a 16K rollback segment with
optimal
> > set to 16K, and update 1000000 rows to force growth. Then update 1000
rows
> > to force shrinkage. Do the same thing without optimal. Create the
segment in
> > both LM and DM tablespace.
>
> I mentioned the extent sizing. How many extents you expect to be purged?
> Unless you really create rollback segments with 8K extents and have it
allocate
> thousands of extents. Can this be more than a few dozen in practice?
>
> When a shrink happens, the majority of work that needs to be done by
Oracle is
> to update fet$ and uet$ on dictionary managed tablespaces. There are good
> chances
> that contents of these tables will be cached. If so, Oracle will have to
do a
> few
> operations in memory. Even if it needs to go to disk, we are talking
about
> reading a
> few blocks. Without getting into too many details, there is also internal
> locking for
> fet$/uet$, this should depend on how actively they are
allocating/deallocating.
> On LMTs
> this shrink is to flip the bits in the file header(s).

Actually, there are trips to the dictionary even with LMTs. They reduce contention for the data dictionary tables, they don't eliminate them altogether. Something has to know which bits belong to which segment, and thus which bits to flip. And there are queries against the data dictionary tables, too, since the setting for optimal has to be checked. By every transaction as it crosses the extent boundary, whether or not a shrink has to actually happen.

And your transaction waits as those checks are performed.

>
> How many seconds do you expect the above to take, if we assume that the
rollback
>
> segments are NOT sized like you suggested for the experiment? A second, t
wo
> seconds?
> What this performance can be dependent on? Any numbers?
>

Frankly, who cares how long it takes? It's extra work being done by the database (thanks for finally aknowledging it has to happen). And that extra work is entirely optional, because it doesn't happen when optimal is not set.

You might consider the waits trivial. That's another of your value judgements, which wouldn't apply to all databases at all times. That they're there at all, however, is not a value judgement, but a matter of plain, hard fact which does apply to all databases for which optimal is in use.

HJR
> Regs
> AK
>
>
Received on Sat Sep 21 2002 - 18:24:23 CDT