| 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: dba - minextents/maxextents
Frank van Bortel wrote:
>
> 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
Because if all your segments are identical size (within a tablespace) then no more fragmentation problems - all freespace chunks are the same size and can be equally filled by any extending/new segment...No more coalesce needed, no more reorgs, optimal space usage
With 8i, you can also make the tspace locally managed, and then you also avoid nasty contention on UET$ and FET$...
Voila ! No more late nights...
--
"Some days you're the pigeon, and some days you're the statue." Received on Fri Nov 12 1999 - 03:09:28 CST
 |
 |