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: Fragmentation of tablespace

Re: Fragmentation of tablespace

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: 2000/06/12
Message-ID: <3944ED7C.37C8@yahoo.com>#1/1

Howard J. Rogers wrote:
>
> "Connor McDonald" <connor_mcdonald_at_yahoo.com> wrote in message
> news:39439943.7F92_at_yahoo.com...
> > Howard J. Rogers wrote:
> > >
> > > "Knotko V." <knotko_at_online.ru> wrote in message
> > > news:8hveqf$q8u$1_at_news.sovam.com...
> > > > Hello,
> > > > How could I escape fragmentation of tablespace RBS in Oracle RDBMS
 7.3.4.
> > > >
> > >
> > > Define terms firstly.
> > >
> > > Fragmentation arises when two things happen: when extents are given up
 by a
> > > segment (because you drop it, deallocate it or truncate it); and when
> > > extents within a tablespace are of different sizes. Why? Because when
 an
> > > extent is freed up, Oracle nevertheless remembers the extent boundaries.
 So
> > > if you release 5 extents of 10Mb you *do NOT* have 50Mb of space that is
> > > useable. If another extent wants to acquire an extent of, say, 20Mb,
 Oracle
> > > will report that it is unable to acquire the extra extent -because no
 20Mb
> > > free space is available. 5 lots of 10 Mb remain 5 little lots, not one
> > > large one.
> > >
> > > The cure is thus obvious. Make all extent sizes withiin a tablespace
> > > identical. If everything came in 10Mb extents in the previous example,
 the
> > > extending segment would of course be able to find a free 10Mb
 extent -there
> > > are five of them going begging.
> > >
> > > Second, avoid freeing up extents in the first place... which sounds a
 bit
> > > daft for normal tables etc (because it means proposing that you never
 drop
> > > or truncate segments). For rollback segments, however, it is not so
 daft.
> > > Rollback segments release free extents if they have cause to grow
 (because
> > > of large transactions, or because of blocking transactions), and if they
> > > then spot later on that they are larger than optimal. The cure for
 rollback
> > > segments is thus to size them properly in the first place (ie, don't
 allow
> > > them to acquire extra extents under normal circumstances) and (perhaps)
 to
> > > avoid the use of the Optimal clause, which will cause automatic
 shrinkage
> > > (and hence release of extents).
> > >
> > > If you want to shrink a rolback segment that has ballooned out of sight,
 I
> > > suggest you perform manual shrink on them (alter rollback segment X
 shrink;)
> > > You might then care to perform a manual coalesce of a tablespace, again
> > > outside of hours (alter tablespace Y coalesce;).
> > >
> > > Coalescing means SMON removes the extent boundaries, so lots of little,
> > > contiguous extents are regarded as one large piece of free space.
> > >
> > > You can get coalescing to happen automatically if you set PCRINCREASE to
> > > anything other than 0 for the tablespace, but I don't recommend it...
 you'll
> > > have Oracle doing stuff that can affect performance at times of its own
> > > choosing, rather than when you deem it appropriate.
> > >
> > > In short: (1) Tablespaces should have INITIAL=NEXT, and no segments
 created
> > > within them should override those settings (thus ensuring consistent
 extent
> > > sizes). Don't set PCTINCREASE to anything other than 0, for the same
> > > reason. And (2): size your rollback segments appropriately to begin
 with,
> > > to avoid automatic growth and shrinkage. If you do get unexpected
 growth
> > > (because of a blocking transaction), manually shrink the thing to an
> > > appropriate size, and follow it with a manual coalesce.
> > >
> > > (Incidentally, the term 'fragmentation' has nothing to do with different
> > > extents of a segment being interspersed with extents of other segments.
 In
> > > that sense, it is not what you might expect if you're in the habit of
 using
> > > DiskKeeper or similar O/S defragmentation tools).
> > >
> > > Hope that helps
> > > HJR
> > >
> > > > Thanks in advance,
> > > > Vera.
> > > >
> > > > knotko_at_online.ru
> > > >
> > > >
> >
> > or with 8i define them as locally managed and most/all of this is taken
> > care of for you...I love 'em.
> >
>
> Actually, I'd forgot about them, but you're absolutely right. Brilliant
> invention!
> (Care to tell me of any drawbacks they have? There must be some, surely??!)
>
> Regards
> HJR
>
> > HTH
> > --
> > ===========================================
> > Connor McDonald
> > http://www.oracledba.co.uk
> >
> > We are born naked, wet and hungry...then things get worse

The only one I know of that the bitmap is not cached thus you need to visit the disk for extent allocation - thus if you had a tspace that was having heavy extent allocation (eg for temporary creation/drop of objects) then maybe dictionary based would be better...

... but you're right - I love 'em.

Cheers

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse
Received on Mon Jun 12 2000 - 00:00:00 CDT

Original text of this message

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