| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Autoextend for rollback
On Fri, 18 Jun 1999 08:58:58 -0400, Kenneth C Stahl
<kstahl_at_lucent.com> wrote:
>Has anyone ever used auto extend for tablespaces that only contain
>rollback segments?
>
>For example, let's say I create a tablespace called RBS which will only
>hold rollback segments and give it a size of 15M. I then create ten
>rollback segments with INITIAL 100K NEXT 100K OPTIMAL 200K MINEXTENTS 2
>MAXEXTENTS 249. Quite obviously, the first time that I have a
>transaction which causes a rollback segment to grow towards 249 extents
>I'll get the "snapshot too old" error message and my transaction will
>rollback.
No, when your transaction reaches the MAXEXTENTS limit of RB segment, you'll get the ORA-1628(max number of extents reached). If before that you reach the limitation of the tablespace size you'll get ORA-1650 (unable to extend rollback segment). The ORA-1555 (snapshot too old) has nothing to do directly with the tablespace size or RB maxextents limitations, and will generally be returned to query operations, no to transactional operations.
>However, if I give the tablespace the ability to auto extend
>then the tablespace will grow as needed. I realize the risk here is that
>if I have multiple transactions that are all causing the rollback
>segments to grow to many extents that I'll end up with a collossal
>tablespace but that is something I could always monitor and adjust as
>needed - but at least I wouldn't be beeped in the middle of the night
>when batch processing is going on and some job aborts because I ran out
>of space in the RBS tablespace.
>
>Thoughts? Dark corners? Nooks and crannies? Convential wisdom?
The conventional wisdom here is to estimate the right number of RB segments and also to assign them the correct physical attributes so that they fit your with average transaction rate/size. Extending of segments (RB or any other type) is quite expensive operation, so you have to size your RBs so that they rarely have to extend. In general, the waste majority of database transaction sizes and their rate can be predicted and estimated in advance, so you size your RBs according to them. However, there usually there some exceptional (mostly maintenance) operations that can't fit to the ordinary rollback segments. So it is common practice to assign a special, large tablespace to a special rollback segment with larger physical attributes. You normally keep this large RB offline, but when there is a need for a large transaction you put this RB online and assign the transaction to it (either with SET TRANSACTION or by putting all other RB segments offline).
So, in general, for well maintained database, there should be no need to set the rb tablespace's datafiles to autoextend. However, I don't deny that sometimes this autoextend option is very comfortable, especially if the database is not under constant monitoring by a DBA.
>Ken
HTH,
Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)
 |
 |