Re: Rollback tablespace size

From: Mark J. Bobak <mark_at_bobak.net>
Date: 9 Sep 2002 23:08:06 -0700
Message-ID: <fe9b0e1b.0209092208.37b89481@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.
Received on Tue Sep 10 2002 - 01:08:06 CDT