Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Questions on extents, etc.

Re: Questions on extents, etc.

From: spencer <spencerp_at_swbell.net>
Date: 2000/04/21
Message-ID: <%g7M4.595$PP5.6822@news.swbell.net>

The MAXEXTENTS parameter on the tablespace does not limit the size of the tablespace per se... it is simply a default value that is used for an object (for example, a rollback segment) when a value for MAXEXTENTS is not given in the create statement for the object.

for example, the following statements accomplish the same thing as the statements in your previous post:

create tablespace ROLLBACK_TBS
default storage

initial 160K
next 160K
pctincrease 0
minextents 2
maxextents 30
;
create rollback segment ROLL1_RBS
tablespace ROLLBACK_TBS
;

the only difference being that now, an object created in ROLLBACK_TBS without MAXEXTENTS specified will have maxextents set to 30, as opposed to the value of 100 in your example.

It is permissible to allow the rollback segments to (as you put it) "overlap"... that is, to allow the total "maxextents" to be larger than the size of the file.

for example, you could create each of the three rollback segments like this:

create rollback segment ROLL1_RBS
tablespace ROLLBACK_TBS
minextents 2
maxextents 120
optimal 20
;

btw... you aren't guaranteed that each batch transaction will use a different rollback segment, unless you specify which rollback segment each transaction shoud use. with only three rollback segments, you run a greater chance that two concurrent "large" transactions will be assigned the same rollback segment.

Not knowing anything else about your environment, it's hard to give you any specific recommendation here, other than to say, these don't look like rollback segments I would create in my database. Is there a reason you only have three non-system rollback segments ? The Oracle guidelines specify a value of 8 as a minimum number of non-system rollback segments in a database.) Maybe you have some others somewhere else ?

The number of and maximum size you choose for each rollback segment is going to depend on your particular environment, for example, the number of concurrent transactions you are expecting your database to handle. A large number of concurrent transactions means that you will want to have more rollback segments. If you are going to be running "large" batch transactions, then you are going to want to make sure those transactions use a rollback segment that is large enough.

In order to handle the mix of a lot of "small" transactions, and a few "large" batch transactions, I created a sufficient number or rollback segments (estimated using the "Rule of Four") , and set the "optimal" size of the rollback segment to handle 4 "normal" OLTP transactions and the maxextents of each to handle the largest batch transaction. An estimate of 50 concurrent transactions and 4 transactions per rollback segment, gave me 12.5 rollback segments... the estimate is probably on the high side of normal, so I rounded down to 12.

To reduce the probability of the rollback segments "extending", I chose to use a large number extents in the initial allocation. (Oracle guidelines indicate that there should be a minimum of 20 extents in the initial allocation.) To handle large batch transactions, I set the maximum number of extents to a number large enough to use up half of the remaining space (subtracting the total space for the optimal size of all of the rollback segments in the tablespace.) I chose _not_ to use "unlimited" maxextents to prevent a runaway transaction from using up all available space in the tablespace. (That's the reason why, as someone else said, it's not a good idea to specify maxextents "unlimited" for rollback segments, even if you have AUTOEXTEND turned on for the datafile.)

To make sure that oracle would use all 12 rollback segments, in the init<sid>.ora configuration file, i set the paramaters TRANSACTIONS = 48
TRANSACTIONS_PER_ROLLBACK_SEGMENT = 4
MAX_ROLLBACK_SEGMENTS = 14 (Note: in addition to the system rollback segment in the SYSTEM tablespace, i have one other non-system rollback segment in the SYSTEM tablespace which is normally "offline"... in order to bring all of the rollback segments online, the minimum value for MAX_ROLLBACK_SEGMENTS is therefore 1+1+12...)

