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: Space Usage Question

Re: Space Usage Question

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Thu, 29 Jan 2004 07:36:40 +1100
Message-ID: <40181d59$0$5862$afc38c87@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!!

Regards
HJR Received on Wed Jan 28 2004 - 14:36:40 CST

Original text of this message

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