Re: dba - minextents/maxextents

From: Frank van Bortel <f.van.bortel_at_vnl.nl>
Date: Fri, 12 Nov 1999 10:20:33 -0800
Message-ID: <382C5A71.273CC3B9@vnl.nl>

Ben Ryan wrote:

> In article <80fgbs$hib$1_at_nnrp1.deja.com>,
> Nandakumar <N.Kumar_at_rocketmail.com> wrote:
> > if a tablespace is created with the following definition
> > crteate tablespace ts_1 datafile 'x' size 1000 M
> > storage (
> > initial 100M
> > next 50 M
> > minextents 1
> > maxextents 20
> > )
>
> You have not specified what you want pctincrease to be, it defaults
> to 50.
>
> >
> > what would be the maximum size that a table (all using default
> > storage) in the tablespace can grow?
>
> Depends on what pctincrease is set to. It would also assume that
> there were no storage parameters set on the table. If they were
> specified on the table they would override the default settings
> on the tablespace.
>
> >
> > In the above example,
> > Is it a good practise to define the maxextents as 1000/50=40?
>
> Sounds like your expecting PCTINCREASE to be zero.
>
> > ( Would it let a table in need take all available space and make no
> more
> > space available for other tables?? )
> >
> > If maxextents is 40, is it same as defining maxextents UNLIMITED?
> >
> > Is there any use of defining minextents when its value is 1?
>
> Which is its default value, so it does not make any technical
> difference unless you specify some value other than 1.
>
> >
> > if any of the questions is irrelevant, i guess i desperately need to
> > know what these MINEXTENTS and MAXEXTENTS are?
> >
>
> If pctincrease (percentage increase) is set to something to other than
> zero, then every time the segment (i.e. table or index) needs to grow
> it will grab a bigger chunk (extent) each time. So, it is
> certainly
> easier to follow what Oracle does if you set PCTINCREASE to zero. It
> is also my standard practise to set it to zero.
>

coalesce has to be done manually, then.

>
> Assuming that you did NOT set autoextend on then there are two
> ways that a segment will be prevented from growing:-
>
> 1) No free space in the tablespace (or, more common, there is
> no piece of contigious free space, big enough to hold your
> requested extent size.)
> 2) Reached the maximum extents for the segment
>
> The important point is that the two things are independent of
> each other.
>
> (Each segment reserves spaces in the first extent for storing
> the addresses of all the subsequent extents, so if you only
> reserve space for storing 40 addresses and you try to get 41
> extents there is nowhere to store the 41st address, hence you
> get an error. Note that this is independent of whether there is
> actually free space in the tablespace).
>
> I would recommend that, to start with, you
>
> a) Set default storage parameters on the tablespaces and do not
> specify anything on individual segments.
> b) Set PCTINCREASE to zero.
> c) Set initial extent size to equal next size.

Why? I always use twice the blocksize as initial (see your remark below). In this case, small tables (eg reference tables) will fit into their initial extent. Works like a charm.

>
> d) Set maxextents to unlimited (assuming you are using 7.3 or higher)
>
> If some of your segments are small and some are large then
> place them in different tablespaces and set the extent size
> appropriately in each tablespace.
>
> If you just want experiment to see what happens, remember that
> an extent is made up from a whole number of database blocks, so if
> your block size is 4K, you cannot have an extent with a size of, say,
> 7K. Also that Oracle rounds the database blocks allocated to an
> extent to the next multiple of 5. (There is minor exception here,
> if you go less than 5 blocks, you get what you asked for, with an
> absolute minimum of 2 blocks).

Correct, one header, one data.

> So to make things easy to follow
> pick an extent size which is say which say 5120 blocks. E.g. with
> a 4k block size pick 4x5120 = 20M.
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

--
Met vriendelijke groet/kind regards,

Frank van Bortel
Technical consultant Oracle

Work:                                Home:
----------------------------------   ----------------------------
Inter Access V&L                     Hunzestraat 4
Palatijn 3, 7521 PN Enschede         7555 WB Hengelo
PoBox 545, 7500 AM Enschede          (31)074-2425046

053-4341500 Received on Fri Nov 12 1999 - 12:20:33 CST