Subject: How Oracle Allocates Extents
Author: RBEARD
Modified: 11 Sep 94 10:06:56
ALLOCATING EXTENTS
1. INTRODUCTION
---------------
The algorithm of allocating extents has been changed in new versions of Oracle7
Server, so this article will try to explain the changes in detail with
appropriate examples.
2. EXAMPLES
-----------
To illustrate the issue we will try to create a table with different extents
size (18, 20, 21, 25, 26 oracle blocks) in a tablespace with 28 blocks free.
The same test will be made against Oracle Server v6.0.37, v7.0.15.4 and v7.1.1.
In all examples we will assume DB_BLOCK_SIZE=2048 bytes
The test is as follows:
REM - create a tablespace with the size 59392 bytes (i.e. 29 oracle blocks)
drop tablespace alloc;
create tablespace alloc
datafile 'DISK$ORACLE:[ORACLE7.ROOT.DB_SSC7]SSC7_ALLOC.DBS' size 59392;
REM
REM - this tablespace will leave 28 oracle blocks free (see dba_free_space view)
select * from sys.dba_free_space where tablespace_name='ALLOC';
REM - drop/create the table with initial extents of 18, 20, 21, 25, 26
REM consequtively
REM - check BLOCKS and INIEXTS clumns in SYS.SEG$ table.
REM BLOCKS column keeps the information on number of blocks allocated for this
REM segment so far.
REM INIEXTS columns keeps the value of INITIAL parameter
REM table with 18 blocks
drop table test_alloc;
create table test_alloc (a number) tablespace alloc storage (initial 36864);
select a.blocks, a.iniexts from sys.seg$ a, sys.dba_data_files b
where file#=file_id
and tablespace_name='ALLOC';
REM table with 20 blocks
drop table test_alloc;
create table test_alloc (a number) tablespace alloc storage (initial 40960);
select a.blocks, a.iniexts from sys.seg$ a, sys.dba_data_files b
where file#=file_id
and tablespace_name='ALLOC';
REM table with 21 blocks
drop table test_alloc;
create table test_alloc (a number) tablespace alloc storage (initial 43008);
select a.blocks, a.iniexts from sys.seg$ a, sys.dba_data_files b
where file#=file_id
and tablespace_name='ALLOC';
REM table with 25 blocks
drop table test_alloc;
create table test_alloc (a number) tablespace alloc storage (initial 51200);
select a.blocks, a.iniexts from sys.seg$ a, sys.dba_data_files b
where file#=file_id
and tablespace_name='ALLOC';
REM table with 26 blocks
drop table test_alloc;
create table test_alloc (a number) tablespace alloc storage (initial 53248);
select a.blocks, a.iniexts from sys.seg$ a, sys.dba_data_files b
where file#=file_id
and tablespace_name='ALLOC';
In the test the error ORA-1547 will be sometimes issued (see tests bellow).
The error explaination is:
01547, 00000, "failed to allocate extent of size %s in tablespace '%s'"
// *Cause: Tablespace indicated is out of space
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
// files to the tablespace indicated or create the object in other
// tablespace if this happens during a CREATE statement
Note, the extent size in this error is in number of oracle blocks, and not
bytes.
3. ALLOCATING EXTENTS IN v6.0.x
-------------------------------
V60_RULE1: ORACLE v6 will try to allocate an extent of the exact size as is
requested
V60_RULE2: if V60_RULE1 fails ORACLE v6 will try to allocate an extent that
has enough free space for the requested size
V60_RULE3: if V60_RULE1 and V60_RULE2 fail ORACLE v6 will try to coallasce
contigous free extents and use that
V60_RULE4: If there is not enough space in requested tablespace, an error
ORA-1547 will be issued
Applying the above test to v6 database will bring following results:
BLOCKS INIEXTS RULE
---------- ---------- ----------
18 18 V60_RULE1
20 20 V60_RULE1
21 21 V60_RULE1
25 25 V60_RULE1
26 26 V60_RULE1
4. ALLOCATING EXTENTS IN v7.0.x
-------------------------------
V70_RULE1: ORACLE v7.0.x will translate the specified extent size to number of
oracle blocks and round them to the next multiplicate of 5.
e.g.
43008 bytes=21 oracle blocks of 2K => will be rounded to 25 blocks
Note: the reason for this rule is to reduce fragmentation.
V70_RULE2: It will then try to allocate the number of blocks resulting from
V70_RULE1
V70_RULE3: If it can't allocate the number of blocks resulting from V70_RULE1,
an error ORA-1547 will be issued
V70_RULE4: If V70_RULE2 succeeds, Oracle v7.0 checks if <5 blocks are left free
in the tablespace, and in this case, allocates them too, as there is
no sense to leave <5 blocks as no extent can allocate them.
e.g.
If dba_free_space show 28 blocks free in the tablespace, and we
try to allocate 25, oracle will see that there are only 3 blocks
left, and will allocate all 28 blocks.
Applying the above test to v7.0 database will bring following results:
BLOCKS INIEXTS RULE
---------- ---------- ----------
20 18 V70_RULE2
20 20 V70_RULE2
28 21 V70_RULE2 & V70_RULE4
28 25 V70_RULE2 & V70_RULE4
ORA-1547 26 V70_RULE2 & V70_RULE3
5 ALLOCATING EXTENTS IN v7.1.x
-------------------------------
V71_RULE1: ORACLE v7.1.x will translate the specified extent size to number of
oracle blocks and round them to the next multiplicate of 5.
V71_RULE2: It will then try to allocate the number of blocks resulting from
V71_RULE1
V71_RULE3: If it can't allocate the number of blocks resulting from V71_RULE1,
if will try to allocate the exact number of blocks as requested.
V71_RULE4: If it can't allocate the number of blocks resulting from V71_RULE1
and V71_RULE3, an error ORA-1547 will be issued
V71_RULE5: If V71_RULE2 or V71_RULE3 succeed, Oracle v7.1 checks if <5 blocks
are left free in the tablespace, and allocates them too in this case
Applying the above test to v7.1 database will bring following results:
BLOCKS INIEXTS RULE
---------- ---------- ----------
20 18 V71_RULE2
20 20 V71_RULE2
28 21 V71_RULE2 & V71_RULE5
28 25 V71_RULE2 & V71_RULE5
28 26 V71_RULE3 & V71_RULE5