Re: Space Usage Question

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Mon, 2 Feb 2004 14:36:26 -0000
Message-ID: <401e606c$0$9392$ed9e5944@reading.news.pipex.net>

"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:IUrTb.39847$Wa.31380_at_news-server.bigpond.net.au...
> 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)
<snip>
> 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
> !!)

Morning Richard :(

I have different results. I attempted to fill up an autoallocate tablespace with 32 tables - 16 extents each, drop half the tables and then allocate a 17th extent to table1.

SQL> set echo on
SQL> select banner from v$version;

BANNER

Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for 32-bit Windows: Version 9.2.0.4.0 - Production NLSRTL Version 9.2.0.4.0 - Production

SQL>
SQL> create tablespace auto_alloc_test
2 datafile 'c:\oracle\oradata\nl9204\auto_alloc.dbf' size 32832k 3 extent management local;

Tablespace created.

SQL>
SQL> /*
DOC>create the tables
DOC>*/
SQL>
SQL> begin

  2 for i in 1..32 loop
  3 execute immediate 'create table table'||i||'(col1 number,col2 number) tablespace auto_alloc_test';
  4 end loop;
  5 end;
  6 /

PL/SQL procedure successfully completed.

SQL>
SQL> select sum(bytes)/1024 free_k from dba_free_space where tablespace_name='AUTO_ALLOC_TEST';

FREE_K

30720

SQL>
SQL> begin
2 for i in 1..15 loop

  3        for j in 1..32 loop
  4         execute immediate 'alter table table'||j||' allocate extent';
  5        end loop;

  6 end loop;
  7 end;
  8 /

PL/SQL procedure successfully completed.

SQL>
SQL> select sum(bytes)/1024/1024 free_M from dba_free_space where tablespace_name='AUTO_ALLOC_TEST';

FREE_M

SQL>
SQL> begin
2 for i in 1..32 loop

  3        if i mod 2 = 0 then
  4         execute immediate 'drop table table'||i;
  5        end if;

  6 end loop;
  7 end;
  8 /

PL/SQL procedure successfully completed.

SQL>
SQL> select sum(bytes)/1024/1024 free_mb from dba_free_space where tablespace_name='AUTO_ALLOC_TEST';

FREE_MB

SQL>
SQL> alter table table1 allocate extent; alter table table1 allocate extent
*
ERROR at line 1:
ORA-01653: unable to extend table NIALL.TABLE1 by 64 in tablespace AUTO_ALLOC_TEST SQL>
SQL> drop tablespace auto_alloc_test including contents and datafiles;

Tablespace dropped.

SQL>
SQL> spool off Received on Mon Feb 02 2004 - 08:36:26 CST