Re: OPTIMAL on Roll Back Segments

On Wed, 14 Jul 2004 13:14:11 -0400, paddy_nyr wrote:

>
> My initital storage is 5m, minextens 4, maxextents249, next 5m, optimal 20m.

You've recently switched from Oracle7, haven't you?

>
> I read somewhere that not specifying an OPTIMAL size when creating your
> rollback segments helps because, if set, Oracle will dynamically shrink
> rollback segments which have grown larger than the specified size by
> deallocating extents. However, it may deallocate extents which still contain
> committed pre-images required for read-consistency. Not setting the OPTIMAL
> value eliminates this cause of "snapshot too old".
>
>
> Is this true?

The short answer is "NOOOOOOOOOO". The long answer is: Yes, optimal can cause 0ra-1555, by deallocating blocks needed for a long running query, but nobody at the right mind would set optimal anyway, so that isn't very important. What is ORA-1555? You get ORA-1555 when a query cannot reconstruct an image of a block as it was before the query has started, because the undo blocks needed for such reconstruction are missing in action. Undo segment has a header in which it keeps transaction table. When transaction commits, undo blocks held by that particular transaction are declared free and available for reuse. Undo blocks and extents are used serially, and in a circular fashion. If your query is taking a long time, and the transaction that modified some of the blocks needed by the query has committed, there is likelihood, that SMON will cut those blocks off the rollback segments, causing the query to fail. Also, setting optimal causes transactions to incessantly grow undo segments, which are then being shrunk by the evil gnome called SMON in the wee hours of the night. Tomorrow morning, the first few lusers will have to grow undo segments yet again. It's called "dynamic space management" and is the worst nightmare of any DBA. In Texas, they execute people for setting the OPTIMAL parameter. It's a big no-no.

-- 
A city is a large community where people are lonesome together.