Re: Indexed Tablespace Fragmentation

Niall Litchfield wrote:
>
> out of interest what size points do you generally pick for your extent
> sizes? and is there a reason for favouring particular sizes rather than
> sizes that differ by say 1000 times or so.
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
>
> ******************************************
> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message
> news:1020168287.14110.0.nnrp-13.9e984b29_at_news.demon.co.uk...
> >
> > Yes.
> >
> > But almost entirely for management reasons.
> > If a segment in an LMT has more than a few
> > dozen segments - and is still growing - it
> > should be in the 'next size up' tablespace.
> >
> >
> > --
> > Jonathan Lewis
> > http://www.jlcomp.demon.co.uk
> >
> > Author of:
> > Practical Oracle 8i: Building Efficient Databases
> >
> > Next Seminar - Australia - July/August
> > http://www.jlcomp.demon.co.uk/seminar.html
> >
> > Host to The Co-Operative Oracle Users' FAQ
> > http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> >
> >
> >
> > Nuno Souto wrote in message
> > <3cce7a59$0$15477$afc38c87_at_news.optusnet.com.au>...
> > >In article <1020117147.18578.0.nnrp-12.9e984b29_at_news.demon.co.uk>, you
> > >said (and I quote):
> > >>
> > >> There's a difference between
> > >> extents per segment
> > >> and
> > >> extents per tablespace
> > >
> > >yup.
> > >
> > >>
> > >> One reason I like to keep extents per segment
> > >> quite low (ideally a few dozen, worst case a
> > >> couple of hundred) is to ensure that the
> > >> tablespace as a whole doesn't end up
> > >> with huge number of extents.
> > >
> > >Is that for lmt AND dmt?
> > >
> > >
> > >--
> > >Cheers
> > >Nuno Souto
> > >nsouto_at_optushome.com.au.nospam
> >
> >

IMHO as long as its a sensible multiple of a multiblock read, pretty much any old size will do - but I like to look at sizing so that a (growth) object will extend about once per month - mainly because it makes those predictions that management like ("You will need more disk in exactly n days") easier to do.

Its for this reason that I do NOT like objects in 1 extent - you never know when their gonna go to the second...

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."