Re: Space Usage Question

"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:rc6Sb.33415$Wa.11886_at_news-server.bigpond.net.au...
> "Howard J. Rogers" <hjr_at_dizwell.com> wrote in message
> news:40181d59$0$5862$afc38c87_at_news.optusnet.com.au...
> >
> > >
> > > 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.
>

And just to add to what I had said.

There has always been an issue with suggestions that autoallocate could lead to fragmentation due to the fact that it allocates extents of differing sizes. Well, I had a little play with this today (Windows, Oracle 9.2)

I created an autoallocate LMT tablespace (200M), created two tables (A and B) and allocated in turn extents to each table such that the extents for each table were multiplexed within tablespace (eg. A,B,A,B,A,B etc).

Eventually both tables were creating 8M extents (3 each) before I got an unable to extend table error for both tables.

I then created a new table (C) just to gobble up all remaining space. The tablespace has now no remaining space.

I then dropped table B, creating a fragmented mess of free space of differing sizes (64K, 1M and 3 extents of 8M).

I then tried to allocate a new extent for table A and it succeeded, using the first 8m free chunk.

The next 2 allocations also succeeded using up the remaining 2 8M chunks to free space.

Would the next allocation succeed as I now only had 64K and 1M areas of free space ?

The answer is a resounding "YES" !!

It grabbed a 1M extent. As did the next allocation, and the next and the next ...

So faced with kinda wanting 8M of free space but not being able to get it "contiguously" within the tablespace, the autoallocate algorithm was quite happy to accept the next biggest piece of free space available. In fact all the remaining free space was able to be utilized by the table A.

Conclusion ?

That the so-called "disadvantage" of autoallocate causing fragmentation issues is somewhat exaggerated and (as far as my little experiment showed) is a non-issue when considering using autoallocate.

If anyone wants me to send/post the actual test, let me know (bedtime now !!)

Cheers

Richard Received on Mon Feb 02 2004 - 07:03:36 CST