Re: Space Usage Question

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:40181d59$0$5862$afc38c87_at_news.optusnet.com.au...
>
> <rc_at_die@you@!spammers.sandworm.demon.co.uk> wrote in message
> news:nd4g10tge9pcan6fsp5rsiv4in879kha8j_at_4ax.com...
> > On Wed, 28 Jan 2004 10:18:57 -0800, Daniel Morgan
> > <damorgan_at_x.washington.edu> wrote:
> >
> > >spam_at_no-spam.com wrote:
> > >>
> > >> The table defination is
> > >> TABLESPACE BHF
> > >> PCTFREE 10
> > >> PCTUSED 40
> > >> INITRANS 1
> > >> MAXTRANS 255
> > >> STORAGE (
> > >> INITIAL 65536
> > >> MINEXTENTS 1
> > >> MAXEXTENTS 2147483645
> > >> FREELISTS 1 FREELIST GROUPS 1 )
> > >>
> > >> According to our stats the table span 223 extents, But that does not
> > >> make sense to me, because that mean each exten size is approx 11MB ?
> > >>
> > >> Can any one in the know please explain. I am new to oracle, so please
> > >> do not flame me if the answer is ovious
> > >
> > >Go with Brian's response to your inquiry but I take a serious look at
> > >your PCTFREE and PCTUSED. These are default values that likely have
> > >absolutely nothing to do with your data.
> > >
> > >Seriously reconsider these values ... you are likley wasting a lot of
> disk.
> >
> > For performance would I be better off with UNIFORM exten of 64M ?
>
>
> No, not at all. First, Daniel must have taken some pills or something this
> morning, because there is absolutely nothing wrong with the default
settings
> for PCTFREE and PCTUSED, and if there were it would be a question of
segment
> header contention, row migration problems, or full table scan poor
> performance, not disk space wastage. Those parameters are for performance
> issues, not space saving ones.
>
> Secondly, there is no performance impact whatsoever from allocating mixed
> extent sizes versus uniform ones. None, nada, niet, rien, zilch. Extent
> sizes and numbers have never had an impact on performance, except when
they
> got stupidly large in number, and your data dictionary had to cope with
the
> strain of dealing with them all. But in LMT, there is no difference
> whatsoever to performance.
>
> Stick with autoallocate. It is a nice algorithm, and thoroughly
recommended.
> And then try to stop worrying about numbers and sizes of extents
altogether,
> because those are the sorts of things DBAs used to worry about 6 or 7
years
> ago. You have far more important things to do with your time these days!!
>

Just to add to what Howard has said, autoallocate behind the covers actually uses a uniform size of 64K. Each bitmap corresponds to a "chunk" of 64K, similar to how a uniform size of 64K is allocated, the only significant difference being the autoallocate algorithm could request multiple occurrences of 64K chunks as the segment grows.

Cheers

Richard Received on Thu Jan 29 2004 - 05:33:43 CST