Re: AUTOALLOACATE vs. UNIFORM extent size in Oracle10g

Other than the fact that typically fragmentation and extent counts don't
tend to matter that much really is there a reason for the recommendation
that I'm missing. It's certainly the case that autoallocate *can* leave you
in the old position where there is sufficient free space to extend an object
but the extent size chosen won't fit any of the gaps left. Uniform by
definition can't hit this issue. Given that I've as yet been unable to find
a reason to prefer n extents over m extents where m >>> n, but I have been
able to find plenty of reasons to limit datafile size my personal bias is
for uniform extents (of 1mb or whatever the platform read size is but that's
a pedantic detail). Oracle consistently recommend AUTOALLOCATE but I've not
yet had a convincing rationale.  My test from 2004 probably not run since is
below - it only works because of the unusual pattern of extend|drop and the
small limit filesize, IIRC the sizes chosen were such that in 2004 my 32m
tablespace would exactly fill not error as below.. (8k db block size)

SQL> select banner from v$version;
BANNER

--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 -
Production
PL/SQL Release 11.2.0.2.0 -
Production
CORE 11.2.0.2.0 Production

TNS for 32-bit Windows: Version 11.2.0.2.0 -
Production
NLSRTL Version 11.2.0.2.0 -
Production
SQL>
SQL> create tablespace auto_alloc_test
  2  datafile 'c:\temp\auto_alloc.dbf' size 32832k
  3  extent management local;
Tablespace created.
SQL>
SQL> /*
SQL> create the tables
SQL> */
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
----------


29760
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  /
begin
*
ERROR at line 1:
ORA-01653: unable to extend table SYS.TABLE18 by 8 in tablespace
AUTO_ALLOC_TEST
ORA-06512: at line 4

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

----------


15.5
SQL>
SQL> alter table table1 allocate extent;
alter table table1 allocate extent
*
ERROR at line 1:
ORA-01653: unable to extend table SYS.TABLE1 by 128 in tablespace
AUTO_ALLOC_TEST

SQL>
SQL> drop tablespace auto_alloc_test including contents and datafiles;
Tablespace dropped.
SQL>
SQL> spool off


On Fri, Mar 4, 2011 at 3:53 PM, Greg Rahn <greg@xxxxxxxxxxxxxxxxxx> wrote:

> I'd would recommend AUTOALLOCATE over UNIFORM unless you require very
> large extents (>64MB).  If require larger extents for a segment, just
> use the INITIAL attribute.
>
> On Thu, Mar 3, 2011 at 10:42 AM, Mandal, Ashoke
> <ashoke.k.mandal@xxxxxxxxxxxxx> wrote:
> > I would like find out your personal experience on using AUTOALLOACTE for
> > extent management in Oracle 10g. I usually use UNIFORM extent size but
> > wondering what are the drawbacks for using AUTOALLOACTE.
>
> --
> Regards,
> Greg Rahn
> http://structureddata.org
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>


-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info

Other related posts: