Subject: How Oracle Allocates Extents when altering PCTINCREASE
Author: RBEARD
Modified: 11 Sep 94 10:07:06
ALLOCATING EXTENTS WHEN ALTERING PCTINCREASE
1. INTRODUCTION
---------------
The algorithm of allocating extents when altering PCTINCREASE storage parameter
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 PCTINCREASE 0, load
some data, alter PCTINCREASE to 50, and then load some more data. We will check
DBA_EXTENTS view to investigate how many blocks will be allocated for
particular extent.
The same test will be made against Oracle Server v6.0.37 and v7.0.15.4.
In all examples we will assume DB_BLOCK_SIZE=2048 bytes
Tables used in these examples:
- EMP -> 23 rows
- DEPT -> 4 rows
The test is as follows:
connect scott/tiger
drop table test_alloc;
REM
REM Create a table with PCTINCREASE 0
REM
REM For v6 use create table statement with CHAR columns, i.e. comment the
REM statement with VARCHAR2 columns
create table test_alloc (x1 char(200), x2 char(200))
storage (initial 10K next 10K minextents 1 maxextents 121 PCTINCREASE 0);
REM
REM For v7 use create table statement with VARCHAR2 columns, i.e. comment
REM the previous statement with CHAR columns
create table test_alloc (x1 varchar(200), x2 varchar(200))
storage (initial 10K next 10K minextents 1 maxextents 121 PCTINCREASE 0);
REM
REM Insert some data
insert into test_alloc select 'AAAAAAAAAA','BBBBBB' from emp,emp, dept;
commit;
REM
REM Alter PCTINCREASE storage parameter to 50
alter table test_alloc storage (PCTINCREASE 50);
REM
REM Insert some more data
insert into test_alloc select 'AAAAAAAAAA','BBBBBB' from emp,emp, dept;
commit;
connect system/manager
REM
REM Check DBA_EXTENTS for the storage allocation
select EXTENT_ID, FILE_ID, BLOCK_ID, BYTES, BLOCKS
from dba_extents
where SEGMENT_NAME='TEST_ALLOC'
and OWNER='SCOTT'
order by EXTENT_ID;
3. ALLOCATING EXTENTS WHEN ALTERING PCTINCREASE IN v6.0.x
---------------------------------------------------------
The algorithm for calculating the size of the new extent to be allocated in
rdbms v6 is iterative:
CALC_SIZE(ext#0) = round(INITIAL)
CALC_SIZE(ext#1) = round(NEXT)
CALC_SIZE(ext#n) = round(CALC_SIZE(ext#n-1)*(1.0 + PCTINCREASE/100))
where round(X) rounds X to next oracle block boundary.
This means rdbms v6 doesn't use the actual allocated size of previous
extent, but calcualtes the iteration for every new extent.
Running the above test will lead to following:
EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS * CALC_SIZE
---------- ---------- ---------- ---------- ---------- * -------------
0 6 8394 10240 5 * 5
1 6 8399 10240 5 * 5
2 6 8404 10240 5 * 8
3 6 8409 10240 5 * 12
4 6 8414 10240 5 * 18
5 6 8419 10240 5 * 27
6 8 1226 83968 41 * 41
NOTES:
- ext#0 - ext#5 has been created with PCTINCREASE 0
- ext#6 has been created with PCTINCREASE 50
- column CALC_SIZE is added manually to show how did oracle came out
to allocate 41 blocks for ext#6.
- in cases like above it is possible that you end up with the error
ORA-1547, trying to allocate a huge number of blocks for the next extent
The error text 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.
4. ALLOCATING EXTENTS WHEN ALTERING PCTINCREASE IN v7.x
-------------------------------------------------------
The algorithm for calculating the size of the new extent to be allocated in
rdbms v7 uses simply the actual allocated size of previous extent:
CALC_SIZE(ext#0) = round_to_5(INITIAL)
CALC_SIZE(ext#1) = round_to_5(NEXT)
CALC_SIZE(ext#n) = round_to_5(PHYS_SIZE(ext#n-1)*(1.0 + PCTINCREASE/100))
where round_to_5(X) rounds X to next multiple of 5 oracle blocks
(see discussion on rounding to 5 in articale 'Allocating Extents')
and PHYS_SIZE(ext#m) = select blocks from dba_extents
where owner = table_owner
and segment_name = table_name
and extent_id = m
Running the above test will lead to following:
EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS
---------- ---------- ---------- ---------- ----------
0 6 1946 10240 5
1 6 1921 10240 5
2 6 1896 10240 5
3 1 7290 10240 5
4 6 1871 10240 5
5 1 7265 10240 5
6 6 1846 10240 5
7 1 5120 20480 10
8 1 9305 30720 15
NOTES:
- ext#0 - ext#6 have been created with PCTINCREASE 0
- ext#7 and ext#8 have been created with PCTINCREASE 50
In this case Oracle7 uses the allocated size of previous extent when calculating
the size of new one.