Re: Rollback tablespace size

mark_at_bobak.net (Mark J. Bobak) wrote in message news:<fe9b0e1b.0209092208.37b89481_at_posting.google.com>...
> For 9i, it's easy. Set the UNDO_RETENTION until you don't get ORA-1555
> anymore. Then add space to the undo tablespace, until you don't get
> ORA-1650 anymore.
>
> For pre-9i, it's essentially the same thing, but requires more manual work.
> It goes something like this:
> 1.) Determine the length of time of your longest running query.
> 2.) Determine the total volume of undo your database writes
> in that length of time.
> 3.) That number should be the total size of the sum of
> the OPTIMAL of all your rollback segments.
> 4.) Determine the largest number of concurrent transactions
> you will encounter, and divide by 4. That's the number
> of rollback segments you need.
> 5.) Take the number from step 3 and divide by number in
> step 4, that's the size of OPTIMAL for your rollback segments.
> You may want to put a little (say, 5%) padding
> in the number from step 3, to avoid resize thrashing.
> (If you inadvertently make OPTIMAL just slightly too small,
> your rollback segments will shrink/wrap themselves to
> death, and you'll get a flood of ORA-1555.)
>
> The above should properly size your rollback segments
> to avoid ORA-1555.
>
> 6.) What's the largest peak transaction volume your
> database needs to support?
> 7.) How many concurrent peak transactions do you expect?
> 8.) Multiply number from step 6 by number from step 7.
> 9.) Total size of your rollback tablespace should be
> large enough to contain the sum of the OPTIMALs of all
> your rollback segments (from step 3) plus the number from
> step 8.
>
> The above should allow you to properly size the rollback
> tablespace to avoid ORA-1650.
>
> Hope that helps,
>
> -Mark
>
>
> 76434.1353_at_compuserve.com wrote in message news:<3d7d0794.11438197_at_news.hccnet.nl>...
> > No problems yet but I want to have the rollback tablespace at a
> > reasonable size. So I'm wondering if there is connection between the
> > rollback tablespace and the size of other elements like user
> > tablespace.

Thanks for your info. Received on Wed Sep 11 2002 - 08:06:03 CDT