I don't know if this has already been mentioned,
but another strategy that Oracle uses to minimise
holes is that an extent of size X is always allocated
on a boundary at size X. So an 8M boundary is
allocated on a 8M boundary in the file (allowing for
the 64K header), a 1M boundary is allocated on
a 1M boundary. So even in a messy cycle of
allocation and dropping, you tend to end up with
a small number of (temporarily) empty 64K extents,
a small number of 1M, and so on, rather than loads
of them all over the place. And, of course, as soon
as you have more than 30 consecutive empty 64K
chunks, that guarantees the existence of some
space that could go into a single 1M chunk.
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick Jr
Next public appearances:
March 2004 Hotsos Symposium - The Burden of Proof
March 2004 Charlotte NC OUG - CBO Tutorial
April 2004 Iceland
One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___February
____UK___June
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
"Richard Foote" <richard.foote_at_bigpond.com> wrote in message
news:IUrTb.39847$Wa.31380_at_news-server.bigpond.net.au...
>
> 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
> !!)
>
Received on Mon Feb 02 2004 - 07:53:37 CST
