Oracle-L: LMT and Fragmentation

From: Niall Litchfield <niall.litchfield_at_dial.pipex.com>
Date: Mon, 13 Oct 2003 12:54:25 -0800
Message-ID: <F001.005D2F55.20031013125425@fatcity.com>

A week or so ago Jesse (I think) suggested a test to see whether auto-allocate LMTs were susceptible to fragmentation, or whether the fact that under the hood every allocation unit was 64k made this irrelevant. The test below shows that under 9.2 creating 32 tables, extending them until each has a next extent of > 64k. Then we drop half the tables. Can Oracle allocate a new extent for a table. Looks like it can't and the old fun of fragmentation might remain. I'll be sticking with ULMTs but flames/corrections welcomed.

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

BANNER

Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production

PL/SQL Release 9.2.0.3.0 - Production

CORE 9.2.0.3.0 Production

TNS for 32-bit Windows: Version 9.2.0.3.0 - Production

NLSRTL Version 9.2.0.3.0 - Production

SQL>
SQL> create tablespace auto_alloc_test
2 datafile 'c:\oracle\oradata\nl9iwk\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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Niall Litchfield
  INET: niall.litchfield_at_dial.pipex.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services


---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Mon Oct 13 2003 - 15:54:25 CDT