Quote:
Originally posted by sreddy
I can take that Q for Amar:
That means no matter how big/small table you create, Oracle creates in multiple of distinct extent sizes mentioned(64K,1M,8M,64M).
In practical terms:
If you create 512K table in LMT with AUTOALLOCATE it uses 64K
If you use 10M , it uses IM extent size and if you use 50M, it uses 8M size and so on.
In first case 8 extents, second case 10 extents, third case 7 extents as opposed to
6+partial extent for 2MB.
Well, this is only the first (less important, IMHO) part of the autoallocate process. The basic idea is not about the initial extent sizes when table is created, but the adoption of next extent sizes when segment grows. Roughly, the basic idea is something like the following:
Quote:
From: Jonathan Lewis 05-Feb-03 08:33
Subject: Re : LMT Autoallocate Algorithm
I doubt if an algorithm will ever be published.
Every time someone asks the question the official
reply is that it is internal. This does mean that
it can change to address the possible honey-comb
effect you describe.
One minor detail - your experiments on sizes and rates
look as if they were based largely on a nice clean
tablespace. Oddities can happen (if in a clean start).
For example, try creating a table with (initial 64M) -
Oracle will create it with no 64K extents, and 64 x 1M
extents. Do the same with (initial 65M) and 8.1.7.4
and 9.2.0.2 will both create the first extent at 8M.
the next 40 something at 1M, and a couple more at 8M.
This makes it rather hard to guarantee that moving
a table, or exp/imp on a table will manage to fill
any funny little holes - possibly everything you
move will start allocating at larger sizes.
On the plus side - extents of size X are allocated on
a boundary matching X - in other words, they always
start in 'the right place' for their size. This does
reduce the probability of excessive honey-combing if
you have an early period of dropping and recreating
objects in the tablespace before things stabilise.
Jonathan Lewis
Author: Practical Oracle 8i