At the beginning of the nightly batch processing, I issue 'alter rollback segment <rbs_name> shrink;' statements to free up space in each rollback segment. (I don't think this is necessary, Oracle seems to be unallocating extents and shrinking the rollback segments back to "optimal" on its own. This has been working for me, and I have not had to resort to creating a special offline rollback segments for use by batch transactions...

Either I have set the rollback segments up correctly, or I have been extremely lucky, in that I have encountered the notorious ORA-01555 ... (rollback segment too small) message or any "insufficient space" type messages ORA-01562, ORA-01560, ORA-01628 concerning the rollback segments.

HTH. Good luck.

<argosy22_at_my-deja.com> wrote in message
news:8dn1ac$dt9$1_at_nnrp1.deja.com...
> HI,
>
> Thanks for responding.
>
> So in this example, even though the tablespace is 25M,
> less than 15M are being used by the 3 rollback segments.
> So, about 10 megs are not utilized.
>
> You say the MAXEXTENTS in the tablespace, puts a secondary
> limit on the rollback segments. Say I instead create each
> rollback segment to have 100 extents. Then each rollback
> segment is the same max size as the tablespace.
>
> Would those rollback segments eventually conflict with each
> other, running out of room in the tablespace. That is,
> given a 3 or more big batch jobs running at once.
>
> I'm wondering if I have to consider the sizing of both
> tablespaces and rollback segments when I am creating them?
>
> Thanks,
>
> Argosy
>
>
> In article <%xuL4.2131$pQ1.31833_at_news.swbell.net>,
> "spencer" <spencerp_at_swbell.net> wrote:
> > if these three rollback segments are the only objects
 created in
> > the tablespace, the maximum amount of storage that will be
> > allocated within the tablespace is
> > 3 * ( 30 * 160K) = 14400K
> >
> > + one oracle block (blocksized not known) for each
 datafile
> > <= 64K )
> >
> > = 14464K = 14.125 megabytes
> >
> > the MAXEXTENTS parameter of each rollback segment is what
 limits
> > the space allocated to each rollback segment.
> >
> > the MAXEXTENTS parameter on the tablespace is a default
 value
> > applied to any object (e.g. a rollback segment) created in
 the
> > tablespace that does not have a MAXEXTENTS value specified
 in
> > the create statement.
> >
> > hth
> >
> > <argosy22_at_my-deja.com> wrote in message
> > news:8dliqa$rqh$1_at_nnrp1.deja.com...
> > > HI all,
> > >
> > > I've been reading about tablespaces, rollback segments,
> > > extents, and I have some questions about them.
> > >
> > > If I use this statement to create a tablespace:
> > >
> > > Create tablespace ROLLBACK_TBS
> > > DATAFILE '/rollback/somefile.dbf'
> > > SIZE 25M
> > > DEFAULT STORAGE
> > > (
> > > INITIAL 160K
> > > NEXT 160K
> > > PCTINCREASE 0
> > > MINEXTENTS 2
> > > MAXEXTENTS 100
> > > )
> > >
> > > and then 3 rollback segments with this statement:
> > >
> > > Create ROLLBACK SEGMENT roll1_rbs
> > > (roll2_rbs, roll3_rbs)
> > > TABLESPACE ROLLBACK_TBS
> > > STORAGE
> > > (
> > > INITIAL 160K
> > > NEXT 160K
> > > MINEXTENTS 2
> > > MAXEXTENTS 30
> > > )
> > >
> > >
> > > I'm wondering:
> > > - is the whole tablespace being used, or, am I limiting
> > > its use to 16 megs (160k * 100 = 16000K = 16M) ?
> > > If so, can we ensure all the 25M is used by just leaving
> > > out the MAXEXTENTS clause?
> > >
> > > - is any transaction using this tablespace/rollback
 segment
> > > now limited to 4800K (160K * 30 = 4800K = 4.8M) total
 rollback
> > > resource? One big transaction can only use one rollback
> > > segment, correct?
> > >
> > > - can I create 10 rollback segments, in the same manner,
> > > all using the same tablespace, or will that cause an
 error?
> > > (10 * 4.8M = 48M. Larger than 25M.)
> > >
> > > - if a transaction runs out of extents, could I just
 increase
> > > the rollback segment's MAXEXTENTS to say, 100? Would this
> > > conflict with the other rollback segments?
> > >
> > > - while not a good idea to just give the UNLIMITED clause
> > > for rollback segments, if it was given, does the rollback
> > > segment grow until the tablespace reaches 25M? Or, until
 the
> > > disk is full?
> > >
> > >
> > > I've read what I could on these subjects, but it's not
> > > quite clear yet (as you can tell).
> > >
> > > Thanks,
> > >
> > > Argosy
> > >
> > >
> > >
> > >
> > >
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
> > >
> > >
> >
> >
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>
Received on Fri Apr 21 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US